mlove1024
06-05-2014, 10:03 AM
Hello,
Greetings and Salutations.
I have been making the rounds on the Excel Forums trying to get some help with the coding for a Data Entry User Form in 2007 Excel VBA which I have been designing.
Some back story first, then I'll post a link to a sample Workbook, and explain what I would like to have the Form do for it to be finished.
BACK STORY:
Currently I am working a Help Desk position for BoA from 1am - 9am, after which I go into a job at the local Community College to work as a Lab Tech until ~ 1:30pm-2pm, then I tutor Math, Physics, and Chemistry the rest of the afternoon. I have been on this daily cycle almost eight months. About six months ago, I brought home about three boxes of Files and Records for the Oil & Gas Mineral Royalties that my Grandfather acquired over 40 years working as an Oil & Gas Attorney. They were in complete disarray and haven't been managed for almost a decade and a half now.
So I decided that it would be an opportunity to make enough money to pay off my debt, quit one (or two) of my jobs, and start living a normal life :). To this end I have started the spreadsheet that you will be seeing a sample of below. PLEASE bear this in mind while reading, as I have Minimal experience with Excel and None with VBA before I started to work on the User Form I have been building. On top of that I get about 4-6 hours in on this stuff a week, and it comes out of my "Free Time" :).
Anyway.
My Spreadsheet consists of the following fields of information:
Original Owner
Heirs
County
Property Description
Assignment - Volume & Page
Affidavit of Heirship - Volume & Page
Probate - Volume & Page
Misc.
I'm creating a Data Entry User form so that I can sick my Momma and Bother on filling the spreadsheet out too. This is really just a first step to the project, but a necessary one to be able to pull up information for specific Owners, Heirs, or Counties and determine what is recorded where and what needs to be recorded where.
So, here is a picture of my VBA Data Entry Userform, and my Spreadsheet Fields (I have provided a Downloadable Sample Worksheet/Workbook at the end of the Post):
11783 11784
As you can see here, I have 3 Combo Boxes, which I have Cascade from Original Owner to Heir to County. Once the County has been chosen for a specific Heir, we can fill in the Property Description, select whether or not the Assignment, Affidavit, or Probate are Recorded or Not, and if so What Volume and Page they are Recorded on. Simple in essence, and this is going to be the foundation for the Spreadsheet I end up creating to organize Royalty Interests that are currently in Production, which I may end up creating an Access Database so that I can create Relationships between what will end up being a total of Four Spreadsheets.
Question 1: It seems to me that using VBA with Excel and customizing my own User Forms, Report Forms, Reports, and Queries I shouldn't have to go to the trouble of trying to teach myself MS Access, since I can basically do everything I need with Excel. Given relatively small amount of information that I am recording, is this a fair assessment?
Back to the User Form, if you look at the little Sample Picture of my Spreadsheet you will see the Fields which Correlate to the fields of my User Form. As of now, I have my Three Combo Boxes changing Heirs and County accordingly with the Original Owner selected in the first Combo Box Drop Down. I have also set the Cancel Button to Unload without updating changes when the Cancel Button is pressed. That is All I have completed in the User Form. I know it doesn't seem like much, but it took me almost 12 weeks to get this far.
Question 2: Once I have selected an Original Owner, an Heir, and a County (for which there is only One Row that has the selected Original Owner & the selected Heir in the selected County), how do I use this to Define the Row for which my Property Description Text Box, and Assignment, Affidavit, and Probate Option Buttons and Vol/Page Text Boxes will update?
Question 3: How do I set it so that when the Update Button is pressed, the changes made in the User Form update the Spreadsheet and the User Form stays, while when the Done Button is pressed the Spreadsheet is updated, and the User Form is closed?
Question 4: Is there any specific code that needs to be used so that if I selected an Original Owner, Heir, and County where there is already a Property Description, Assignment Recorded and Vol/Page information, it automatically populated in the correlating User Form fields?
Question 5: How do I set the Option Button to change the Assignment Cell for the Row that has the O.O-Heir-County selected in the three Combo Boxes? (For example, If I have Selected Original Owner-Adams, Paul M. Heir-Wilmington Trust Company... and County-Live Oak; This selection refers to Row 5 and as such I only want changes to be made to Property Description, Assignment, Affidavit, and Probate Fields for Row 5 when these three options are selected in the Combo Boxes)
I think this is really all the information I need to complete my User Form to the point where I will be able to use it to start filling out my spreadsheet, and have my Momma and Brother start doing so, with a minimum of Explanation.
Any Help that I get is greatly appreciated, as it seems that I have not bee getting any real consideration in any of the other Forums, YET ;) but I usually do get a few comments. I'm really just trying to save myself spending the next 3-6 weeks continuing to work on this User Form instead of getting the Spreadsheet finished and ready to be updated as needed, then moving on to the next stage of the project.
All the same though, here is a Sample of My Workbook with my UserForm as it currently exists:
11785
Greetings and Salutations.
I have been making the rounds on the Excel Forums trying to get some help with the coding for a Data Entry User Form in 2007 Excel VBA which I have been designing.
Some back story first, then I'll post a link to a sample Workbook, and explain what I would like to have the Form do for it to be finished.
BACK STORY:
Currently I am working a Help Desk position for BoA from 1am - 9am, after which I go into a job at the local Community College to work as a Lab Tech until ~ 1:30pm-2pm, then I tutor Math, Physics, and Chemistry the rest of the afternoon. I have been on this daily cycle almost eight months. About six months ago, I brought home about three boxes of Files and Records for the Oil & Gas Mineral Royalties that my Grandfather acquired over 40 years working as an Oil & Gas Attorney. They were in complete disarray and haven't been managed for almost a decade and a half now.
So I decided that it would be an opportunity to make enough money to pay off my debt, quit one (or two) of my jobs, and start living a normal life :). To this end I have started the spreadsheet that you will be seeing a sample of below. PLEASE bear this in mind while reading, as I have Minimal experience with Excel and None with VBA before I started to work on the User Form I have been building. On top of that I get about 4-6 hours in on this stuff a week, and it comes out of my "Free Time" :).
Anyway.
My Spreadsheet consists of the following fields of information:
Original Owner
Heirs
County
Property Description
Assignment - Volume & Page
Affidavit of Heirship - Volume & Page
Probate - Volume & Page
Misc.
I'm creating a Data Entry User form so that I can sick my Momma and Bother on filling the spreadsheet out too. This is really just a first step to the project, but a necessary one to be able to pull up information for specific Owners, Heirs, or Counties and determine what is recorded where and what needs to be recorded where.
So, here is a picture of my VBA Data Entry Userform, and my Spreadsheet Fields (I have provided a Downloadable Sample Worksheet/Workbook at the end of the Post):
11783 11784
As you can see here, I have 3 Combo Boxes, which I have Cascade from Original Owner to Heir to County. Once the County has been chosen for a specific Heir, we can fill in the Property Description, select whether or not the Assignment, Affidavit, or Probate are Recorded or Not, and if so What Volume and Page they are Recorded on. Simple in essence, and this is going to be the foundation for the Spreadsheet I end up creating to organize Royalty Interests that are currently in Production, which I may end up creating an Access Database so that I can create Relationships between what will end up being a total of Four Spreadsheets.
Question 1: It seems to me that using VBA with Excel and customizing my own User Forms, Report Forms, Reports, and Queries I shouldn't have to go to the trouble of trying to teach myself MS Access, since I can basically do everything I need with Excel. Given relatively small amount of information that I am recording, is this a fair assessment?
Back to the User Form, if you look at the little Sample Picture of my Spreadsheet you will see the Fields which Correlate to the fields of my User Form. As of now, I have my Three Combo Boxes changing Heirs and County accordingly with the Original Owner selected in the first Combo Box Drop Down. I have also set the Cancel Button to Unload without updating changes when the Cancel Button is pressed. That is All I have completed in the User Form. I know it doesn't seem like much, but it took me almost 12 weeks to get this far.
Question 2: Once I have selected an Original Owner, an Heir, and a County (for which there is only One Row that has the selected Original Owner & the selected Heir in the selected County), how do I use this to Define the Row for which my Property Description Text Box, and Assignment, Affidavit, and Probate Option Buttons and Vol/Page Text Boxes will update?
Question 3: How do I set it so that when the Update Button is pressed, the changes made in the User Form update the Spreadsheet and the User Form stays, while when the Done Button is pressed the Spreadsheet is updated, and the User Form is closed?
Question 4: Is there any specific code that needs to be used so that if I selected an Original Owner, Heir, and County where there is already a Property Description, Assignment Recorded and Vol/Page information, it automatically populated in the correlating User Form fields?
Question 5: How do I set the Option Button to change the Assignment Cell for the Row that has the O.O-Heir-County selected in the three Combo Boxes? (For example, If I have Selected Original Owner-Adams, Paul M. Heir-Wilmington Trust Company... and County-Live Oak; This selection refers to Row 5 and as such I only want changes to be made to Property Description, Assignment, Affidavit, and Probate Fields for Row 5 when these three options are selected in the Combo Boxes)
I think this is really all the information I need to complete my User Form to the point where I will be able to use it to start filling out my spreadsheet, and have my Momma and Brother start doing so, with a minimum of Explanation.
Any Help that I get is greatly appreciated, as it seems that I have not bee getting any real consideration in any of the other Forums, YET ;) but I usually do get a few comments. I'm really just trying to save myself spending the next 3-6 weeks continuing to work on this User Form instead of getting the Spreadsheet finished and ready to be updated as needed, then moving on to the next stage of the project.
All the same though, here is a Sample of My Workbook with my UserForm as it currently exists:
11785