CODEWITHSUNDEEP
excelvba
Fabio Ochoa Restrepo
Fabio Ochoa Restrepo

Reputation: 81

VBA Macro - check particular boxes

good morning!

I'm developing a small project and I came across with this Macro world aka "Macro VBA Scripting" that I never heard of before.

Is something pretty basic I guess, but I still struggle with it.

What I did so far? A lot of google searches.

and I did my own method:

Sub Clickall()
With Selection
ActiveSheet.Shapes.Range("Check Box 2").Select
.Value = xlOn
.LinkedCell = ""
.Display3DShading = False
End With
End Sub

But this one only checks 1 box at a time. And it fails a lot of times, without any reason.

Then I found this:

Public Function Check(filePath As String, sheetName As String)
Dim xlWorkbook As Workbook
Dim xlWorksheet As Worksheet
Dim xlRange As Range
Set xlWorkbook = Workbooks.Open(filePath, UpdateLinks:=False)
Set xlWorksheet = xlWorkbook.Worksheets(sheetName)
For Each chkBox In xlWorksheet.CheckBoxes
chkBox.Value = xlOn
Next chkBox
xlWorkbook.Save
xlWorkbook.Close
End Function

But in this case, is checking all the boxes at the same time and I want to check only particular boxes.

List of check boxes

So, when I receive that excel file. Checkboxes 1,2,3,4 will already be checked. My job is to check, checkboxes number 5,6,7,8,9, and leave uncheck the checkboxes number 10 and 11.

Did someone have any idea how to achieve this?

Thank you

Upvotes: 1

Views: 520

Answers (3)

Алексей Р
Алексей Р

Reputation: 7627

you can operate with checkboxes using an array of indexes or names .CheckBoxes(Array(...))

Sub SetCheckBoxes()
    ' set all the CheckBoxes to False
    ActiveSheet.CheckBoxes.Value = False
    
    ' option one: indexes of the checkboxes = checkboxes numbers
    ActiveSheet.CheckBoxes(Array(5, 6, 7, 8, 9)).Value = True
    
    ' option two: indexes of the checkboxes <> checkboxes numbers
    arr = Array(5, 6, 7, 8, 9)
    For i = 0 To UBound(arr)    'make array with the checkboxes names
        arr(i) = "Check Box " & arr(i)
    Next
    ActiveSheet.CheckBoxes(arr).Value = True    'apply
End Sub

Upvotes: 1

Capt.Krusty
Capt.Krusty

Reputation: 627

Public Function Check(filePath As String, sheetName As String)
Dim xlWorkbook As Workbook
Dim xlWorksheet As Worksheet
Dim xlRange As Range
Dim i As Integer
Set xlWorkbook = Workbooks.Open(filePath, UpdateLinks:=False)
Set xlWorksheet = xlWorkbook.Worksheets(sheetName)

For i = 5 To 9
   xlWorksheet.OLEObjects("Checkbox" & i).Object.Value = True 'Give name as String as your checkboxes are named
Next i

xlWorkbook.Save
xlWorkbook.Close

End Function

Upvotes: 1

FaneDuru
FaneDuru

Reputation: 42256

Plese, test the next code. Not returning anything, it is enough to be a Sub:

Sub CheckCB(filePath As String, sheetName As String)
 Dim xlWorkbook As Workbook, xlWorksheet As Worksheet, xlRange As Range, i As Long
 Set xlWorkbook = Workbooks.Open(filePath, UpdateLinks:=False)
 Set xlWorksheet = xlWorkbook.Worksheets(sheetName)
 For i = 1 To xlWorksheet.CheckBoxes.count
    Select Case i
        Case 5, 6, 7, 8, 9
              xlWorksheet.CheckBoxes("Check Box " & i).value = True
    End Select
 Next i
 xlWorkbook.Close , True
End Sub

Upvotes: 2

Related Questions