Data Entry UserForm for Excel 2007

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):
Attachment 11783 Attachment 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:

Attachment 11785

This would be a whole class on VB. Forms require lots o code.

Yeah, I understand that I'm probably biting off more than I can chew with this. I'm not terribly daunted though. I'm pretty good at picking things up as I go. As for a whole class on Visual Basic, I have been watching the Youtube ExcelVBAIsFun videos, and going through a cadre of websites teaching myself the basics of how to use VBA for Excel.

Also, I downloaded and purchased both the UserForm and access to it's VBA Code from this website, as well as his Developers PUPv7 Add-on and it's code (and I have read and re-read the code a dozen and a half times).

Even though I'm not to the point where I can pump out my own code, I feel I understand the basics enough to be able to this if I can get some assistance. The truth is that I am aware that I am asking for a lot, and my expectations for assistance - while optimistic - are not naive. I'm posting to every forum I can with my fingers crossed, hoping to get someone that knows this stuff jumps on it.

Below you can see that I have set up the Form so far to Initialize and populate all three Combo Boxes, removing the Duplicates.

Then I have set it up so that once the Original Owner is selected (OOCombo) then the Heir Combo Box (HCombo) only lists those Heirs of the Original Owner Selected.

Once an Heir is selected only those County names for that Heir appear. I copied the code to Remove Duplicates for these as well, but once an Original Owner is Selected in the first Combo Box, the Heir Combo Box (which has removed all Duplicates from the Heir list before the Original Owner is selected) still shows the names Duplicated for some reason.

Once the Heir is selected however, the County names Do Not duplicate, Lol!! That really shouldn't matter for what I want to do :).

Here is the code that I have created for the form in VBA as it is now:

Code:

---

'This code sets the Cancel Button to close out and not save changes
Private Sub CancelComm_Click()

Unload Me

End Sub

'This code restricts the County Combo Box list to only those Counties which appear for the Selected Heir
Private Sub HCombo_Change()

Dim z As Long
Dim lastDataRow3b As Long
Dim R_E_M3bText As String

'set myVal
myVal = Me.HCombo.Value

'clear CCombo
Me.CCombo.Clear

       
'loop thru col B
lastDataRow3b = Cells(Rows.Count, "C").End(xlUp).Row

'loop thru
            For z = 2 To lastDataRow3b
                R_E_M3bText = Cells(z, 3).Value
                If myVal = ThisWorkbook.Sheets("Royalty_Estates_Main").Cells(z, 2).Value Then
        'add to combobox
                Me.CCombo.AddItem R_E_M3bText
                If Not Is_Duplicate3b(R_E_M3bText) Then CCombo.AddItem R_E_M3bText
                End If
            Next z
           
End Sub

'This code restricts the Heir List to only those Heirs for the Selected Original Owner
Private Sub OOCombo_Change()

Dim x As Long
Dim lastDataRow2b As Long
Dim R_E_M2bText As String

'set myVal
myVal = Me.OOCombo.Value

'clear HCombo
Me.HCombo.Clear

       
'Locate the Last Row on Columb B
lastDataRow2b = Cells(Rows.Count, "B").End(xlUp).Row

'loop thru
            For x = 2 To lastDataRow2b
                R_E_M2bText = Cells(x, 2).Value
                If myVal = ThisWorkbook.Sheets("Royalty_Estates_Main").Cells(x, 1).Value Then
        'add to combobox
                Me.HCombo.AddItem R_E_M2bText
                If Not Is_Duplicate2b(R_E_M2bText) Then HCombo.AddItem R_E_M2bText
                End If
            Next x

End Sub

'This is my Initialize Event that runs when the Submit Button on the Spreadsheet is pressed
Private Sub UserForm_Initialize()

