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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
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 |