Setting attributes for controls

[Don Madsen]

If a sheet has CheckBox1 .....CheckBox20 and you want to program to hide
certain ones based on some criteria. For example, making CheckBox5 to
CheckBox9 Visible.False?

 

[Harald Staff]

Hi

There are checkboxes and checkboxes. The ones from the Forms toolbar are part of its
Checkboxes collection, so you can address #5 as Checkboxes(5). The ActiveX boxes from the
Controls toolbar are not, you have to deal with them one by one.

 

[Tom Ogilvy]

Just to add, for ActiveX boxes on a worksheet with name like textbox1 to
txtbox9

Dim obj as OleObject
Dim tbox as MSForms.TextBox
for each obj in Activesheet.OleObjects
if typeof obj.Object is MSForms.Textbox then
set tBox = obj.Object
idx = clng(right(tbox.name,1))
if idx >= 5 and idx <=9 then
tbox.Value = ""
end if
End if
Next

On a userform

Dim tbox as MSForms.TextBox
dim ctrl as Control
for each ctrl in Userform1.Controls
if typeof ctrl is MSforms.TextBox then
set tbox = ctrl

End if
Next

or

for i = 5 to 9
Userform1.Controls("Textbox" & i).Value = ""
Next i