'Populate OOCombobox
Dim i As Long, lastDataRow As Long
Dim R_E_MText As String
Dim wks As Worksheet

    Set wks = Sheets("Royalty_Estates_Main")
   
    With wks
        lastDataRow = Cells(Rows.Count, "A").End(xlUp).Row
        For i = 2 To lastDataRow
            R_E_MText = .Cells(i, 1).Value
            If Not Is_Duplicate(R_E_MText) Then OOCombo.AddItem R_E_MText
        Next
    End With

    Set wks = Nothing
   
'Populate HCombobox
   
Dim j As Long, lastDataRow2 As Long
Dim R_E_M2Text As String
Dim wks1 As Worksheet

    Set wks1 = Sheets("Royalty_Estates_Main")

    With wks1
        lastDataRow2 = Cells(Rows.Count, "B").End(xlUp).Row
        For j = 2 To lastDataRow2
            R_E_M2Text = .Cells(j, 2).Value
            If Not Is_Duplicate2(R_E_M2Text) Then HCombo.AddItem R_E_M2Text
        Next
    End With
   
'Populate CCombobox
   
Dim y As Long, lastDataRow3 As Long
Dim R_E_M3Text As String
Dim wks2 As Worksheet

    Set wks2 = Sheets("Royalty_Estates_Main")

    With wks2
        lastDataRow3 = Cells(Rows.Count, "C").End(xlUp).Row
        For y = 2 To lastDataRow3
            R_E_M3Text = .Cells(y, 3).Value
            If Not Is_Duplicate3(R_E_M3Text) Then CCombo.AddItem R_E_M3Text
        Next
    End With
   
'Empty PropText
PropText.Value = ""

'Set OptionButton2 as default
OptionButton2.Value = True

'Empty VolText1
VolText1.Value = ""

'Empty PageText1
PageText1.Value = ""

'Set OptionButton4 as default
OptionButton4.Value = True

'Empty VolText2
VolText2.Value = ""

'Empty PageText2
PageText2.Value = ""

'Set OptionButton6 as default
OptionButton6.Value = True

'Empty VolText3
VolText3.Value = ""

'Empty PageText3
PageText3.Value = ""

End Sub

'Below are all of the Duplicate Removal Functions that I have set up for the three ComboBoxes both before and after selections have been made
Private Function Is_Duplicate(inText As String) As Boolean
Dim k As Long
Dim tmpBool As Boolean

    For k = 0 To OOCombo.ListCount - 1
        If inText = OOCombo.List(k) Then
            tmpBool = True
            Exit For
        End If
    Next

    Is_Duplicate = tmpBool
   
End Function

Private Function Is_Duplicate2(inText As String) As Boolean
Dim k As Long
Dim tmpBool As Boolean

    For k = 0 To HCombo.ListCount - 1
        If inText = HCombo.List(k) Then
            tmpBool = True
            Exit For
        End If
    Next

    Is_Duplicate2 = tmpBool
   
End Function

Private Function Is_Duplicate3(inText As String) As Boolean
Dim k As Long
Dim tmpBool As Boolean

    For k = 0 To CCombo.ListCount - 1
        If inText = CCombo.List(k) Then
            tmpBool = True
            Exit For
        End If
    Next

    Is_Duplicate3 = tmpBool
   
End Function

Private Function Is_Duplicate2b(inText As String) As Boolean
Dim k As Long
Dim tmpBool As Boolean

    For k = 0 To HCombo.ListCount - 1
        If inText = HCombo.List(k) Then
            tmpBool = True
            Exit For
        End If
    Next

    Is_Duplicate2b = tmpBool
   
End Function

Private Function Is_Duplicate3b(inText As String) As Boolean
Dim k As Long
Dim tmpBool As Boolean

    For k = 0 To CCombo.ListCount - 1
        If inText = CCombo.List(k) Then
            tmpBool = True
            Exit For
        End If
    Next

    Is_Duplicate3b = tmpBool
   
End Function

---

So, as it is I have learned a great deal and even managed to get the Form to begin to function according to how I would like. At the rate I'm going, I will probably have it up and running exactly how I would like in abut 3-4 weeks. Just trying to cut this time in half or more.

