MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Mary on May 30, 2001 6:10 AM

I am trying to produce a userform to act as the user interface for a parts ordering system,

I have been trying to use checkboxes to act as a yes/ no option for the various options,
Once the user has made all the appropriate selections I need all the selected options to be processed.
I have read the Q/A session entitled “Control Freak” and some of the requirements seem to be the same as mine, with that in mind I have assigned a variable to each checkbox.
Once the “Enter” control button is pressed all the variables from the selected checkboxes are processed.

Unfortunately, if the user clicks a checkbox, then changes his mind, and clicks it again to deselect the tick mark; the variable is still processed.

How can I get the “Enter” control button to process only the checkboxes that are still selected and not those that have been selected at some stage.

There are about 20 checkboxes required on my form, I really don’t want to have to use a list box or anything else

The final code for the enter button is as follows:

Private Sub Enter_Button_Click()

On Error Resume Next

Run aChoice
Run bChoice
Run cChoice
Run dChoice
Run eChoice

End Sub

Code for one of the checkboxes:

Private Sub CheckBox1_Click()

aChoice = "Mary.xls!Order_Pebbles"

End Sub

Prior to any code in userform

Dim aChoice As String
Dim bChoice As String
Dim cChoice As String
Dim dChoice As String
Dim eChoice As String

This seems to work fine (albeit a little slow) except for not allowing the user to change their mind

Thanks for your help in advance


Posted by Dave Hawley on May 30, 2001 7:54 AM

aChoice = "Mary.xls!Order_Pebbles"

Hi Mary

Sounds like you just need an If Statement in the checkboxes:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
aChoice = "Mary.xls!Order_Pebbles"
aChoice = ""
End If
End Sub

Grouped OptionButtons though would probably be better suited as they can only have one selected.

OzGrid Business Applications