none
Cannot run the macro… the macro may not be available in this workbook RRS feed

  • Question

  • I've created a workbook/worksheet macro that creates a button in a given cell when the value in another cell changes. This macro assigns an OnAction event to the button. 

    This macro works correctly. The problem is that when I click on the created button I get the following message:

    Cannot run the macro 'My_Workbook.xlsm!test'. The macro may not be available in this workbook or all macros may be disabled'

    Since I can run the original macro, I'm guessing the last part of the error message is not applicable. I verified the macro name. I can't figure out what I need to do to get this to work.

    Let me add that I manually created a button to call the Test macro and that works.

    Here is my code:

    Sub test()
    
    'code that is used to inject into each button that is created
    
    MsgBox "The button is clicked"
    
    
    End Sub
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim KeyCells As Range
    
        ' The variable KeyCells contains the cells that will
        ' cause an alert when they are changed.
        Set KeyCells = Range("B:B")
        
        If Not Application.Intersect(KeyCells, Range(Target.Address)) _
               Is Nothing Then
               Set t = ActiveSheet.Range(Cells(ActiveCell.Row, 10), Cells(ActiveCell.Row, 10))
               If Range(Target.Address).Value = "Yes" Then
                    'MsgBox "Cell " & Target.Address & " has changed."
                    'MsgBox "This is row " & ActiveCell.Row
                    
                    Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
                    With btn
                        .OnAction = "test"
                        .Caption = "Add to Knowlege Transfer Sheet"
                        .Name = "KT" & ActiveCell.Row
                    End With
                
               ElseIf Range(Target.Address).Value = "No" Then
                    Dim thebutton As String
                    Dim sHape As sHape
                    thebutton = "KT" & ActiveCell.Row
                    On Error Resume Next
                    Set sHape = ActiveSheet.Shapes(thebutton)
                    On Error GoTo 0
         
                    If sHape Is Nothing Then
                        Exit Sub
                    Else
                        ActiveSheet.Shapes(thebutton).Delete
                    End If
      
               End If
           
        End If
    End Sub


    • Edited by Whalensdad Friday, September 29, 2017 1:47 PM
    Friday, September 29, 2017 1:23 PM

Answers

  • W,
    re:  macro for button

    Worksheet modules are class modules and by nature are private.
    You will have to either...
      Put the "test" sub in a standard module.
      or
     When assigning the OnAction property, prefix the sub name with the sheet code name (module name):  Sheet1.test

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, September 29, 2017 6:20 PM
  • Hi Whalensdad,

    Based on your current code, these code is in the WorkSheet Object, and I suggest you try suggestion from James on calling test by SheetName.MethodName.

    I suggest you make a test with below code.

    Sub test()
    
    'code that is used to inject into each button that is created
    
    MsgBox "The button is clicked"
    
    
    End Sub
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim KeyCells As Range
    
        ' The variable KeyCells contains the cells that will
        ' cause an alert when they are changed.
        Set KeyCells = Range("B:B")
        
        If Not Application.Intersect(KeyCells, Range(Target.Address)) _
               Is Nothing Then
               Set t = ActiveSheet.Range(Cells(ActiveCell.Row, 10), Cells(ActiveCell.Row, 10))
               If Range(Target.Address).Value = "Yes" Then
                    'MsgBox "Cell " & Target.Address & " has changed."
                    'MsgBox "This is row " & ActiveCell.Row
                    
                    Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
                    With btn
                        .OnAction = ActiveSheet.Name & ".test"
                        .Caption = "Add to Knowlege Transfer Sheet"
                        .Name = "KT" & ActiveCell.Row
                    End With
                
               ElseIf Range(Target.Address).Value = "No" Then
                    Dim thebutton As String
                    Dim sHape As sHape
                    thebutton = "KT" & ActiveCell.Row
                    On Error Resume Next
                    Set sHape = ActiveSheet.Shapes(thebutton)
                    On Error GoTo 0
         
                    If sHape Is Nothing Then
                        Exit Sub
                    Else
                        ActiveSheet.Shapes(thebutton).Delete
                    End If
      
               End If
           
        End If
    End Sub
    

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 2, 2017 6:20 AM

All replies

  • W,
    re:  macro for button

    Worksheet modules are class modules and by nature are private.
    You will have to either...
      Put the "test" sub in a standard module.
      or
     When assigning the OnAction property, prefix the sub name with the sheet code name (module name):  Sheet1.test

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, September 29, 2017 6:20 PM
  • Hi Whalensdad,

    Based on your current code, these code is in the WorkSheet Object, and I suggest you try suggestion from James on calling test by SheetName.MethodName.

    I suggest you make a test with below code.

    Sub test()
    
    'code that is used to inject into each button that is created
    
    MsgBox "The button is clicked"
    
    
    End Sub
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim KeyCells As Range
    
        ' The variable KeyCells contains the cells that will
        ' cause an alert when they are changed.
        Set KeyCells = Range("B:B")
        
        If Not Application.Intersect(KeyCells, Range(Target.Address)) _
               Is Nothing Then
               Set t = ActiveSheet.Range(Cells(ActiveCell.Row, 10), Cells(ActiveCell.Row, 10))
               If Range(Target.Address).Value = "Yes" Then
                    'MsgBox "Cell " & Target.Address & " has changed."
                    'MsgBox "This is row " & ActiveCell.Row
                    
                    Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
                    With btn
                        .OnAction = ActiveSheet.Name & ".test"
                        .Caption = "Add to Knowlege Transfer Sheet"
                        .Name = "KT" & ActiveCell.Row
                    End With
                
               ElseIf Range(Target.Address).Value = "No" Then
                    Dim thebutton As String
                    Dim sHape As sHape
                    thebutton = "KT" & ActiveCell.Row
                    On Error Resume Next
                    Set sHape = ActiveSheet.Shapes(thebutton)
                    On Error GoTo 0
         
                    If sHape Is Nothing Then
                        Exit Sub
                    Else
                        ActiveSheet.Shapes(thebutton).Delete
                    End If
      
               End If
           
        End If
    End Sub
    

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 2, 2017 6:20 AM