Cell Values On User Form
Gregor,
Post the code.
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"gregork" wrote in message
...
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
"Bob Phillips" wrote in message
...
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)
"gregork" wrote in message
...
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
"Bob Phillips" wrote in message
...
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)
"gregork" wrote in message
...
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
|