Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    keyboard shortcut (2002)

    I need to have a macro saved in a workbook (for public use) to carry the same keyboard shortcut as a very similar macro in my personal.xls with the same keyboard shortcut. In the specific workbook, I want that macro to supersede my personal one. Is that possible?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: keyboard shortcut (2002)

    Keyboard shortcuts aren't specific to a workbook. I'd use different keyboard shortcuts.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: keyboard shortcut (2002)

    So if I link a specific macro in a specific workbook to a shortcut, others would not see that?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: keyboard shortcut (2002)

    If you want, you can use code like this in the ThisWorkbook module of the workbook:
    <code>
    Private Sub Workbook_Activate()
    Application.OnKey "{F1}", "MyMacro"
    End Sub

    Private Sub Workbook_Deactivate()
    Application.OnKey "{F1}"
    End Sub
    </code>
    Replace {F1} with the keystroke code for your keyboard shortcut - see the built-in help for OnKey - and MyMacro with the name of your macro.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: keyboard shortcut (2002)

    No, unless you use code as in the reply I added later.

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: keyboard shortcut (2002)

    Thanks Hans.
    May I ask another question.
    My workbook contains 11 macro's linked to 11 different buttons that should be run in order. Is there a way that I can stop m5 being accidentally executed before m4, for example.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: keyboard shortcut (2002)

    Why offer 11 buttons if the macros should be run in a specific order? You could provide a single button that runs macro1, then macro2, etc., up to macro11.

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: keyboard shortcut (2002)

    I should have said in my initial post as I had a feeling that you would ask.... That is not possible as there is manual intervention required between each of the stages. Only so much automation is possible <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: keyboard shortcut (2002)

    At the end of each macro (except for the last one), you could store the index number of the macro in a cell or in a custom document property.
    At the beginning of each macro (except for the first one), check the value of this cell or document property, and exit if it's too low (perhaps also if it's too high).

    For example:

    Sub macro1()
    ...
    Worksheets("Sheet1").Range("Z37") = 1
    End Sub

    Sub macro2()
    If Worksheets("Sheet1").Range("Z37") < 1 Then
    MsgBox "Please run the previous step(s) first!", vbExclamation
    Exit Sub
    End If
    ...
    Worksheets("Sheet1").Range("Z37") = 2
    End Sub

    ...

    Sub macro11()
    If Worksheets("Sheet1").Range("Z37") < 10 Then
    MsgBox "Please run the previous step(s) first!", vbExclamation
    Exit Sub
    End If
    ...
    End Sub

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: keyboard shortcut (2002)

    Clever, and works perfect, Thanks!

Posting Permissions

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