Using checkboxes

[Noz2k]

Ok so ideally what I want to do is use checkboxes to determine what columns are hidden/unhidden on another sheet.

Currently I do this by using code like

If Sheets("Sheet1").Location = False Then
Sheets("sheet2").Range("c3").EntireColumn.Hidden = True
Sheets("sheet2").Range("c3").Offset(0, 1).EntireColumn.Hidden = True
Else
Sheets("sheet2").Range("c3").EntireColumn.Hidden = False
Sheets("sheet2").Range("c3").Offset(0, 1).EntireColumn.Hidden = False
End If

with Location being the name of a checkbox.


But what I would like to do, is instead, use code to loop through all the checkboxes on sheet1 and use either the checkbox name or caption, to find that value in row 3 of sheet2 and hide that column and the column to the right.

This way if any columns are added to sheet 2 I only have to add a checkbox with a matching name/caption

 

[Gary McMaster]

Not sure I follow but maybe try "Application.Caller" with "Form" check boxes. You could use the check box name to specify a range if you devise a logical naming scheme or you could use the "TopLeftCell" property of the check box with "Offset" to specify relative ranges.

Hope this helps.

Gary

Public Sub Test()

'Place a form check box on a worksheet and use the "Macro Assign" button
'to specify this procedure to run when check box is selected

MsgBox Application.Caller

MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address

MsgBox ActiveSheet.Shapes(Application.Caller).ControlFormat.Value

End Sub