Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled

    I have a workbook where I run a macro from a shape.
    This is done by manually assigning the macro in the "assign macro" dialogue box.

    The macro needs parameters, so the call is: NameOfWorkbook.xlsm!'NameOfMacro "parameter"'

    When I open the workbook and tries to run the macro from the shape, I get the error message as in the title.
    However If I press the shape again, it works just fine. No matter how many times I run the macro from this shape or other similar shapes afterwards there are no problems. It is only the first time i click it that there are problems.

    I have tried to make the function public, but with no change in behaviour.
    I have tried to change the security settings, but with no change in behaviour.

    It should also be mentioned that I've read a lot of previous posts on this site and other with the same error, but none of the suggested solutions worked for me.


    Any help or comments would be greatly appreciated

    Thanks in advance

  2. #2
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled

    Is the procedure that your shape is linked to in a different workbook? The fact that it doesn't work the first time might indicate that the workbook wasn't open at the first click, but if the workbook containing the procedure is then opened, that would mean that any subsequent clicks would work fine. Just a thought.

  3. #3
    New Member
    Join Date
    Apr 2012
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled

    Quote Originally Posted by PeteRooneyHome View Post
    Is the procedure that your shape is linked to in a different workbook? The fact that it doesn't work the first time might indicate that the workbook wasn't open at the first click, but if the workbook containing the procedure is then opened, that would mean that any subsequent clicks would work fine. Just a thought.
    Thanks for the reply

    No the sub is in the same workbook stored in a module.
    I've tried to place the sub on the specific sheet also.
    The sub is also denoted public.

  4. #4
    New Member
    Join Date
    Apr 2012
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled

    Now I've also tried to include the name of the module as suggested in other threads, but still no effect.

    Anyone has any suggestions? Or experienced a similar problem?

    My main issue is that I cant see what changes between the opening of the sheet and the second time i try to run to macro.
    If it works the second time, the path, security etc. must all be ok?

  5. #5
    New Member
    Join Date
    Apr 2012
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled

    Just a thought on the comment by Pete.

    Is it possible to run the macro, without specifying the name of the workbook?

    The current call is: NameOfWorkbook.xlsm!'NameOfModule.NameOfSub "parameter"'

    I've tried to remove the name of the workbook, but excel seems to insert it by itself.

    Is there any way to run the macro, just by calling the module and macro name?

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,304
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled

    Can you post the actual code you are using to 'call' the macro?
    If posting code please use code tags.

  7. #7
    New Member
    Join Date
    Apr 2012
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled

    Pipeline_1.49.xlsm!'Pipeline.PrintPipeline "Denmark"'

    The macro is "called" from a shape. Normally you would select the macro from the list available, but since the macro required parameters its not in the list. Hence I have to type it manually.

    The strange part is that it works the second time i click the shape?!

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,304
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled

    Why not use Application.Run in the Click event of the shape?

    You can do that by selecting the shape, right clicking, selecting Assign Macro... and then click New instead of selecting a macro.

    This is the kind of thing you should then see.
    Code:
    Sub RoundedRectangle1_Click()
    
    End Sub
    Try putting this in that sub.
    Code:
    Application.Run "Pipeline_1.49.xlsm!Pipeline.PrintPipeline", "Denmark"
    If posting code please use code tags.

  9. #9
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled

    Quote Originally Posted by tuetue View Post
    Pipeline_1.49.xlsm!'Pipeline.PrintPipeline "Denmark"'

    The macro is "called" from a shape. Normally you would select the macro from the list available, but since the macro required parameters its not in the list. Hence I have to type it manually.

    The strange part is that it works the second time i click the shape?!
    Can you email the workbook?

    Also, not sure about the Syntax of a macroname with spaces and double quotes...

  10. #10
    New Member
    Join Date
    Apr 2012
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled

    Thank you so much Norie. It worked perfectly
    I have still no idea as to why is should work better, but it did!

    I used this code where the sheet name is removed so I can change the name without changing the code:

    Code:
    Public Sub Pipeline_Click()
    Application.Run "Pipeline.PrintPipeline", "Denmark"
    End Sub
    Where "Pipeline" is the name of the shape.

    Thanks again for all suggestions and the help

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •