Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display contents of a cell in a user form text box -- Excel 2003 VBA | Excel Discussion (Misc queries) | |||
how do i cancel user restricted values in a cell | New Users to Excel | |||
How do I fill a cell in a user form from a selection on same form? | Excel Discussion (Misc queries) | |||
How do i fill the adjacent cell formulas in user form when i press | Excel Discussion (Misc queries) | |||
I am looking to see if anybody has an equivalant user form to Outlooks CONTACT form | Excel Programming |