MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Changing the choice of a Combo Box via VBA

  Asked By: Magenta    Date: Feb 18    Category: MS Office    Views: 14782

I need to Change the choice of a ComboBox when a particular cell
reads "Yes" and then lock the ComboBox until the cell reads "No", when
the ComboBox will work as normal.



12 Answers Found

Answer #1    Answered By: Channarong Boonliang     Answered On: Feb 18

With forms (activate Event) you can use something like the following;
the key being the .locked property of the combobox.

If Cell = yes then
Me.ComboboxName.locked = true
Me.ComboboxName.locked = False
End if

If the form needed to change  dynamically you would have to use a
worksheet change event to trap the Cell change and then run the above

Answer #2    Answered By: Abagail Cohen     Answered On: Feb 18

...this sorts out the locking but how do I change  the combobox  value ?
eg. if cell="Yes" then combobox must equal "Export" ?

Answer #3    Answered By: Daisy Phillips     Answered On: Feb 18

Add the line:

ComboboxName.Value = "Export"


ComboboxName.Value = ComboboxName.list(1)

' the Numbe(1) being the position in the dropdown list starting with 0
for the first

Answer #4    Answered By: Keiko Mori     Answered On: Feb 18

.I've used the following code :-
"ActiveSheet.Shapes("Drop Down 56").Locked = True" this works ok
"ActiveSheet.Shapes("Drop Down 56").Value = "Export" " doesn't.
What am I doing wrong ?

Answer #5    Answered By: Code Guru     Answered On: Feb 18

I was assuming a FORM, but a shape is a bit different - I think it
requires .Name instead of .value.

Answer #6    Answered By: Qadriyyah Malik     Answered On: Feb 18

worth a try but still no go !!!!!!!!!!!!!

Answer #7    Answered By: Terry Williams     Answered On: Feb 18

"pre - coffee excuse"? . . . I'm an ordained minister and I'm not
even sure if I believe in God before coffee. And, the idea that I
might exist is way beyond hallucinations. (Checking my vital signs,
in case I'm not alive . . . stay tuned.)

Answer #8    Answered By: Casey Montgomery     Answered On: Feb 18

Is "Export" a valid choice  in the combo  box? I doubt if you can set its
value via VBA to something you can't select with the mouse.

Are you getting an error or is it simply not doing anything?

Answer #9    Answered By: Jonathan Brown     Answered On: Feb 18

"Export" is one of the choices in the combobox.
I'm trying to get VBA to choose "Export" if a particular cell
reads "Yes".

Answer #10    Answered By: Zobebah Mizrachi     Answered On: Feb 18

If you are using an OLEObject ComboBox (NOT a form) then play with the
following in the Worksheet.

With ComboBox1
Debug.Print .List(1)
Debug.Print .Name
Debug.Print .Value
.Value = .List(4)
Debug.Print .Value
End With

Answer #11    Answered By: Chuong Tran     Answered On: Feb 18

The problem might be with "Locked". It isn't what you think it is. (I've
just read the MSDN explanation, and I can't really see what it is - just
what it isn't.)

I've just put a combo  box on my active sheet and hit it with code in a
command button click event. This works fine.

Option Explicit

Private Sub CommandButton1_Click()
If ComboBox1.Enabled Then
Range("A2") = "Unlocked"
ComboBox1.Enabled = False
ComboBox1.Value = "Export"
Range("A2") = "Locked"
ComboBox1.Enabled = True
End If
End Sub

The box  is filled from a short cell  range, which includes "Export" as one of
the values (the last one, but that probably doesn't matter.) And the box is
linked to A1 where I can see that the value is being put in quite OK when I
disable the box and set its value.

Answer #12    Answered By: Indie Williams     Answered On: Feb 18

just what I needed to get me going.

Didn't find what you were looking for? Find more on Changing the choice of a Combo Box via VBA Or get search suggestion and latest updates.