CODEWITHSUNDEEP
vbacheckbox
stark
stark

Reputation: 13

Select linked cell, cell next to checkbox, or get the checkbox name

I have a document full of Checkboxes and I dont want to write specific VBA code for each checkbox because the file size needs to stay relatively small. What I am trying to do with my code is when the checkbox is checked, it automatically selects the cells next to it(not hard coded in using "Range") and then perform the rest of the programed VBA function.

How do I either get the name of the checkbox, select the linked cell, or select the cell next to the checkbox using some kind of "offset" property? I am completely stumped!

Thanks for your help in advance.

Upvotes: 0

Views: 4044

Answers (1)

cyboashu
cyboashu

Reputation: 10443

Use Form Controls instead of ActiveX Controls for Check Box.

Select ActiveX

Following code will not be work with check box from ActiveX Controls. Also, you need to assign macro to the checkbox, simply trying to run this code from VBEditor will give error.

Add Macro

Assuming all the checkboxes are on same sheet, select all your checkboxes and assign them same macro, something like this

       Sub checkBoxHandler()

            Dim shp As Shape

            Set shp = ActiveSheet.Shapes(Application.Caller)
            MsgBox shp.Name 'Name
            MsgBox shp.TopLeftCell.Offset(1).Address ' 1 Rows below checkbox
            ActiveSheet.Range(shp.ControlFormat.LinkedCell).Select ' Select linked cell.

            Set shp = Nothing
        End Sub

here Application.Caller helps VBA to identify which checkbox is being clicked.

Upvotes: 2

Related Questions