turn a cell into a button

[Crazy_Ivan]

Hi all,

I am having problems with foms and activex forms that do not hide when rows are hidden. An easy solution would be to turn cells into buttons (rather than have buttons from the Developer menu). Is this possible?

Thanks!

 

[Jon von der Heyden]

There are various way of triggering a macro using worksheet events:

Using double-click:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$A$1" Then
        'do something
    End If
    Cancel = True
End Sub

Using right-click:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$A$1" Then
        'do something
    End If
    Cancel = True
End Sub

Using change event (This one may be helpful to emulate check box. If ticked then do something, else do something else):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        If Target.Value Then
            'do something
        Else
            'do something else
        End If
    End If
End Sub

Selection change event (i.e. when you select a cell):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        'do something
    End If
End Sub

 

[Crazy_Ivan]

Very useful - many thanks!! Am trying them all now!

 

[Crazy_Ivan]

Works an absolute treat - fantastic! Thanks again Jon!