CODEWITHSUNDEEP
vbaexcel
Derek
Derek

Reputation: 15

My shortcut key will not allow me to run a message box

Previous versions of this code had no message box, which sometimes resulted in the wrong workbook being closed. I added an okcancel message box to keep this from happening, but the message box doesn't show up when I use a shortcut key to open it. What am I missing?

Sub openerQuick()

    Dim myfile As String
    Dim clientID As String
    Dim PDSfilename As String
    Dim myopener As Variant

    clientID = ActiveCell
    PDSfilename = ActiveCell.Offset(0, 1)
    myfile = "N:\DOWNLOAD\FILEDIR\" & clientID & "\original\" & PDSfilename
    Set wbOpener = Workbooks.Open(myfile)

    If MsgBox("Okay to close?", vbOKCancel) = vbOK Then
        ActiveWorkbook.Close
    End If

End Sub

Upvotes: 1

Views: 78

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

I doubt the MsgBox itself has anything to do with losing the macro shortcut key.

Shortcut keys are defined by a hidden member attribute value, and the VBE has a tendency to lose member attributes when you rewrite a method's signature, or rewrite a module*; it's possible that modifying the code caused the previously existing attribute to somehow get lost.

  • Remove the module from the project, pick "Yes" when prompted whether to export or not
  • Open the exported file in Notepad++ your favorite text editor
  • Locate the procedure
  • Add the attribute if it's not there
  • Save the file if it was changed, re-import into the project

The member attribute should look something like this:

Public Sub OpenerQuick()
    Attribute OpenerQuick.VB_ProcData.VB_Invoke_Func = "A\n14"
    '...code....
End Sub

That exact attribute associates Ctrl+Shift+A to the macro; change the A for whichever letter rocks your boat to change the shortcut.

When you record a macro in Excel and specify A for a shortcut key, the macro recorder automatically adds this hidden attribute for you.

* Rubberduck's module rewriters have that very problem and it's driving me nuts.

Upvotes: 1

Vityata
Vityata

Reputation: 43585

In a module write the following 2 subs:

Public Sub OpenerQuick()
    If MsgBox("Okay to close?", vbOKCancel) = vbOK Then ActiveWorkbook.Close
End Sub

Public Sub InitiateMe()
    Application.OnKey "{a}", "OpenerQuick"
End Sub

Run only InitiateMe. Now, when you press a, InitiateMe would be triggered.

Upvotes: 0

Related Questions