I may get time to look at this. But so that I know that I (or any one else for that matter) am/is not duplicating effort on another forum/site, would you be good enough to tell us which forums/sites you have posted these questions to? I realise you may not be able to post links until you've made 5 posts here but you can do the nearest thing by pasting links here but removing the http:// bit.

No real error checking but some thoughts on what I think you're looking to do

Code:

---

'---------------------------------------------------
Private Sub DoneComm_Click()
    Call UpdateComm_Click
    Call CancelComm_Click
End Sub

'----------------------------------------------------------
Private Sub UpdateComm_Click()
    Dim rRow As Range
   
    For Each rRow In Worksheets("Royalty_Estates_Main").Cells(1, 1).CurrentRegion.Rows
        With rRow
            If .Cells(1, 1).Value = Me.OOCombo.Value And _
                .Cells(1, 2).Value = Me.HCombo.Value And _
                .Cells(1, 3).Value = Me.CCombo.Value Then
           
                .Cells(1, 4).Value = Me.PropText.Value
                       
                       
                .Cells(1, 7).Value = Me.VolText1.Value
                .Cells(1, 8).Value = Me.PageText1.Value
           
                .Cells(1, 11).Value = Me.VolText2.Value
                .Cells(1, 12).Value = Me.PageText2.Value
           
                .Cells(1, 15).Value = Me.VolText3.Value
                .Cells(1, 16).Value = Me.PageText3.Value
           
                Exit Sub
           
            End If
        End With
    Next
End Sub

Private Sub CancelComm_Click()
    Unload Me
End Sub

---

Hey Paul,

This is fantastic, if I understand the code correctly, then yes this is exactly what I would like to have happen. I'm going to give it a try.

It also occurred to me while I was going through the forum a moment ago that I haven't set any parameters on what happens once the County Combo Box is selected. Now that I think about it though, once it has been selected, then it makes sense that I set code to populate the Property Description text boxt and the Assignment, Affidavit, and Probate Frames. After I check out the code you've provided, then I'm going to work on getting writing the code for the CCombo Change Event, after which I believe I will be all done :)!!!!!!

Thanks and I'll let you know how it goes,
Mathew

Paul,

Once again, THANK YOU!!!

As with almost all of the VBA Coding that I have come across the answer always makes perfect sense to me, but I am yet to develop either the confidence or the practical know-how (either or both :P) to actually jump out on a limb and start writing code for any given command. Now though, I think I'll be able to modify my Option Button code for Recorded/Not-Recorded to update the Assignment, Affidavit, and Probate Frames. I really can't convey my appreciation enough.

Now, I have a link to a tutorial on calling the information in my Text Boxes once a Drop Down Selection has been made, and I am going to bury my nose in it until I have completed my form. I'm stoked as I can now get down to the work that needs to be done of filling my Spreadsheet out!!

Thank you,
Mathew

Hey Paul or anyone that might be feeling charitable,

To save myself even more time, I decided I would throw these two questions (implied in my last two responses) out there and see if I could get a few lines of code to finish up my Form for good.

Question 1: What code would I use for the form to populate the PropDesc TxtBx, Assignment, Affidavit, and Probate Frames from the Row that County is in for the Original Owner and Heir chosen?
Essentially I am seeing it as similar to the Update Button Code but with the Equals to Values after my Then statement switched. Here is the idea:

Code:

---

Private Sub CCombo_Change

