| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box if... then... else question
Hello, I would like to change a cell source base once a selection made from a
combo box. After making a selection from the combo box the code should do something like: If MyComboBox = €śtest1€ť then B1 = Sheet2!A1 Else B1 has no data source (null) End if Whats the easiest way to accomplish my goal in Excel 2007? PS. B1 is the cell in my fist Sheet. Thank you, Silvio |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combo Box if... then... else question
Assuming you named your Combobox MyComboBox this should work for you. Put
this code into the ComboBox_Change Event. This will fire each time someone changes the combobox value. Hope this helps! If so, let me know, click "YES" below. Private Sub MyComboBox_Change() If MyComboBox = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Hello, I would like to change a cell source base once a selection made from a combo box. After making a selection from the combo box the code should do something like: If MyComboBox = €śtest1€ť then B1 = Sheet2!A1 Else B1 has no data source (null) End if Whats the easiest way to accomplish my goal in Excel 2007? PS. B1 is the cell in my fist Sheet. Thank you, Silvio |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combo Box if... then... else question
Hi Ryan, I am not sure if I am missing something but regardless of my
selection from the combo boix the "else" condition always fires up even though "test1"was selected. Any idea? "Ryan H" wrote: Assuming you named your Combobox MyComboBox this should work for you. Put this code into the ComboBox_Change Event. This will fire each time someone changes the combobox value. Hope this helps! If so, let me know, click "YES" below. Private Sub MyComboBox_Change() If MyComboBox = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Hello, I would like to change a cell source base once a selection made from a combo box. After making a selection from the combo box the code should do something like: If MyComboBox = €śtest1€ť then B1 = Sheet2!A1 Else B1 has no data source (null) End if Whats the easiest way to accomplish my goal in Excel 2007? PS. B1 is the cell in my fist Sheet. Thank you, Silvio |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combo Box if... then... else question
Check your spelling. The Else part will only execute if the first part is
not true. "Silvio" wrote in message ... Hi Ryan, I am not sure if I am missing something but regardless of my selection from the combo boix the "else" condition always fires up even though "test1"was selected. Any idea? "Ryan H" wrote: Assuming you named your Combobox MyComboBox this should work for you. Put this code into the ComboBox_Change Event. This will fire each time someone changes the combobox value. Hope this helps! If so, let me know, click "YES" below. Private Sub MyComboBox_Change() If MyComboBox = "test1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Hello, I would like to change a cell source base once a selection made from a combo box. After making a selection from the combo box the code should do something like: If MyComboBox = "test1" then B1 = Sheet2!A1 Else B1 has no data source (null) End if What's the easiest way to accomplish my goal in Excel 2007? PS. B1 is the cell in my fist Sheet. Thank you, Silvio |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combo Box if... then... else question
Ryan, I inserted a break to debug the code and it always tells me that
MyComboBox = Empty "Ryan H" wrote: Assuming you named your Combobox MyComboBox this should work for you. Put this code into the ComboBox_Change Event. This will fire each time someone changes the combobox value. Hope this helps! If so, let me know, click "YES" below. Private Sub MyComboBox_Change() If MyComboBox = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Hello, I would like to change a cell source base once a selection made from a combo box. After making a selection from the combo box the code should do something like: If MyComboBox = €śtest1€ť then B1 = Sheet2!A1 Else B1 has no data source (null) End if Whats the easiest way to accomplish my goal in Excel 2007? PS. B1 is the cell in my fist Sheet. Thank you, Silvio |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combo Box if... then... else question
I did. I even changed the value to numeric (1, 2 ,3) and I am getting the
same thing. For some reason appears that the value selected from the combo box is not been detected and the else condition always get executed. "JLGWhiz" wrote: Check your spelling. The Else part will only execute if the first part is not true. "Silvio" wrote in message ... Hi Ryan, I am not sure if I am missing something but regardless of my selection from the combo boix the "else" condition always fires up even though "test1"was selected. Any idea? "Ryan H" wrote: Assuming you named your Combobox MyComboBox this should work for you. Put this code into the ComboBox_Change Event. This will fire each time someone changes the combobox value. Hope this helps! If so, let me know, click "YES" below. Private Sub MyComboBox_Change() If MyComboBox = "test1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Hello, I would like to change a cell source base once a selection made from a combo box. After making a selection from the combo box the code should do something like: If MyComboBox = "test1" then B1 = Sheet2!A1 Else B1 has no data source (null) End if What's the easiest way to accomplish my goal in Excel 2007? PS. B1 is the cell in my fist Sheet. Thank you, Silvio . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combo Box if... then... else question
Try adding .Value at the end of the Combobox in the If...Then statement.
Like this: Private Sub MyComboBox_Change() If MyComboBox.Value = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Ryan, I inserted a break to debug the code and it always tells me that MyComboBox = Empty "Ryan H" wrote: Assuming you named your Combobox MyComboBox this should work for you. Put this code into the ComboBox_Change Event. This will fire each time someone changes the combobox value. Hope this helps! If so, let me know, click "YES" below. Private Sub MyComboBox_Change() If MyComboBox = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Hello, I would like to change a cell source base once a selection made from a combo box. After making a selection from the combo box the code should do something like: If MyComboBox = €śtest1€ť then B1 = Sheet2!A1 Else B1 has no data source (null) End if Whats the easiest way to accomplish my goal in Excel 2007? PS. B1 is the cell in my fist Sheet. Thank you, Silvio |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combo Box if... then... else question
I did and I am getting a: Run-time error '424': Object required error message
if it makes any difference I am using excel 2007 and standart combo box (not active x control) "Ryan H" wrote: Try adding .Value at the end of the Combobox in the If...Then statement. Like this: Private Sub MyComboBox_Change() If MyComboBox.Value = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Ryan, I inserted a break to debug the code and it always tells me that MyComboBox = Empty "Ryan H" wrote: Assuming you named your Combobox MyComboBox this should work for you. Put this code into the ComboBox_Change Event. This will fire each time someone changes the combobox value. Hope this helps! If so, let me know, click "YES" below. Private Sub MyComboBox_Change() If MyComboBox = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Hello, I would like to change a cell source base once a selection made from a combo box. After making a selection from the combo box the code should do something like: If MyComboBox = €śtest1€ť then B1 = Sheet2!A1 Else B1 has no data source (null) End if Whats the easiest way to accomplish my goal in Excel 2007? PS. B1 is the cell in my fist Sheet. Thank you, Silvio |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combo Box if... then... else question
Try it with this syntax:
Private Sub MyComboBox_Change() If Me.MyComboBox.Value = "test1" Then Range("B1").Formula = "=Sheet2!A1" Else ActiveSheet.Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub "Silvio" wrote in message ... I did. I even changed the value to numeric (1, 2 ,3) and I am getting the same thing. For some reason appears that the value selected from the combo box is not been detected and the else condition always get executed. "JLGWhiz" wrote: Check your spelling. The Else part will only execute if the first part is not true. "Silvio" wrote in message ... Hi Ryan, I am not sure if I am missing something but regardless of my selection from the combo boix the "else" condition always fires up even though "test1"was selected. Any idea? "Ryan H" wrote: Assuming you named your Combobox MyComboBox this should work for you. Put this code into the ComboBox_Change Event. This will fire each time someone changes the combobox value. Hope this helps! If so, let me know, click "YES" below. Private Sub MyComboBox_Change() If MyComboBox = "test1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Hello, I would like to change a cell source base once a selection made from a combo box. After making a selection from the combo box the code should do something like: If MyComboBox = "test1" then B1 = Sheet2!A1 Else B1 has no data source (null) End if What's the easiest way to accomplish my goal in Excel 2007? PS. B1 is the cell in my fist Sheet. Thank you, Silvio . |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combo Box if... then... else question
Is you combobox name MyComboBox? To get the name of the combobox, click the
combobox so the dots show at the corners, then put this in the Immediate Window in VBE: ?Selection.Name Put that name here in this code. NOTE: This code goes in a standard module Sub ComboBoxChanger() If Sheets("Sheet1").DropDowns("NAME HERE") = "test1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub Now assign this sub to the combobox. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Silvio" wrote: I did and I am getting a: Run-time error '424': Object required error message if it makes any difference I am using excel 2007 and standart combo box (not active x control) "Ryan H" wrote: Try adding .Value at the end of the Combobox in the If...Then statement. Like this: Private Sub MyComboBox_Change() If MyComboBox.Value = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Ryan, I inserted a break to debug the code and it always tells me that MyComboBox = Empty "Ryan H" wrote: Assuming you named your Combobox MyComboBox this should work for you. Put this code into the ComboBox_Change Event. This will fire each time someone changes the combobox value. Hope this helps! If so, let me know, click "YES" below. Private Sub MyComboBox_Change() If MyComboBox = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Hello, I would like to change a cell source base once a selection made from a combo box. After making a selection from the combo box the code should do something like: If MyComboBox = €śtest1€ť then B1 = Sheet2!A1 Else B1 has no data source (null) End if Whats the easiest way to accomplish my goal in Excel 2007? PS. B1 is the cell in my fist Sheet. Thank you, Silvio |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combo Box if... then... else question
The control name is corrent. Interesting is that I have changed the the code
to validate the slection index number (test1 = 1, test2 = 2 test3 = 3) and not the spelling of the selection and appears to work fine. and using your original code with by using the index number and enclosing [MyComboBox] in nrackets als works. Here are both working methods: If Sheets("Sheet1").DropDowns("MyComboBox") = "1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If or Sub MyComboBox_Change() If [MyComboBox] = "1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub mmhhhhh why is that? "Ryan H" wrote: Is you combobox name MyComboBox? To get the name of the combobox, click the combobox so the dots show at the corners, then put this in the Immediate Window in VBE: ?Selection.Name Put that name here in this code. NOTE: This code goes in a standard module Sub ComboBoxChanger() If Sheets("Sheet1").DropDowns("NAME HERE") = "test1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub Now assign this sub to the combobox. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Silvio" wrote: I did and I am getting a: Run-time error '424': Object required error message if it makes any difference I am using excel 2007 and standart combo box (not active x control) "Ryan H" wrote: Try adding .Value at the end of the Combobox in the If...Then statement. Like this: Private Sub MyComboBox_Change() If MyComboBox.Value = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Ryan, I inserted a break to debug the code and it always tells me that MyComboBox = Empty "Ryan H" wrote: Assuming you named your Combobox MyComboBox this should work for you. Put this code into the ComboBox_Change Event. This will fire each time someone changes the combobox value. Hope this helps! If so, let me know, click "YES" below. Private Sub MyComboBox_Change() If MyComboBox = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Hello, I would like to change a cell source base once a selection made from a combo box. After making a selection from the combo box the code should do something like: If MyComboBox = €śtest1€ť then B1 = Sheet2!A1 Else B1 has no data source (null) End if Whats the easiest way to accomplish my goal in Excel 2007? PS. B1 is the cell in my fist Sheet. Thank you, Silvio |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combo Box if... then... else question
The code below will only be TRUE if your comboboxs value is a string = "1"
all other cases will cause the Else portion of the If...Then statement to run. The .ListIndex property has no effect on this code. Sub StandardSubCode() If Sheets("Sheet1").DropDowns("MyComboBox") = "1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub This code below, by putting brackets around it, you are asking for the ..ListIndex of the combobox. The List Index is the position of the value you selected in the list within the combobox. You really don't need the = "1", I would just make it = 1. By putting the brackets a Double data type is returned, so basically what VB is doing is converting your string = "1" to a double = 1. It just so happens VB can do that. Sub DropDown1_Change() If [MyComboBox] = 1 Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub Did I confuse you? Hope this helps! If so, let me know, and click "YES" below. -- Cheers, Ryan "Silvio" wrote: The control name is corrent. Interesting is that I have changed the the code to validate the slection index number (test1 = 1, test2 = 2 test3 = 3) and not the spelling of the selection and appears to work fine. and using your original code with by using the index number and enclosing [MyComboBox] in nrackets als works. Here are both working methods: If Sheets("Sheet1").DropDowns("MyComboBox") = "1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If or Sub MyComboBox_Change() If [MyComboBox] = "1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub mmhhhhh why is that? "Ryan H" wrote: Is you combobox name MyComboBox? To get the name of the combobox, click the combobox so the dots show at the corners, then put this in the Immediate Window in VBE: ?Selection.Name Put that name here in this code. NOTE: This code goes in a standard module Sub ComboBoxChanger() If Sheets("Sheet1").DropDowns("NAME HERE") = "test1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub Now assign this sub to the combobox. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Silvio" wrote: I did and I am getting a: Run-time error '424': Object required error message if it makes any difference I am using excel 2007 and standart combo box (not active x control) "Ryan H" wrote: Try adding .Value at the end of the Combobox in the If...Then statement. Like this: Private Sub MyComboBox_Change() If MyComboBox.Value = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Ryan, I inserted a break to debug the code and it always tells me that MyComboBox = Empty "Ryan H" wrote: Assuming you named your Combobox MyComboBox this should work for you. Put this code into the ComboBox_Change Event. This will fire each time someone changes the combobox value. Hope this helps! If so, let me know, click "YES" below. Private Sub MyComboBox_Change() If MyComboBox = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Hello, I would like to change a cell source base once a selection made from a combo box. After making a selection from the combo box the code should do something like: If MyComboBox = €śtest1€ť then B1 = Sheet2!A1 Else B1 has no data source (null) End if Whats the easiest way to accomplish my goal in Excel 2007? PS. B1 is the cell in my fist Sheet. Thank you, Silvio |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combo Box if... then... else question
Yes I am confused but as log it works that's what I really care. Thank you so
much for you help. "Ryan H" wrote: The code below will only be TRUE if your comboboxs value is a string = "1" all other cases will cause the Else portion of the If...Then statement to run. The .ListIndex property has no effect on this code. Sub StandardSubCode() If Sheets("Sheet1").DropDowns("MyComboBox") = "1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub This code below, by putting brackets around it, you are asking for the .ListIndex of the combobox. The List Index is the position of the value you selected in the list within the combobox. You really don't need the = "1", I would just make it = 1. By putting the brackets a Double data type is returned, so basically what VB is doing is converting your string = "1" to a double = 1. It just so happens VB can do that. Sub DropDown1_Change() If [MyComboBox] = 1 Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub Did I confuse you? Hope this helps! If so, let me know, and click "YES" below. -- Cheers, Ryan "Silvio" wrote: The control name is corrent. Interesting is that I have changed the the code to validate the slection index number (test1 = 1, test2 = 2 test3 = 3) and not the spelling of the selection and appears to work fine. and using your original code with by using the index number and enclosing [MyComboBox] in nrackets als works. Here are both working methods: If Sheets("Sheet1").DropDowns("MyComboBox") = "1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If or Sub MyComboBox_Change() If [MyComboBox] = "1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub mmhhhhh why is that? "Ryan H" wrote: Is you combobox name MyComboBox? To get the name of the combobox, click the combobox so the dots show at the corners, then put this in the Immediate Window in VBE: ?Selection.Name Put that name here in this code. NOTE: This code goes in a standard module Sub ComboBoxChanger() If Sheets("Sheet1").DropDowns("NAME HERE") = "test1" Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub Now assign this sub to the combobox. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Silvio" wrote: I did and I am getting a: Run-time error '424': Object required error message if it makes any difference I am using excel 2007 and standart combo box (not active x control) "Ryan H" wrote: Try adding .Value at the end of the Combobox in the If...Then statement. Like this: Private Sub MyComboBox_Change() If MyComboBox.Value = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Ryan, I inserted a break to debug the code and it always tells me that MyComboBox = Empty "Ryan H" wrote: Assuming you named your Combobox MyComboBox this should work for you. Put this code into the ComboBox_Change Event. This will fire each time someone changes the combobox value. Hope this helps! If so, let me know, click "YES" below. Private Sub MyComboBox_Change() If MyComboBox = €śtest1€ť Then Range("B1").Formula = "=Sheet2!A1" Else Range("B1").Value = "" MsgBox "B1 has no data source.", vbInformation End If End Sub -- Cheers, Ryan "Silvio" wrote: Hello, I would like to change a cell source base once a selection made from a combo box. After making a selection from the combo box the code should do something like: If MyComboBox = €śtest1€ť then B1 = Sheet2!A1 Else B1 has no data source (null) End if Whats the easiest way to accomplish my goal in Excel 2007? PS. B1 is the cell in my fist Sheet. Thank you, Silvio |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| combo box question | Excel Programming | |||
| Combo Box Question | Excel Worksheet Functions | |||
| Combo Box Question | Excel Programming | |||
| Combo box question | Excel Programming | |||
| Combo Box Question | Excel Programming | |||
Linear Mode
