Excel VBA array of checkboxes 2

[deb18]

I have 44 checkboxes on a VBA Excel form that I dragged there from the Toolbox. I named them all rep1_cb, rep2_cb, rep3_cb, ... rep44_cb.

I want to add a button to the forum to un/check all checkboxes within this "group". Is there some way to store them all within an array - or create some sort of loop without having to individually change the value of each and every one of them?

Thanks!
Deb

 

[cLFlaVA]

Add a button, CommandButton1.

Double-click the button in Design mode to get the CommandButton1_Click event.

Add this code:

Private Sub CommandButton1_Click()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 44 Step 1
Sheet1.OLEObjects("rep" & i & "_cb").Object.Value = False
Next i
Application.ScreenUpdating = True
End Sub

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[fumei]

Or, if you do not want to bother with the names, and just want to make all checkboxes blank.

Dim obj As OLEObject
For Each obj In Worksheets("Sheet1").OLEObjects
    If obj.Object.Value = -1 Then
        obj.Object.Value = 0
    End If
Next

NOTE: this will clear off all Option buttons as well.

Gerry

 

[cLFlaVA]

And will also clear any textboxes or selectboxes with a value of -1.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[deb18]

Clflava, thank you very much - that sounds like a great way of doing it... but it's not working. When I do

Sub allChecked(tf As Boolean)
    Dim counter As Integer
    Application.ScreenUpdating = False
    For counter = 1 To 44 Step 1
        Sheet1.OLEObjects("rep" & counter & "_cb").Object.Value = False
    Next counter
    Application.ScreenUpdating = True
End Sub

I get the following error: [tt]"<Method 'OLEObjects' of object '_Worksheet' failed>" [/tt]

How can I fix that, please?

And thank you fumei for your help, too.
Deb

 

[deb18]

cLFlaVA,

I got it to work with this:

Sub allChecked(tf As Boolean)
    Dim ctl As Control
    Dim ckbox As CheckBox
    For Each ctl In repsNames_frame.Controls
        If Left(ctl.Name, 3) = "rep" And Right(ctl.Name, 3) = "_cb" Then
            ctl.Value = tf
        End If
    Next
End Sub

Thanks again for your help!

Deb

 

[fumei]

And will also clear any textboxes or selectboxes with a value of -1.

cLFlaVA - could you explain how a textbox can have a value of -1? I have tried to get one to have such a value, and I can not do so. What am I missing? Thanks.

Gerry

 

[cLFlaVA]

By typing -1 into the text box.
[tt]
Sub CommandButton1_Click()
If TextBox1.Value = -1 Then
MsgBox "Value is -1"
End If
End Sub[/tt]

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[fumei]

Doh. I had not thought about a literal "-1". Silly me! Of course, yes, if you type -1 into a text box, then that is its value.

I think I just walked into a tree.

Gerry

 

[fumei]

cLFlaVA - do you know of a way to reference an ActiveX textbox control by type? I can reference it by name of course, I can reference it a part of looping through all controls - but is there a way of looping through and:

If control is a textbox THEN
do this

This is easy with formfields (Type constants: 70 = textbox, 71 = Checkbox, 83 = Dropdown), but all controls created from the Controls toolbar (be it checkbox, option box, textbox or command button) seem to have type = 5.

What am I missing?

Gerry

 

[cLFlaVA]

fumei

Excellent question. It got me thinking, and then playing around with Excel. I came up with a solution. I like it!

Private Sub CommandButton1_Click()
    Dim oleObj As OLEObject
    Dim obj As Variant
    
    For Each oleObj In Sheet1.OLEObjects
        Select Case oleObj.progID
            Case "Forms.TextBox.1"
                MsgBox "This is a textbox"
            Case "Forms.OptionButton.1"
                MsgBox "This is an option button"
            Case "Forms.CommandButton.1"
                MsgBox "This is a button"
            .
            .
            .
        End Select
    Next oleObj
End Sub

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[fumei]

Hohooo! I had found the prog.id and had it display "Forms.TextBox.1", but again...thunk, that tree again....I was thinking it is still dealing with a specific object, that is, textbox1. I wanted something that would let me know what kind of object.

So, while
{quote}Case "Forms.TextBox.1"
MsgBox "This is a textbox"
[/quote]

is not correct - This is Textbox1 - I.e it is specifically textbox1, NOT this is a textbox.....

I can take the Case, and check within the string for "Textbox", and thereby get the type.

That works for me!

Here is the code to do it in Word, which does NOT have OLEObjects (believe it or not). ActiveX objects are part of the InlineShapes collection.

Sub FindTextbox()
Dim mShape As InlineShape
Dim X As Long

For Each mShape In ActiveDocument.InlineShapes()
    X = InStr(1, mShape.OLEFormat.ProgID, "TextBox")
    Select Case X
        Case 7
            MsgBox "This is a textbox"
        Case Else
            MsgBox "Nope, not a textbox."
    End Select
Next
End Sub

This will detect if the ActiveX control is a textbox, or not.

Gerry

 

[cLFlaVA]

I wouldn't use a select case in this manner, but rather...

Sub FindTextbox()
  Dim mShape As InlineShape

  For Each mShape In ActiveDocument.InlineShapes()
    If InStr(1, mShape.OLEFormat.ProgID, "TextBox") > 0 Then
      MsgBox "This is a textbox"
    End If
  Next
End Sub

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[fumei]

Even better. I have a very sore head from not seeing those individual trees. This has helps me quite a bit.

Thanks. You deserve a star.

Gerry

 

[cLFlaVA]

Haha thanks. Not sure which country you're in, but if you're in the US - enjoy your holiday weekend. If not, enjoy your regular, two-day weekend.



*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[deb18]

Thanks from me, too and here's another star.
-Deb

 

[fumei]

I am not in the U.S., but we do have a long holiday weekend (Labour Day), and I will be up in the mountains playing my 33 string wire harp, and working with new gear on my telescope. Singing at the top of my lungs, so the bears know I am there. Chasing the marmots away from my truck. They like to chew on wiring.

Have a good one....I'm outta here!

Gerry