Dim rRow As Range
   
    For Each rRow In Worksheets("Royalty_Estates_Main").Cells(1, 1).CurrentRegion.Rows
        With rRow
            If .Cells(1, 1).Value = Me.OOCombo.Value And _
            .Cells(1, 2).Value = Me.HCombo.Value And _
            .Cells(1, 3).Value = Me.CCombo.Value Then
               
                Me.PropText.Value = .Cells(1, 4).Value
                               
                Me.VolText1.Value = .Cells(1, 7).Value
                Me.PageText1.Value = .Cells(1, 8).Value
               
                Me.VolText2.Value = .Cells(1, 11).Value
                Me.PageText2.Value = .Cells(1, 12).Value
               
                Me.VolText2.Value = .Cells(1, 15).Value
                Me.PageText2.Value = .Cells(1, 16).Value
               
                Exit Sub
               
            End If
        End With
    Next
End Sub

---

How does this look, seems like it would do the job :)?

Next, my Option Buttons for the Assignment, Affidavit, and Probate Frames.
I would like the selection of the Recorded option to change the Cell field to say "Recorded" for the Assignment, Affidavit, and Probate Cells on the Row of the chosen County. Here is the code that I have so far:

Code:

---

Private Sub AffHrNRecOB_Click()

If AffHrNRecOB.Value = True Then Range().Value = ""

End Sub

Private Sub AffHrRecOB_Click()

If AffHrRecOB.Value = True Then Range().Value = "Recorded"

End Sub

Private Sub AsgnNRecOB_Click()

If AsgnNRecOB.Value = True Then Range().Value = ""

End Sub

Private Sub AsgnRecOB_Click()

If AsgnRecOB.Value = True Then Range().Value = "Recorded"

End Sub

Private Sub ProbNRecOB_Click()

If ProbNRecOB.Value = True Then Range().Value = ""

End Sub

Private Sub ProbRecOB_Click()

If ProbRecOB.Value = True Then Range().Value = "Recorded"

End Sub

---

Question 2: You will notice that I have not Defined the Range for the Value.
How do I define the Range so that it fills in these Fields on the Row of the selected County?
Also, how would I get the Option Button to reflect the current Value (i.e. with the correct Option Button - Recorded or Not-Recorded - selected) once the County has been selected, in line with populating the rest of the Form?

Y'all are the best, thanks for helping me out.
Mathew

UPDATE: Nevermind Question 1. It Works!! :)

As for Question 2. About the Range for my Option Boxes... that still stands, any help is greatly appreciated.

Oh Yes, Sorry p45cal I did see your post, and meant to respond sooner. I posted this question on the ExcelForum, Mr.Excel, and Ozgrid Forums. I'm going to head over and update them to notify that I have found an answer in a few minutes.

Thanks,
Mathew

Quote:

---

Question 2: You will notice that I have not Defined the Range for the Value.
How do I define the Range so that it fills in these Fields on the Row of the selected County?
Also, how would I get the Option Button to reflect the current Value (i.e. with the correct Option Button - Recorded or Not-Recorded - selected) once the County has been selected, in line with populating the rest of the Form?

---

I'd capture the status in the Update sub and not try to use the Click event

Something similar to this maybe??

Code:

---


If ProbRecOB.Value Then .cells(1,xxxx).Value = "Recorded" (I don't know which column)

---

Paul, You sir are a Gentleman and a Scholar.

The suggested code worked perfectly, and I switched it around in the CCombo_Change Sub setting the OB.Value = True if the Cell has "Recorded" in it, etc.

I cannot express in words my appreciation. My Form is complete, and I can focus on filling out the 4500 some odd entries :).

Thank you,
Mathew

This might be helpful too:

http://www.snb-vba.eu/VBA_Userform_in_database_en.html

Thanks snb,

This looks like a good resource. I've bookmarked it, and I will get back to it and check it out as needed.

Once again, to everyone, Paul, p45cal, ranman, snb... thank you for the help. I was really starting to worry that I would be spending more time programming my form than actually working on filling in the spreadsheet. I know this sounds a little petty but I can only spend about an hour filling a spreadsheet out at a time before I start to get a headache. Not sure why as I'm normally very patient, but having the form will let me really buckle down and get tons more done at once. Hell, I spent almost three hours in front of it last night before I logged in to work at 1 am.