Incrementing checkbox numbers with VBA.

[medic5678]

I have 10 checkboxes that I need to set value to FALSE

They are named NACB101 thru NACB110.

for x = 1 to 10
NACB101.value = FALSE (not sure how to concatenate the name of the textbox here so I can increment with a counter).

next x

Thanks for your help here.

 

[Rick Rothstein]

Where are you CheckBoxes located at... on a UserForm or directly on the worksheet? If on the worksheet, what kind of CheckBoxes are they... Form or ActiveX?

 

[6StringJazzer]

Try

Dim x As Integer

For x = 101 To 110

   Controls("NACB" & x).Value = False

Next x

 

[medic5678]

Sorry, they're on a userform.

 

[My Aswer Is This]

This script will set all the checkboxes on your UserForm to False no matter how they are named.

Private Sub CommandButton3_Click()
'Modified  11/16/2018  9:23:21 PM  EST
Dim c As Control
For Each c In Me.Controls
    If TypeName(c) = "CheckBox" Then c.Value = False
Next
End Sub

 

[medic5678]

Thanks for the awesome solutions guys! You are all masters to a clueless newbie! Understanding how to iterate through these controls simplifies things dramatically.

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thanks for the awesome solutions guys! You are all masters to a clueless newbie! Understanding how to iterate through these controls simplifies things dramatically.

 

[medic5678]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Actually your solution has really got my brain zipping around in my head. Is there a way you could use this, for example, to clear all the checkboxes, but only within a particular frame (frame1, for example), while it left the checkboxes in other frames (2 thru x) alone? This way, I wouldn't have to write code for each frame, because there are a different number of checkboxes in each frame.

 

[Rick Rothstein]

Actually your solution has really got my brain zipping around in my head. Is there a way you could use this, for example, to clear all the checkboxes, but only within a particular frame (frame1, for example), while it left the checkboxes in other frames (2 thru x) alone? This way, I wouldn't have to write code for each frame, because there are a different number of checkboxes in each frame.

If you leave your Frames with their default names (Frame1, Frame2, etc.), then here is a subroutine that you can pass the frame number into and it will clear all the checkboxes within that Frame. The subroutine name is ClearFrameCheckBoxes and you would call it from you event code (say, the event code for a CommandButton) like this to, for this example, clear Frame2...

ClearFrameCheckBoxes 2

Here is the subroutine code (you can place it in the UserForm's code module...

[table="width: 500"]
[tr]
	[td]Sub ClearFrameCheckBoxes(FrameNum As Long)
  Dim Cntrl As Control
  For Each Cntrl In Controls("Frame" & FrameNum).Controls
    If TypeOf Cntrl Is MSForms.CheckBox Then Cntrl.Value = False
  Next
End Sub[/td]
[/tr]
[/table]

 

[My Aswer Is This]

Yes.
Try this:

Private Sub CommandButton4_Click()
'Modified  11/18/2018  2:33:03 PM  EST
Dim c As Control
For Each c In [COLOR=#ff0000]Frame1[/COLOR].Controls
    If TypeName(c) = "CheckBox" Then c.Value = False
Next
End Sub

Change the name of the frame to meet your needs.