CODEWITHSUNDEEP
excelvbaobjectreference
GlassHalfAwesome
GlassHalfAwesome

Reputation: 873

Referencing the object a macro is assigned to

As the title states: in Excel 2010 how can I reference the object that a macro has been assigned to? I've created a spreadsheet with a form checkbox and assigned this macro to it:

Sub Toggle()

If ActiveSheet.Shapes("Checkbox1").OLEFormat.Object.Value = 1 Then
    ActiveSheet.Shapes("Picture1").Visible = True
Else
    ActiveSheet.Shapes("Picture1").Visible = False
End If

End Sub

The checkbox toggles whether or not a picture is visible and that's working fine but I'd like to reuse the script for multiple checkboxes.

The above code is specifically targeting Checkbox1 but I'd like it to target "this", the object I've assigned the macro to.

I feel like this should be really easy but I spent all evening on MSDN, excelforums.com and just googling around.

Thanks for your help!

Upvotes: 1

Views: 661

Answers (2)

Tim Williams
Tim Williams

Reputation: 166885

Application.Caller is what you want

Sub Toggle()

    Dim cb As String, shps As Shapes

    cb = Application.Caller
    Set shps = ActiveSheet.Shapes

    shps("Picture1").Visible = (shps(cb).OLEFormat.Object.Value = 1)

End Sub

Upvotes: 1

D_Bester
D_Bester

Reputation: 5931

As far as I know that is not possible using VBA. Sure you could put the toggle code into a separate sub and re-use it. That might help a bit but you still need to specify the name of the checkbox.

Private Sub CheckBox1_Click()
    Call Toggle("Checkbox1", "Picture1")
End Sub

Sub Toggle(ByVal Nm As String, ByVal pic As String)

    If ActiveSheet.Shapes(Nm).OLEFormat.Object.Value = 1 Then
        ActiveSheet.Shapes(pic).Visible = True
    Else
        ActiveSheet.Shapes(pic).Visible = False
    End If

End Sub

You need to put the toggle sub in the same sheet as the checkbox code or else put the toggle sub in a module.

Upvotes: 0

Related Questions