Cell Values On User Form

G

gregork

Is it possible to have cell values displayed on a user form?
For example: When I enter a car registration number in a text box on a user
form I want information (e.g. make,model,etc...) to be displayed on the user
form.

Regards
GregK
 
B

Bob Phillips

Gregor,

Do you mean something like


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox2.Text = WorksheetFunction.VLookup(TextBox1.Text,
Worksheets("Sheet2").Range("A1:C10"), 2, False)
TextBox3.Text = WorksheetFunction.VLookup(TextBox1.Text,
Worksheets("Sheet2").Range("A1:C10"), 3, False)
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

gregork

Yes Bob that is exactly what I was after. Many thanks.
Only thing that bothers me is when I enter data that is not found in the
range I get a run time error which is a little ugly. Can I get a message box
or something to say " invalid data" or is it possible to use a dropdown list
for my text box 1 that contains all the entries I have in column 1 ?

Thanks
GregK
 
B

Bob Phillips

Gregor,

That is pretty straight-forward

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim nMatch As Long
With TextBox1
On Error Resume Next
nMatch = WorksheetFunction.Match(.Text, Range("A1:A10"), 0)
On eror GoTo 0
If nMatch <> 0 Then
TextBox2.Text = WorksheetFunction.VLookup(.Text, _
Worksheets("Sheet2").Range("A1:C10"), 2, False)
TextBox3.Text = WorksheetFunction.VLookup(.Text, _
Worksheets("Sheet2").Range("A1:C10"), 3, False)
Else
MsgBox "Value not found"
.SelLength = Len(.Text)
.SelStart = 0
.SetFocus
Cancel = True
End If
End With
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

gregork

Many thanks again Bob.....I have one small problem - on my form I also have
an exit button. When I click it the "value not found" message comes up and I
can't get out of the form?

Regards
Gregk
 
B

Bob Phillips

Gregor,

Post the code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

gregork

Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheet1.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
MsgBox "One record written to Sheet1"


response = MsgBox("Do you want to enter another record?", vbYesNo)


If response = vbYes Then
TextBox1.Text = ""


TextBox1.SetFocus

Else
Unload Me
End If

End Sub



Private Sub CommandButton2_Click()
End
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim nMatch As Long
With TextBox1
On Error Resume Next
nMatch = WorksheetFunction.Match(.Text, Range("A1:A10"), 0)
On Eror GoTo 0
If nMatch <> 0 Then
TextBox2.Text = WorksheetFunction.VLookup(.Text,
Worksheets("Sheet2").Range("A1:C10"), 2, False)
TextBox3.Text = WorksheetFunction.VLookup(.Text,
Worksheets("Sheet2").Range("A1:C10"), 3, False)
Else
MsgBox "Value Not Found"
.SelLength = Len(.Text)
.SelStart = 0
.SetFocus
Cancel = True
End If
End With
End Sub




Private Sub UserForm_Click()

End Sub
 
B

Bob Phillips

Gregor,

I assume that you are getting that problem when nothing is entered in
Textbox1? If so, this should take care of it

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim nMatch As Long
With TextBox1
If .Text <> "" Then
On Error Resume Next
nMatch = WorksheetFunction.Match(.Text, Range("A1:A10"), 0)
On Error GoTo 0
If nMatch <> 0 Then
TextBox2.Text = WorksheetFunction.VLookup(.Text, _
Worksheets("Sheet2").Range("A1:C10"), 2, False)
TextBox3.Text = WorksheetFunction.VLookup(.Text, _
Worksheets("Sheet2").Range("A1:C10"), 3, False)
Else
MsgBox "Value Not Found"
.SelLength = Len(.Text)
.SelStart = 0
.SetFocus
Cancel = True
End If
End If
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

gregork

Thank you Bob, I really appreciate your help, as you would have noticed I'm
very inexperienced with VBA but I have learnt allot tonight just from
fiddling around with these codes you've kindly provided. There is one more
thing I would really like to know how to do - that is how do you insert
dropdown lists on a form ? On my sheet for example - I would like text box1
to have a drop down list of the values in sheet 2 Column A.

Regards
gregK
 
Top