Limiting Choices in a Dropdown Box with VBA

[TechnaDiva]

I am new to VBA and I am trying to control which items show up in my dropdown box (MyDropdown), based on whether a check box (MyCheckbox) is checked or not. The dropdown box is populated from a table (MyChoices). If the check box is checked, I would like it to list choises 1,2,4, and 8. If it is unchecked, I want it to list 3,5,6, and 7. It is important that the unchecked value is the starting value )as the box is unchecked) and that toggling it on and off resets the value accordingly. I have no idea where to begin, but would appreciate any help.

 

[DeeBeeGee]

TD

Probably the simplest way to do this is to clear down MyDropdown then individually add each item you require depending on the value of MyCheckbox each time you change the value of MyCheckbox. You can call the sub when the form is originally invoked

Sub MyCheckbox_Change

MyDropdown.Clear

If MyCheckBox = True then
MyDropDown.Add MyChoices(1)
MyDropDown.Add MyChoices(2)
MyDropDown.Add MyChoices(4)
MyDropDown.Add MyChoices(8)
Else
MyDropDown.Add MyChoices(3)
MyDropDown.Add MyChoices(5)
MyDropDown.Add MyChoices(6)
MyDropDown.Add MyChoices(7)
End If

End Sub

 

[TechnaDiva]

DeeBeeGee,

You are wonderful to help me out, it is exactly what I wanted. I am unable to get it to work though. Can you help?

I added the following code to the form open:


Private Sub Form_Open(Cancel As Integer)

Call MyCheckbox_Change

End Sub


Then I added your code:


Sub MyCheckbox_Change

MyDropdown.Clear

If MyCheckBox = True then
MyDropDown.Add MyChoices(1)
MyDropDown.Add MyChoices(2)
MyDropDown.Add MyChoices(4)
MyDropDown.Add MyChoices(8)
Else
MyDropDown.Add MyChoices(3)
MyDropDown.Add MyChoices(5)
MyDropDown.Add MyChoices(6)
MyDropDown.Add MyChoices(7)
End If

End Sub


I get an error message when I open theform. "Method or data member not found" - it stops at the MyDropdown.Clear line of the code.

 

[DeeBeeGee]

In what application are you working? ".Clear" should work for a ComboBox on an Excel MSUserform but I know Word has slightly different methods and syntax.
If you delete ".Clear" and put back the "." then you should be prompted with a list of methods that are applicable to MyDropdown. If not then it may be possible that it needs to be better referenced
eg. Userform2.MyDropdown

 

[PHV]

If Access, try .RowSource="" and .AddItem

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TechnaDiva]

I'm sorry, I don't know how I could have forgotten to mention it is an Access form.


I made the following changes to the form open event:

Private Sub Form_Open(Cancel As Integer)

Call MyCheckbox_Change

End Sub


And the following changes to the dropodown event:


Sub MyCheckbox_Change()

End Sub

MyDropdown.RowSource = ""

If MyCheckbox = True Then
MyDropdown.AddItem MyChoices(1)
MyDropdown.AddItem MyChoices(2)
MyDropdown.AddItem MyChoices(4)
MyDropdown.AddItem MyChoices(8)
Else
MyDropdown.AddItem MyChoices(3)
MyDropdown.AddItem MyChoices(5)
MyDropdown.AddItem MyChoices(6)
MyDropdown.AddItem MyChoices(7)
End If

End Sub


Now as soon as I open the form, I get an error:

"Compile Error
Sub or function not defined."

The error is thrown on the Sub MyCheckbox_Change() line of code. I thought this would be easier. What am I doing wrong?

 

[PHV]

Compile your project:
while in VBE, menu Debug -> Compile xxx
Provided no error during copy'n'paste and post, I suspect a spurious End Sub line.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244