Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
txtBox/VLOOKUP
Hello,
I'm new to VB programming (but not Excel), so please bear with me. I'm creating a usrFrm with a cboBox (Project Name) & txtBox (Project No.). I want the txtBox to return the corresponding value depending on what's been selected in the cboBox. The cbo values are derived from a table on a worksheet: Project name Project no. a 1 b 2 etc, Question: 1)would I need to use VLOOKUP in VB? If so, how? 2)how do I get the result of the VLOOKUP to be the value in the txtBox? Your help is very much appreciated. Mac |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
txtBox/VLOOKUP
In the combo click event
With Me .txtBox.Text = Application.Vlookup(.cboBox.Value,Worksheets("Shee t1").Range(A2:B20),2,False ) End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "CMcK" wrote in message ... Hello, I'm new to VB programming (but not Excel), so please bear with me. I'm creating a usrFrm with a cboBox (Project Name) & txtBox (Project No.). I want the txtBox to return the corresponding value depending on what's been selected in the cboBox. The cbo values are derived from a table on a worksheet: Project name Project no. a 1 b 2 etc, Question: 1)would I need to use VLOOKUP in VB? If so, how? 2)how do I get the result of the VLOOKUP to be the value in the txtBox? Your help is very much appreciated. Mac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
txtBox/VLOOKUP
Or in the combobox Change Event:
Private Sub ComboBox1_Change() Me.TextBox1.Value = WorksheetFunction.VLookup _ (ComboBox1.Value, Sheet1.Range("A2:B9"), 2, False) End Sub Of course, change the Sheet1.Range("A2:B9") to your lokup range. Mike F "Bob Phillips" wrote in message ... In the combo click event With Me .txtBox.Text = Application.Vlookup(.cboBox.Value,Worksheets("Shee t1").Range(A2:B20),2,False ) End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "CMcK" wrote in message ... Hello, I'm new to VB programming (but not Excel), so please bear with me. I'm creating a usrFrm with a cboBox (Project Name) & txtBox (Project No.). I want the txtBox to return the corresponding value depending on what's been selected in the cboBox. The cbo values are derived from a table on a worksheet: Project name Project no. a 1 b 2 etc, Question: 1)would I need to use VLOOKUP in VB? If so, how? 2)how do I get the result of the VLOOKUP to be the value in the txtBox? Your help is very much appreciated. Mac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
txtBox/VLOOKUP
Bob,
Thanks for your swift response - very much appreciated. You had one minor error in your code - the VLOOKUP range cell reference needed to be in speech marks, ("X:X") - otherwise it did exactly what I wanted. CMcK "Bob Phillips" wrote: In the combo click event With Me .txtBox.Text = Application.Vlookup(.cboBox.Value,Worksheets("Shee t1").Range(A2:B20),2,False ) End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "CMcK" wrote in message ... Hello, I'm new to VB programming (but not Excel), so please bear with me. I'm creating a usrFrm with a cboBox (Project Name) & txtBox (Project No.). I want the txtBox to return the corresponding value depending on what's been selected in the cboBox. The cbo values are derived from a table on a worksheet: Project name Project no. a 1 b 2 etc, Question: 1)would I need to use VLOOKUP in VB? If so, how? 2)how do I get the result of the VLOOKUP to be the value in the txtBox? Your help is very much appreciated. Mac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
txtBox/VLOOKUP
Mike,
Thanks for your response. Unfortunately this code did not work. The data is held in a worksheet called 'Projects', so instead of referencing 'sheet1.range("X:X")' I used: worksheets.("Projects").range("X:X") and the following error came up: Run-time error 1004: Unable to get the Vlookup property of the Worksheet function class Not sure how to resolve this. Nonetheless, your efforts are genuinely appreciated. CMcK "Mike Fogleman" wrote: Or in the combobox Change Event: Private Sub ComboBox1_Change() Me.TextBox1.Value = WorksheetFunction.VLookup _ (ComboBox1.Value, Sheet1.Range("A2:B9"), 2, False) End Sub Of course, change the Sheet1.Range("A2:B9") to your lokup range. Mike F "Bob Phillips" wrote in message ... In the combo click event With Me .txtBox.Text = Application.Vlookup(.cboBox.Value,Worksheets("Shee t1").Range(A2:B20),2,False ) End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "CMcK" wrote in message ... Hello, I'm new to VB programming (but not Excel), so please bear with me. I'm creating a usrFrm with a cboBox (Project Name) & txtBox (Project No.). I want the txtBox to return the corresponding value depending on what's been selected in the cboBox. The cbo values are derived from a table on a worksheet: Project name Project no. a 1 b 2 etc, Question: 1)would I need to use VLOOKUP in VB? If so, how? 2)how do I get the result of the VLOOKUP to be the value in the txtBox? Your help is very much appreciated. Mac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
txtBox/VLOOKUP
Drop the first dot
change worksheets.("Projects").range("X:X") to Worksheets("Projects").range("X:X") Mike F "CMcK" wrote in message ... Mike, Thanks for your response. Unfortunately this code did not work. The data is held in a worksheet called 'Projects', so instead of referencing 'sheet1.range("X:X")' I used: worksheets.("Projects").range("X:X") and the following error came up: Run-time error 1004: Unable to get the Vlookup property of the Worksheet function class Not sure how to resolve this. Nonetheless, your efforts are genuinely appreciated. CMcK "Mike Fogleman" wrote: Or in the combobox Change Event: Private Sub ComboBox1_Change() Me.TextBox1.Value = WorksheetFunction.VLookup _ (ComboBox1.Value, Sheet1.Range("A2:B9"), 2, False) End Sub Of course, change the Sheet1.Range("A2:B9") to your lokup range. Mike F "Bob Phillips" wrote in message ... In the combo click event With Me .txtBox.Text = Application.Vlookup(.cboBox.Value,Worksheets("Shee t1").Range(A2:B20),2,False ) End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "CMcK" wrote in message ... Hello, I'm new to VB programming (but not Excel), so please bear with me. I'm creating a usrFrm with a cboBox (Project Name) & txtBox (Project No.). I want the txtBox to return the corresponding value depending on what's been selected in the cboBox. The cbo values are derived from a table on a worksheet: Project name Project no. a 1 b 2 etc, Question: 1)would I need to use VLOOKUP in VB? If so, how? 2)how do I get the result of the VLOOKUP to be the value in the txtBox? Your help is very much appreciated. Mac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
txtBox/VLOOKUP
Sorry about that, that will teach me to test it <g
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "CMcK" wrote in message ... Bob, Thanks for your swift response - very much appreciated. You had one minor error in your code - the VLOOKUP range cell reference needed to be in speech marks, ("X:X") - otherwise it did exactly what I wanted. CMcK "Bob Phillips" wrote: In the combo click event With Me .txtBox.Text = Application.Vlookup(.cboBox.Value,Worksheets("Shee t1").Range(A2:B20),2,False ) End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "CMcK" wrote in message ... Hello, I'm new to VB programming (but not Excel), so please bear with me. I'm creating a usrFrm with a cboBox (Project Name) & txtBox (Project No.). I want the txtBox to return the corresponding value depending on what's been selected in the cboBox. The cbo values are derived from a table on a worksheet: Project name Project no. a 1 b 2 etc, Question: 1)would I need to use VLOOKUP in VB? If so, how? 2)how do I get the result of the VLOOKUP to be the value in the txtBox? Your help is very much appreciated. Mac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping focus on a txtbox | Excel Programming | |||
txtbox problem | Excel Programming | |||
To put value from txtbox to cell? | Excel Programming | |||
Validateing a txtbox from form | Excel Programming | |||
txtbox code | Excel Programming |