How to create a kind of variable Checkbox in vba excel

Question

I have the next excel sheet with many checkboxs.

enter image description here

The problem is when I am coding I have to do some functions with Valor1 and Valor2 depending of if the checkbox is activate.

Well I have the code.

Option Explicit

Sub Casilladeverificación1_Haga_clic_en()

    Range("c12").Activate

    Do


        If CheckBox1.Value Then
            Call fucntion1
        'Works for the first row, but for the second row int shoul be check CheckBox12 ,a next CheckBox23 ...

        If CheckBox2.Value Then
            Call fucntion1

        If CheckBox2.Value Then
            Call fucntion3

        ....

    ActiveCell.Offset(1, 0).Activate

    While Not IsEmpty(ActiveCell.Value2)

End Sub

But you can notice I dont want to made all the case with all the checkbox, there is a solve for this like checkbox[i]

Bmo · Accepted Answer · 2013-02-07 12:29:29Z

1

I would put all of your functions into one big function and the functionality would separated by a Select Case block.

 Private Sub functionRouter(checkAction as integer)
 Select Case checkAction
      Case 1
         'Code for function one
      Case 2
         'Code for function two
      ''Etc.
 End Select
 End Sub

You're going to want to loop over all your check boxes. This is going to depend on what checkbox you are using.

 Sub test()

 Dim chkBox As CheckBox
 Dim chkBox2 As OLEObject

 'Regular
 For Each chkBox In Sheets("Sheet1").CheckBoxes
  Debug.Print chkBox.Caption
 Next chkBox

 'ActiveX
 For Each chkBox2 In Sheets("Sheet1").OLEObjects
  If TypeName(chkBox2.Object) = "CheckBox" Then
     Debug.Print chkBox2.Object.Value
  End If
 Next chkBox2

You could do a few different things with all of your checkboxes. You could use the tagproperty (you would need to set all of them, but this allows for duplicates). Then call functionRouter(chkBox.tag) Or you could parse something from the name functionRouter Right(chkBox.name, 1)

edited Feb 7, 2013 at 12:29

answered Feb 7, 2013 at 12:01

Bmo

1,22211 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonathan Raul Tapia Lopez Over a year ago

Thats awsome, but Im testing now, I have some question about the differences between CheckBox and OLEObject, but I am using OLEObject, please try to follow that post, for you I am going to have more questions.

Bmo Over a year ago

Ok so you're going to be using chkBox2.object.Value and .Name or .Tag I tried to look in the object browser for a way to reference it directly but I haven't found it yet. Make sure you always use .Object before you try to get to one of its properties. Honestly I don't know the major differences between the two. One is native to Excel. Probably for legacy support the ActiveX is the newer version and is likely more powerful (but harder to use)

Jonathan Raul Tapia Lopez Over a year ago

Hey I tryed with chkBox2.Object.Tag but I have the object does not support this property or method but I am doing to parse the numbers of the .name and if divide by 11 and working with this value.

Andrey Gordeev · Accepted Answer · 2013-02-07 11:42:02Z

1

You can iterate checkboxes on worksheet by using this loop:

For Each chk In ActiveSheet.CheckBoxes
    MsgBox chk.Name
Next

It won't work if you use ActiveX controls though.

answered Feb 7, 2013 at 11:42

Andrey Gordeev

32.7k16 gold badges143 silver badges169 bronze badges

Collectives™ on Stack Overflow

How to create a kind of variable Checkbox in vba excel

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related