Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo Box - Set Value in Macro
Hi, I have a combobox (cboReview), I would like to set its value (to "N/A")
through a Macro that also does a few other things. I'm can't figure out the correct syntax. Also, does the value I set it to need to be an item in the Range that provides the list or not (N/A is in the range but doesn't need to be if that isn't required)? Stored keystrokes didn't help at all. Thank you. Karin R. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo Box - Set Value in Macro
I'm guessing that this is a combobox on a userform--not a dropdown from the
Forms toolbar placed on a worksheet and not a combobox from the control toolbox toolbar placed on a worksheet. If that's true, then it depends on the style of the combobox. If you look at the properties for that combobox, you'll see a Style property. That has two options--one is fmStyleDropDownCombo and the other is fmStyleDropDownList. The List means that it has to match something in the list. The Combo means that a second property is looked at (MatchRequired). When I set that to true, I could change the value displayed in that combobox to look like #n/a, but I couldn't leave the combobox without changing it to something on that list. This was behind my test userform: Option Explicit Private Sub CommandButton1_Click() If Me.ComboBox1.ListIndex < 0 Then Beep 'nothing selected Else MsgBox Me.ComboBox1.Value End If End Sub Private Sub CommandButton2_Click() Unload Me End Sub Private Sub CommandButton3_Click() Me.ComboBox1.Value = "#N/A" End Sub Private Sub UserForm_Initialize() With Me.ComboBox1 .RowSource = ThisWorkbook.Worksheets("Sheet1") _ .Range("A1:a10").Address(external:=True) .Style = fmStyleDropDownCombo .MatchRequired = True End With End Sub ======== I'm not sure what you're doing, but instead of showing #N/A in the combobox, maybe you could use a label and even disable the combobox. Karin wrote: Hi, I have a combobox (cboReview), I would like to set its value (to "N/A") through a Macro that also does a few other things. I'm can't figure out the correct syntax. Also, does the value I set it to need to be an item in the Range that provides the list or not (N/A is in the range but doesn't need to be if that isn't required)? Stored keystrokes didn't help at all. Thank you. Karin R. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo Box - Set Value in Macro
Thank you - and now I know to be more specific about what type of combo box
- sorry, it came from the control box. What I ended up doing was hiding the box and typing the N/A in the cell using a macro (based on the results of a different combo box). "Dave Peterson" wrote: I'm guessing that this is a combobox on a userform--not a dropdown from the Forms toolbar placed on a worksheet and not a combobox from the control toolbox toolbar placed on a worksheet. If that's true, then it depends on the style of the combobox. If you look at the properties for that combobox, you'll see a Style property. That has two options--one is fmStyleDropDownCombo and the other is fmStyleDropDownList. The List means that it has to match something in the list. The Combo means that a second property is looked at (MatchRequired). When I set that to true, I could change the value displayed in that combobox to look like #n/a, but I couldn't leave the combobox without changing it to something on that list. This was behind my test userform: Option Explicit Private Sub CommandButton1_Click() If Me.ComboBox1.ListIndex < 0 Then Beep 'nothing selected Else MsgBox Me.ComboBox1.Value End If End Sub Private Sub CommandButton2_Click() Unload Me End Sub Private Sub CommandButton3_Click() Me.ComboBox1.Value = "#N/A" End Sub Private Sub UserForm_Initialize() With Me.ComboBox1 .RowSource = ThisWorkbook.Worksheets("Sheet1") _ .Range("A1:a10").Address(external:=True) .Style = fmStyleDropDownCombo .MatchRequired = True End With End Sub ======== I'm not sure what you're doing, but instead of showing #N/A in the combobox, maybe you could use a label and even disable the combobox. Karin wrote: Hi, I have a combobox (cboReview), I would like to set its value (to "N/A") through a Macro that also does a few other things. I'm can't figure out the correct syntax. Also, does the value I set it to need to be an item in the Range that provides the list or not (N/A is in the range but doesn't need to be if that isn't required)? Stored keystrokes didn't help at all. Thank you. Karin R. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use Combo Box value to determine macro used | Excel Worksheet Functions | |||
Macro to be used with Combo box | Excel Worksheet Functions | |||
assign a macro to a combo box | Excel Discussion (Misc queries) | |||
Combo Box and Macro | Excel Discussion (Misc queries) | |||
Macro that triggers off a combo box | Excel Discussion (Misc queries) |