Initializing Form Controls

[PosseJohn]

I have a form that has many checkboxes. I would like to initialize all of
them to false.

What code would cycle thru the controls and determine if it is a checkbox?

For Each chk in Me.Controls
If ????
Me.Controls(chk) = False
End If
Next

 

[Dave Peterson]

Dim ctrl as control
for each ctrl in me.controls
if typeof ctrl is msforms.checkbox then
ctrl.value = false
end if
next ctrl

 

[PosseJohn]

How do I distinguish between a ComboBox, ListBox, TextBox, or CheckBox?

 

[Dave Peterson]

if typeof ctrl is msforms.checkbox then
if typeof ctrl is msforms.listbox then
if typeof ctrl is msforms.textbox then

if typeof ctrl is msforms.combobox then
(but there's only one combobox--the Forms version is called a dropdown.)

Is that what you meant?

 

[Rick Rothstein \(MVP - VB\)]

You would use an If-ElseIf-ElseIf-(etc)-End If block. When you type this...

If TypePf ctrl Is msforms.

or this...

ElseIf TypeOf ctrl Is msforms.

as soon as you type the dot after MsForms, you will be offered a list to
select from.

Rick

 

[PosseJohn]

Thank you both, Rick and Dave... I wasn't aware of the TYPEOF syntax.

 

[Ryan Ragno]

When i use your code i get an "invalid use of Me keyword". I don't actually
understand the Me keyword and thus often avoid using it. I have tried alot of
other classes but i can't seem to find the right word to put inplace of 'Me'?

Thank you

 

[Dave Peterson]

This code assumes that the checkboxes were on a userform and the code was behind
that userform.

If your checkboxes are on a worksheet, then this code won't work.

But if your checkboxes are on a worksheet, what type of checkboxes did you
use--the checkboxes from the Forms toolbar or the checkboxes from the control
toolbox toolbar?

'If they're from the forms toolbar:
ActiveSheet.CheckBoxes.Value = False

'If they're from the control toolbox toolbar:
Dim myCBX As OLEObject
For Each myCBX In ActiveSheet.OLEObjects
If TypeOf myCBX.Object Is MSForms.CheckBox Then
myCBX.Object.Value = False
End If
Next myCBX