Thread: Using a lookup function as a part of a form

KaitMacN,

Any chance you could post a copy of the workbook (with all sensitive data deleted or changed) in xls format. It would be easier to give you customized advice without having to guess/recreate the userforms.

In general terms, you'd likely need to use the TextBox_Exit event as a trigger to look up the room number in column A. The value in the Textbox can be treated as a variable, and the .Find method can be used to find it in column A. Then, the .Offset method can be used to pull information in subsequent columns from the same row.

Yes of couse!
I am just starting using VBA so if anyone has a suggestion for a good place to have programming talk "translated" into english that would be great as well.

Thanks

KaitMacN,

I'm attaching a new copy of your workbook. I included a new userform (UserForm2) which demonstrates how I would work this. I included a new ComboBox that includes all of the room numbers to be selected. Once selected, the corresponding txtboxes are populated. Please note, I included random numbers in some of your cells. I did this simply as a test to make sure it's working correctly. You can delete them.

See attached and report back with any questions.

For some reason when I try getting a "run-time error '424' any ideas as to why this may be happening?
Thanks for all your help

Hi Kait,

Are you getting an error when you launch my userform, UserForm2? Or are you getting an error on your amended code? If the latter, can you please post the code that you are using?

Now I am not getting the error i did well im not sure what but when i click the "ENTER" button nothing is happening.
PS i love the drop down for the rooms

    Private Sub ComboBox1_Click()
        Dim ws As Worksheet
        Dim rng As Range
        Dim fRng As Range
        Dim fRow As Long
        Dim cbo As String
        
        Set ws = Sheets("Sheet1")
        Set rng = ws.Range("A6:A" & ws.UsedRange.Rows.Count)
        
        cbo = ComboBox1.Value
        Set fRng = rng.Find(what:=cbo, after:=Range("A6"))
        
        txtName = CStr(fRng.Offset(, 1))
        txtRank = CStr(fRng.Offset(, 2))
        txtInitial = CStr(fRng.Offset(, 3))
        txtSN = CStr(fRng.Offset(, 4))
        txtResn = CStr(fRng.Offset(, 5))
        txtStart = CStr(fRng.Offset(, 6))
        txtEnd = CStr(fRng.Offset(, 7))
        txtUnit = CStr(fRng.Offset(, 8))
        txtRats = CStr(fRng.Offset(, 9))
        txtFin = CStr(fRng.Offset(, 10))
        txtReser = CStr(fRng.Offset(, 11))
            
        
    End Sub

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim LastRow As Long
    
    Set ws = Sheets("Sheet1")
    LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 6 To LastRow
        Me.ComboBox1.AddItem Cells(i, 1)
    Next i
    
End Sub

That is the only other code that is written

There's no code attached to the Enter button in userform2. As it stands, the code we have populates the comboboxes with data that already exists in the Worksheet based on the room selection. Is this what you needed to do initially?

Next, do you want to be able to modify the individual textboxes, then pass that information along to the Worksheet once the enter button is pressed? If so, let me know and we can work on some code for that.

In the end the document will be used to track room occupancy for long term stays. As such what I would LOVE when all is said in done is that:

select the room on the form, fill in the information on there and then press enter.
After pressing enter the computer will brilliantly locate the room and fill in the rest of the information!

I would also want to have another macro that lookups the room number and clears the cells for when people have moved out but I will be happy with the first part!

Did that make more sense? If I am saying the same thing that you are I appologize I was getting lost with comboboxes etc :P

I see what you need to do. I think I actually worked backwards, presuming the information was already in the form and you needed to populate it into the userform. As such, and I apologize, our previous code was a bit of a waste of time. (Hopefully, it was informative if nothing else).

I cleaned up the code a bit. Now you can add all of the relevant information and it gets populated into the worksheet. I used a loop this time for efficiency, so let me know if that is confusing. I also included a "Check Out" button which will clear entries for someone who has checked out. Let me know how its working out for you.

YAY!!
As that was working so well I decided to make it more complicated :P
I have included the attachment where I was trying to add checkboxes (sheet C46), i think i started the code in the right place however and being told that the sub or function is not defined.
I really appreciate all the assistance with this I am MAYBE starting to get bits and pieces.

Change your command button click even to the following:

Private Sub cmdB77_Click()
    Dim ws As Worksheet
    Dim rng As Range
    Dim fRng As Range
    Dim fRow As Long
    Dim cbo As String
    Dim i As Integer
    
    Set ws = Sheets("C46")
    Set rng = ws.Range("A5:A" & ws.UsedRange.Rows.Count)
    
    cbo = ComboBox1.Value
    Set fRng = rng.Find(what:=cbo, after:=Range("A5"))
        
    For i = 1 To 10
        fRng.Offset(, i) = Me.Controls("txt" & i).Value
    Next i
    
    
    
    Dim c As Control
    For Each c In C46.Controls
        If TypeName(c) = "TextBox" Then
            c.Value = ""
        End If
    Next c
    
    If Rats Then fRng.Offset(, 11) = "YES"
    If FinCode Then fRng.Offset(, 12) = "YES"
    If Reserves Then fRng.Offset(, 13) = "YES"
End Sub