Cell Values On User Form
 

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

Cell Values On User Form
 

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

Cell Values On User Form
 

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

Cell Values On User Form
 

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

Cell Values On User Form
 

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

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

Cell Values On User Form
 

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


"Bob Phillips" wrote in message
...
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

Cell Values On User Form
 

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)

Cell Values On User Form
 

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

"Bob Phillips" wrote in message
...
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)

Cell Values On User Form
 

Gregor,

A Dropdown (Listbox or Combobox) is a control in its own right, as is a
Textbox. Thus you can't have a textbox that has a dropdown.

--

HTH

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

"gregork" wrote in message
...
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

"Bob Phillips" wrote in message
...
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)

Cell Values On User Form
 

Hey Guys,

I saw that you have been working on a very nice code...this helped me
as well! (thanks for that)

Now I do also have 2 questions:

1) Is it possible to safe changes you make in the data (the data that
comes up after the search) to the original rows, so that there are no
duplicates?

2) It is also possible go to the next matching record?

Thank you for your help in advance!

Greetings
Dingetje2000 (NL)


---
Message posted from http://www.ExcelForum.com/