Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking down a combobox
Is there a way to allow a combobox entry to be selected from a list, but to
disable any option to enter text manually? I know I can set the MatchRequired property to ensure that any text must match an item in the list, but this still allows typing to be done. I suspect I could use a ListBox, but I want the drop-down list to appear. Any thoughts? -- Ian -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking down a combobox
Ian,
A Listbox is the solution. What do you mean "I want the drop-down to appear" ? A Listbox will show the dropdown list. Please elaborate. MB "IanC" wrote in message news:bU_Un.51524$k15.28828@hurricane... Is there a way to allow a combobox entry to be selected from a list, but to disable any option to enter text manually? I know I can set the MatchRequired property to ensure that any text must match an item in the list, but this still allows typing to be done. I suspect I could use a ListBox, but I want the drop-down list to appear. Any thoughts? -- Ian -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking down a combobox
Hi MerseyBeat
Thanks for the quick response. As far as I understand it, you can scroll up/down through the contents of the list in a listbox, but there's no way to display the whole list to click on an entry without expanding the size of the listbox. A combobox has a drop-down arrow to the right of the text area which does this. In the help for ListBox Control it says "You can't drop text into a drop-down ListBox" but I can't find any way to make it drop down. Using Excel 2000. -- Ian -- "MerseyBeat" wrote in message ... Ian, A Listbox is the solution. What do you mean "I want the drop-down to appear" ? A Listbox will show the dropdown list. Please elaborate. MB "IanC" wrote in message news:bU_Un.51524$k15.28828@hurricane... Is there a way to allow a combobox entry to be selected from a list, but to disable any option to enter text manually? I know I can set the MatchRequired property to ensure that any text must match an item in the list, but this still allows typing to be done. I suspect I could use a ListBox, but I want the drop-down list to appear. Any thoughts? -- Ian -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking down a combobox
Is this a combobox from the Control toolbox toolbar (if it's on a a worksheet)
or a combobox on a userform (inside the VBE)? If yes, then look at the .Style property of that combobox. On 06/25/2010 04:44, IanC wrote: Is there a way to allow a combobox entry to be selected from a list, but to disable any option to enter text manually? I know I can set the MatchRequired property to ensure that any text must match an item in the list, but this still allows typing to be done. I suspect I could use a ListBox, but I want the drop-down list to appear. Any thoughts? -- Ian -- -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking down a combobox
Thanks Dave. fmStyleDropDownList appears to be just what I needed.
The only drawback is that I need to rewrite some of my code. When I select this, the Text property becomes invalid. I currently have "Select" appearing in each ComboBox to prompt the user and some code that looks for the absence of this to enable a command button (ie each ComboBox must have an option selected for the OK button to be enabled). I'm sure I'll find a way round it. -- Ian -- "Dave Peterson" wrote in message ... Is this a combobox from the Control toolbox toolbar (if it's on a a worksheet) or a combobox on a userform (inside the VBE)? If yes, then look at the .Style property of that combobox. On 06/25/2010 04:44, IanC wrote: Is there a way to allow a combobox entry to be selected from a list, but to disable any option to enter text manually? I know I can set the MatchRequired property to ensure that any text must match an item in the list, but this still allows typing to be done. I suspect I could use a ListBox, but I want the drop-down list to appear. Any thoughts? -- Ian -- -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking down a combobox
I've never seen that .text property break using this.
You may want to try again or post more details. On 06/25/2010 06:28, IanC wrote: Thanks Dave. fmStyleDropDownList appears to be just what I needed. The only drawback is that I need to rewrite some of my code. When I select this, the Text property becomes invalid. I currently have "Select" appearing in each ComboBox to prompt the user and some code that looks for the absence of this to enable a command button (ie each ComboBox must have an option selected for the OK button to be enabled). I'm sure I'll find a way round it. -- Ian -- "Dave wrote in message ... Is this a combobox from the Control toolbox toolbar (if it's on a a worksheet) or a combobox on a userform (inside the VBE)? If yes, then look at the .Style property of that combobox. On 06/25/2010 04:44, IanC wrote: Is there a way to allow a combobox entry to be selected from a list, but to disable any option to enter text manually? I know I can set the MatchRequired property to ensure that any text must match an item in the list, but this still allows typing to be done. I suspect I could use a ListBox, but I want the drop-down list to appear. Any thoughts? -- Ian -- -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking down a combobox
Hi Dave
I'm not trying to change the Text property in code, but in the Properties box in VBE. Initailly, I had Style = 0 - fmStyleDropDownCombo Text = Select When I changed Style to 2 - fmStyleDropDownList, Text automatically cleared. Now when I try to enter something in Text, I get "Could not set the Text property. Invalid property value." If I copy/paste an entry from the list this is accepted, but if I try to type anything in (even something in the list) I get the same failure. MatchEntry is set to 2 - fmMatchEntryNone and MatchRequired is set to False. Any ideas? I think I've figured out a workaround for my code if I have to change it. As an alternative to looking for the absence of "Select" with... If Me.ComboBox1.Text = "Select" Then Me.CommandButton1.Enabled = False I think I can set MatchRequired to True, then check the MatchFound property with ... If Me.ComboBox1.MatchFound = True Then Me.CommandButton1.Enabled = False That said, if I use MatchRequired/MatchFound and change my code to suit, I don't need to change the ComboBox Style as invalid entries will result in the OK button being disabled. -- Ian -- "Dave Peterson" wrote in message ... I've never seen that .text property break using this. You may want to try again or post more details. On 06/25/2010 06:28, IanC wrote: Thanks Dave. fmStyleDropDownList appears to be just what I needed. The only drawback is that I need to rewrite some of my code. When I select this, the Text property becomes invalid. I currently have "Select" appearing in each ComboBox to prompt the user and some code that looks for the absence of this to enable a command button (ie each ComboBox must have an option selected for the OK button to be enabled). I'm sure I'll find a way round it. -- Ian -- "Dave wrote in message ... Is this a combobox from the Control toolbox toolbar (if it's on a a worksheet) or a combobox on a userform (inside the VBE)? If yes, then look at the .Style property of that combobox. On 06/25/2010 04:44, IanC wrote: Is there a way to allow a combobox entry to be selected from a list, but to disable any option to enter text manually? I know I can set the MatchRequired property to ensure that any text must match an item in the list, but this still allows typing to be done. I suspect I could use a ListBox, but I want the drop-down list to appear. Any thoughts? -- Ian -- -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking down a combobox
If "Select" is an option in the combobox list, then you should be able to use
something like: Option Explicit Private Sub UserForm_Initialize() Dim iCtr As Long With Me.ComboBox1 .Style = fmStyleDropDownList 'first item .AddItem "(Select)" 'test data For iCtr = 1 To 5 .AddItem "A" & iCtr Next iCtr 'show that it works .Text = "(Select)" 'or even use the top choice '.ListIndex = 0 End With End Sub Personally, I'd modify all those settings via code. Then if (when!) I need the same kind of form in another project, I can just copy the code and not have to worry. On 06/25/2010 08:32, IanC wrote: Hi Dave I'm not trying to change the Text property in code, but in the Properties box in VBE. Initailly, I had Style = 0 - fmStyleDropDownCombo Text = Select When I changed Style to 2 - fmStyleDropDownList, Text automatically cleared. Now when I try to enter something in Text, I get "Could not set the Text property. Invalid property value." If I copy/paste an entry from the list this is accepted, but if I try to type anything in (even something in the list) I get the same failure. MatchEntry is set to 2 - fmMatchEntryNone and MatchRequired is set to False. Any ideas? I think I've figured out a workaround for my code if I have to change it. As an alternative to looking for the absence of "Select" with... If Me.ComboBox1.Text = "Select" Then Me.CommandButton1.Enabled = False I think I can set MatchRequired to True, then check the MatchFound property with ... If Me.ComboBox1.MatchFound = True Then Me.CommandButton1.Enabled = False That said, if I use MatchRequired/MatchFound and change my code to suit, I don't need to change the ComboBox Style as invalid entries will result in the OK button being disabled. -- Ian -- "Dave wrote in message ... I've never seen that .text property break using this. You may want to try again or post more details. On 06/25/2010 06:28, IanC wrote: Thanks Dave. fmStyleDropDownList appears to be just what I needed. The only drawback is that I need to rewrite some of my code. When I select this, the Text property becomes invalid. I currently have "Select" appearing in each ComboBox to prompt the user and some code that looks for the absence of this to enable a command button (ie each ComboBox must have an option selected for the OK button to be enabled). I'm sure I'll find a way round it. -- Ian -- "Dave wrote in message ... Is this a combobox from the Control toolbox toolbar (if it's on a a worksheet) or a combobox on a userform (inside the VBE)? If yes, then look at the .Style property of that combobox. On 06/25/2010 04:44, IanC wrote: Is there a way to allow a combobox entry to be selected from a list, but to disable any option to enter text manually? I know I can set the MatchRequired property to ensure that any text must match an item in the list, but this still allows typing to be done. I suspect I could use a ListBox, but I want the drop-down list to appear. Any thoughts? -- Ian -- -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking down a combobox
Hi Dave
"Select" is just the initial setting for the ComboBoxes. It isn't in the list. The intention is to remind the user what they need to do, but not to be a selectable item. I've been looking for a way to delete "Select" from the list as soon as another option is highlighted, but can't figure it out. I know it will require RemoveItem, but I two issues with this: 1. How do I work out how to determine whether "Select" is in the focus 2. How do I delete "Select" by name. -- Ian -- "Dave Peterson" wrote in message ... If "Select" is an option in the combobox list, then you should be able to use something like: Option Explicit Private Sub UserForm_Initialize() Dim iCtr As Long With Me.ComboBox1 .Style = fmStyleDropDownList 'first item .AddItem "(Select)" 'test data For iCtr = 1 To 5 .AddItem "A" & iCtr Next iCtr 'show that it works .Text = "(Select)" 'or even use the top choice '.ListIndex = 0 End With End Sub Personally, I'd modify all those settings via code. Then if (when!) I need the same kind of form in another project, I can just copy the code and not have to worry. On 06/25/2010 08:32, IanC wrote: Hi Dave I'm not trying to change the Text property in code, but in the Properties box in VBE. Initailly, I had Style = 0 - fmStyleDropDownCombo Text = Select When I changed Style to 2 - fmStyleDropDownList, Text automatically cleared. Now when I try to enter something in Text, I get "Could not set the Text property. Invalid property value." If I copy/paste an entry from the list this is accepted, but if I try to type anything in (even something in the list) I get the same failure. MatchEntry is set to 2 - fmMatchEntryNone and MatchRequired is set to False. Any ideas? I think I've figured out a workaround for my code if I have to change it. As an alternative to looking for the absence of "Select" with... If Me.ComboBox1.Text = "Select" Then Me.CommandButton1.Enabled = False I think I can set MatchRequired to True, then check the MatchFound property with ... If Me.ComboBox1.MatchFound = True Then Me.CommandButton1.Enabled = False That said, if I use MatchRequired/MatchFound and change my code to suit, I don't need to change the ComboBox Style as invalid entries will result in the OK button being disabled. -- Ian -- "Dave wrote in message ... I've never seen that .text property break using this. You may want to try again or post more details. On 06/25/2010 06:28, IanC wrote: Thanks Dave. fmStyleDropDownList appears to be just what I needed. The only drawback is that I need to rewrite some of my code. When I select this, the Text property becomes invalid. I currently have "Select" appearing in each ComboBox to prompt the user and some code that looks for the absence of this to enable a command button (ie each ComboBox must have an option selected for the OK button to be enabled). I'm sure I'll find a way round it. -- Ian -- "Dave wrote in message ... Is this a combobox from the Control toolbox toolbar (if it's on a a worksheet) or a combobox on a userform (inside the VBE)? If yes, then look at the .Style property of that combobox. On 06/25/2010 04:44, IanC wrote: Is there a way to allow a combobox entry to be selected from a list, but to disable any option to enter text manually? I know I can set the MatchRequired property to ensure that any text must match an item in the list, but this still allows typing to be done. I suspect I could use a ListBox, but I want the drop-down list to appear. Any thoughts? -- Ian -- -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking down a combobox
If "(Select)" isn't part of the list, then how do you get it to show up in the
combobox choices? I think it would be simpler to add a label with an instruction that told the user to select their choices. (Actually, I can't imagine anyone needing to be told how to use the comboboxes. With all the stuff on the web, it's pretty obvious what needs to be done.) Anyway, this worked ok for me: Option Explicit Dim BlkProc As Boolean Private Sub ComboBox1_Change() Dim myList As Variant Dim myVal As String Dim iCtr As Long If BlkProc = True Then Exit Sub End If With Me.ComboBox1 If .ListIndex < 0 Then Exit Sub 'nothing chosen End If If LCase(.List(0, 0)) = LCase("(Select)") Then myList = .List myVal = .Value BlkProc = True .Clear 'Skip the first entry For iCtr = 1 To UBound(myList, 1) .AddItem myList(iCtr, 0) Next iCtr .Value = myVal BlkProc = False End If End With End Sub Private Sub UserForm_Initialize() Dim iCtr As Long BlkProc = True With Me.ComboBox1 .Style = fmStyleDropDownList 'first item .AddItem "(Select)" 'test data For iCtr = 1 To 5 .AddItem "A" & iCtr Next iCtr 'show that it works .Text = "(Select)" 'or .ListIndex = 0 End With BlkProc = False End Sub On 06/25/2010 11:57, IanC wrote: Hi Dave "Select" is just the initial setting for the ComboBoxes. It isn't in the list. The intention is to remind the user what they need to do, but not to be a selectable item. I've been looking for a way to delete "Select" from the list as soon as another option is highlighted, but can't figure it out. I know it will require RemoveItem, but I two issues with this: 1. How do I work out how to determine whether "Select" is in the focus 2. How do I delete "Select" by name. -- Ian -- "Dave wrote in message ... If "Select" is an option in the combobox list, then you should be able to use something like: Option Explicit Private Sub UserForm_Initialize() Dim iCtr As Long With Me.ComboBox1 .Style = fmStyleDropDownList 'first item .AddItem "(Select)" 'test data For iCtr = 1 To 5 .AddItem "A"& iCtr Next iCtr 'show that it works .Text = "(Select)" 'or even use the top choice '.ListIndex = 0 End With End Sub Personally, I'd modify all those settings via code. Then if (when!) I need the same kind of form in another project, I can just copy the code and not have to worry. On 06/25/2010 08:32, IanC wrote: Hi Dave I'm not trying to change the Text property in code, but in the Properties box in VBE. Initailly, I had Style = 0 - fmStyleDropDownCombo Text = Select When I changed Style to 2 - fmStyleDropDownList, Text automatically cleared. Now when I try to enter something in Text, I get "Could not set the Text property. Invalid property value." If I copy/paste an entry from the list this is accepted, but if I try to type anything in (even something in the list) I get the same failure. MatchEntry is set to 2 - fmMatchEntryNone and MatchRequired is set to False. Any ideas? I think I've figured out a workaround for my code if I have to change it. As an alternative to looking for the absence of "Select" with... If Me.ComboBox1.Text = "Select" Then Me.CommandButton1.Enabled = False I think I can set MatchRequired to True, then check the MatchFound property with ... If Me.ComboBox1.MatchFound = True Then Me.CommandButton1.Enabled = False That said, if I use MatchRequired/MatchFound and change my code to suit, I don't need to change the ComboBox Style as invalid entries will result in the OK button being disabled. -- Ian -- "Dave wrote in message ... I've never seen that .text property break using this. You may want to try again or post more details. On 06/25/2010 06:28, IanC wrote: Thanks Dave. fmStyleDropDownList appears to be just what I needed. The only drawback is that I need to rewrite some of my code. When I select this, the Text property becomes invalid. I currently have "Select" appearing in each ComboBox to prompt the user and some code that looks for the absence of this to enable a command button (ie each ComboBox must have an option selected for the OK button to be enabled). I'm sure I'll find a way round it. -- Ian -- "Dave wrote in message ... Is this a combobox from the Control toolbox toolbar (if it's on a a worksheet) or a combobox on a userform (inside the VBE)? If yes, then look at the .Style property of that combobox. On 06/25/2010 04:44, IanC wrote: Is there a way to allow a combobox entry to be selected from a list, but to disable any option to enter text manually? I know I can set the MatchRequired property to ensure that any text must match an item in the list, but this still allows typing to be done. I suspect I could use a ListBox, but I want the drop-down list to appear. Any thoughts? -- Ian -- -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking down a combobox
Hi Dave
"Dave Peterson" wrote in message ... If "(Select)" isn't part of the list, then how do you get it to show up in the combobox choices? Me.ComboBox1.Text = "Select" This only works as long as the Locked property is set to False. Instead of using Locked, I've set MatchRequired to True and use the following lines to enable/disable the OK button. There are several ComboBoxes so I set the button to Enabled then check each ComboBox. Any one returning false will disable the button. CommandButton1.Enabled = True If Me.ComboBox1.MatchFound = False Then _ Me.CommandButton1.Enabled = False Anyway, this worked ok for me: ---code snipped--- Your code worked OK on it's own, but I couldn't work out how to make it look for a list in another worksheet. It's probably pretty simple, but then again, so am I ;-) The solution I'm using is working OK apart from one minor glitch which I'm not too worried about ("Invalid property value" appears if I attempt to close the form without selecting from each of the comboboxes). I haven't figured out a way of trapping this, but it's a minor detail as the users shouldn't be closing the forms incomplete anyway. -- Ian -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
row locking, instead of file locking? | Excel Discussion (Misc queries) | |||
locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) | |||
Combobox options based on the input of another combobox | Excel Programming | |||
ComboBox list reliant on the entry from a different ComboBox | Excel Programming | |||
How Do I Load A ComboBox RowSource From The Results Of Another ComboBox | Excel Programming |