CODEWITHSUNDEEP
excelvba
Gideon
Gideon

Reputation: 1886

Using VBA to systematically define the linked cell for a group of check boxes (excel)

In a spreadsheet I have a large number of pre-existing check-boxes, setting the linked cell of each manually would be a tedious task.

I hope to group large numbers of them, and then write VBA code that achieves:

i = 1
n = number of checkboxes in group
While i < n
Loop
For checkbox i in 'group X', assign linked cell to cell (range A1-->Z1)
End loop

Obviously this isn't VBA, but I'm not familiar with the syntax, does anyone know a) if it possible to execute such a function (i.e. by grouped elements + assigning linked cells) b) the commands / syntax I need to lookup to write it.

Many thanks

Upvotes: 0

Views: 9968

Answers (2)

Santosh
Santosh

Reputation: 12353

Is it what you are looking for ?

Below code checks for each checkbox on sheet1 and sets the linked cell property starting from cell A1 & so on.

Lets keep it simple.

Sub sample()
    Dim i As Integer
    Dim chk As Variant

    i = 1

    With Sheets("Sheet1")

        For Each chk In .OLEObjects
            If TypeName(chk.Object) = "CheckBox" Then
                chk.LinkedCell = .Range("A" & i).Address
                 i = i + 1
            End If
        Next

    End With
End Sub

Upvotes: 4

Floris
Floris

Reputation: 46435

This code will do what you want:

Sub linkFromGroup()
Dim g              ' we put groups in this variable
Dim gc As Integer  ' group count - number of elements in group
Dim r As Range     ' points to cell we will link to            

Set r = Range("A1") ' initially point to cell A1 - this could be anything

' we will know something is a group when we can count the objects in the group
' if we try to count objects that don't exist we will get an error.
' we will trap that with the following line:
On Error Resume Next 

' turn off screen updating while macro runs - or it will flicker
Application.ScreenUpdating = False

' loop over all the "shapes" in sheet1. A group is a special kind of shape
For Each g In Sheets("Sheet1").Shapes
  ' set count to zero
  gc = 0
  ' see if we get a value other than zero
  gc = g.GroupItems.Count ' on error we go to the next line and gc will still be zero
  If gc > 0 Then
    For ii = 1 To gc
      g.GroupItems.Item(ii).Select
      Selection.LinkedCell = r.Address  ' right now I am assuming only check boxes in groups...
      Selection.Caption = "linked to " & r.Address ' not necessary - but shows which box was linked to what. 
      Set r = r.Offset(1, 0) ' next check box will be linked with the next cell down from r
    Next ii
  End If
Next g

Application.ScreenUpdating = True ' turn on normal operation again  

End Sub

Example of what my test sheet looks like after running this (there were two groups and a single check box):

enter image description here

Single check box wasn't touched - the groups were. I never clicked box $A$8 so its value doesn't show up as either TRUE or FALSE.

You need to open the VBA editor (Alt-F11), insert a module, and paste in the above code. You can then run it using (Alt-F8) and picking the macro from the list that is shown. There are many other ways you can do this. It sounds from your question that you can adapt the code from here. Make sure you do this on a copy of your spreadsheet first - until you are sure this is working as you want it to!

Upvotes: 6

Related Questions