Simple user form help

[Rick50]

I am a VBA novice. I am trying to create a user form that performs a search and returns cell values (mostly text) from an excel spreadsheet that is not already open.
The user form would have 1 box for the user to enter (type in) what they are looking for. They would then hit a search button. And It would populate 3 other boxes in the user form displaying values found on the same row but in other columns (like a vlookup).
I tried this with a drop down list I received from another post on the MrExcel forum but because I plan on adding additional search buttons that link to different spreadsheets the drop down combo box won’t work for me. Plus I didn’t get it right as it kept coding out on me.
I was informed it was best to use an array, but again my end-state is to have multiple search buttons so after reading a bit more it seemed an array would not work. Once I get it right once I’ll just plagiarize it adding the spreadsheet differences, error handling, etc. Any help would truly be appreciated.

 

[Akashwani]

Hi Rick,

Welcome to MrExcel the best excel site around.

I'm not an expert and I can not help you with vba,
but this may do what you want.....

http://www.contextures.com/xlUserForm01.html

Good luck.

Ak

 

[Rick50]

Thank you, but this is exactly the site I used the 1st time. However, it provides drop down boxes instead of showing me how to use different spread sheets and search buttons.

 

[Akashwani]

Hi Rick,

It sounds like you are trying to do something like this....

http://www.mrexcel.com/forum/showthread.php?t=388053

Ak

 

[Rick50]

Here is my code. I type in the value on the user form in Cbox1.
It continues to come up as "value not found." I also tried Find(VAL(cbox1, but still get nothing.
Sub Mylook()
Dim wsn1 As Range
Set wsn1 = Sheets("uic list").Range("A1:A288").Find(Cbox1, lookat:=xlWhole, LookIn:=xlValues)
If wsn1 Is Nothing Then
MsgBox "Value not found"
Else
'Populate the Myfrm1 with data
TextBox2 = wsn1.Offset(0, 1) 'Down 1 row, over 1 column
TextBox3 = wsn1.Offset(0, 2) 'Down row, over 2 column
'
'
'and 68 more
End If
End Sub

Any Help?

 

[Akashwani]

Hi Rick,

To lookup a numeric value on a sheet (Master) and to return the data in that row to a UserForm I use this code, perhaps you can adapt it...

Private Sub tbLookFor_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
                               ByVal Shift As Integer)
    Dim Product As Range
    Dim x As Long, y As Long
    If KeyCode = 13 And IsNumeric(tbLookFor.Value) = True Then
    
       
    Set Product = Sheets("Master").Range("A1:A300").Find(Val(tbLookFor), lookat:=xlWhole, LookIn:=xlValues)
    If Product Is Nothing Then
        MsgBox "Incorrect Product code, try again"

    Else
        For x = 1 To 97
            If x < 5 Then
                y = x
             Else
                y = x + 1
            End If
            Me.Controls("tbData" & x) = Product.Offset(0, y)
        Next
        
    End If
End If

End Sub

tbLookFor is the name of the text box where I type in the number I am looking for.
tbData is the name of the text boxes where the data from sheet Master is displayed on the UserForm, eg tbData1, tbData2 etc to tbData96

Perhaps your code would be something like this...

 Private Sub tbLookFor_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
                               ByVal Shift As Integer)
    Dim wsn1As Range
    Dim x As Long, y As Long
    If KeyCode = 13 And IsNumeric(Mylook.Value) = True Then
    
       
    Set wsn1 = Sheets("uic list").Range("A1:A288").Find(Val(Mylook), lookat:=xlWhole, LookIn:=xlValues)
    If wsn1 Is Nothing Then
        MsgBox "Incorrect Product code, try again"

    Else
        For x = 1 To 97
            If x < 5 Then
                y = x
             Else
                y = x + 1
            End If
            Me.Controls("TextBox" & x) = wsn1.Offset(0, y)
        Next

    End If
End If

End Sub

I hope that helps.

Ak