CODEWITHSUNDEEP
vbacheckbox
Jonathan Raul Tapia Lopez
Jonathan Raul Tapia Lopez

Reputation: 1403

How to create a kind of variable Checkbox in vba excel

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]

Upvotes: 1

Views: 10027

Answers (2)

Bmo
Bmo

Reputation: 1212

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)

Upvotes: 1

Andrey Gordeev
Andrey Gordeev

Reputation: 32449

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.

Upvotes: 1

Related Questions