Consulting

Results 1 to 13 of 13

Thread: Data Entry UserForm for Excel 2007

  1. #1

    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):
    UserFormImage.JPG ExcelFields.JPG
    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:

    EstateRoyaltyAssetsSample.xlsm

  2. #2
    VBAX Tutor
    Joined
    Mar 2014
    Posts
    210
    Location
    This would be a whole class on VB. Forms require lots o code.

  3. #3
    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:


    '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.

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,571
    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.

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,641
    Location
    No real error checking but some thoughts on what I think you're looking to do

    '---------------------------------------------------
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    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

  7. #7
    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

  8. #8
    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:
    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:
    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

  9. #9
    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

  10. #10
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,641
    Location
    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??


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

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    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

  12. #12
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,517

  13. #13
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •