从快捷键运行宏时，输入框不会出现 [英] Input Box doesn't appear when macro is run from shortcut key

问题描述

我有一个简单的小型Excel宏，打开一个模板，要求一个文件名，并保存该文件。它从Microsoft VBA窗口运行没有问题，但是当从Excel使用快捷键时，它将打开文件，但不显示输入框。

  Sub NewCommentSheet（）
'
'NewCommentSheet宏
'打开评论和重新检查模板。一个对话框要求数据模块名称
'，然后用于新注释表的文件名。 
'
'键盘快捷键：Ctrl + Shift + N 
'
'打开文件
 
 Workbooks.Open文件名：= _ 
 C：\Users\Kelly.Keck\Documents\Projects\SBIR\QA Notes\Comments and Recheck Template.xlsx
 
'定义变量。 moduleName来自输入框。 newFileName使用moduleName _ 
创建一个文件名：对于[moduleName] .xslx的评论
 
 Dim moduleName As String 
 Dim newFileName As String 
 
 '请求用户的数据模块名称 - 默认设置为文件名_ 
的公共部分。 
 
 moduleName = Application.InputBox（Prompt：=输入数据模块的名称，_ 
标题：=数据模块标题，默认值：=DMTitle-） 
 
'检查输入是否被取消。如果取消，结束宏以避免使用不正确的文件名保存。 
 
如果moduleName =False然后结束
 
'使用新名称保存文件。 
 
 newFileName =对& moduleName& .xslx
 ActiveWorkbook.SaveAs文件名：= newFileName 
 
 End Sub 
  

解决方案

Excel中的 Shift 键用于打开工作簿以打开文件而不运行宏，这会干扰运行其余宏。

从 MSDN 文章

当在用户界面打开工作簿时，Excel被设计为不运行Auto_Open和Workbook_Open代码，同时按住shift键。不幸的是，当通过VBA代码打开工作簿时，这种（所需的）行为也适用。

上述链接的解决方案（如果链接死亡）

此问题的解决方法（仅适用于Windows®平台）是检测是否按下Shift键并等待它在发行Workbooks.Open命令之前被释放：

 '声明API 
声明函数GetKeyState LibUser32（ByVal函数ShiftPressed（）As Boolean 
'如果按下shift键，则返回True 
 ShiftPressed = GetKeyState（SHIFT_KEY） < 0 
结束函数
 
 Sub Demo（）
尽管ShiftPressed（）
 DoEvents 
循环
 Workbooks.Open =C：\\ \\ My Documents\ShiftKeyDemo.xls
 End Sub 
  

编辑

我刚刚尝试过，下面看起来很好。在 Workbooks.Open

DoEvents > Sub NewCommentSheet（）
Dim moduleName As String
Dim newFileName As String

DoEvents

Workbooks.Open文件名：= _
C：\book1.xlsx

moduleName = Application.InputBox（Prompt：=输入数据模块的名称，_
标题：= 数据模块标题，默认值：=DMTitle-）

如果moduleName =False则结束

'使用新名称保存文件。

newFileName =对& moduleName& .xslx
ActiveWorkbook.SaveAs文件名：= newFileName
End Sub


I have a simple little Excel macro that opens a template, asks for a filename, and saves the file. It runs from the Microsoft VBA window without issues, but when the shortcut key is used from Excel, it opens the file, but doesn't show the input box.

Sub NewCommentSheet()
'
' NewCommentSheet Macro
' Opens the Comments and Recheck template. A dialog box asks for the data module name,
' which is then used for the filename of the new comment sheet.
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    'Opens the file

    Workbooks.Open Filename:= _
        "C:\Users\Kelly.Keck\Documents\Projects\SBIR\QA Notes\Comments and Recheck Template.xlsx"

    'Defines variables. moduleName comes from the input box. newFileName uses moduleName _
    to create a filename: "Comments for [moduleName].xslx"

    Dim moduleName As String
    Dim newFileName As String

    'Asks the user for the data module name--default is set as the common portion of _
    the filename.

    moduleName = Application.InputBox(Prompt:="Enter the name of the data module.", _
    Title:="Data Module Title", Default:="DMTitle-")

    'Checks to see if input was canceled. If canceled, ends the macro to avoid saving with an incorrect filename.

    If moduleName = "False" Then End

    'Saves file with the new name.

    newFileName = "Comments for " & moduleName & ".xslx"
    ActiveWorkbook.SaveAs Filename:=newFileName

End Sub

解决方案

The Shift key in Excel is used to open a workbook to open the file without running macros and this interferes with running the rest of the macro.

From MSDN article

Excel is designed not to run Auto_Open and Workbook_Open code when a workbook is opened from the User interface whilst holding down the shift key. Unfortunately, this (desired) behaviour also applies when opening workbooks through VBA code.

Resolution from the above link (In case the link dies)

The workaround for this problem (only applicable on Windows ® platforms) is to detect whether the shift key is pressed and wait for it to be released before issuing the Workbooks.Open command:

'Declare API
Declare Function GetKeyState Lib "User32" (ByVal vKey As Integer) As Integer
Const SHIFT_KEY = 16

Function ShiftPressed() As Boolean
    'Returns True if shift key is pressed
    ShiftPressed = GetKeyState(SHIFT_KEY) < 0
End Function

Sub Demo()
    Do While ShiftPressed()
        DoEvents
    Loop
    Workbooks.Open = "C:\My Documents\ShiftKeyDemo.xls"
End Sub

EDIT

I just experimented and the below seems to work. Adding DoEvents before the Workbooks.Open

Sub NewCommentSheet()
    Dim moduleName As String
    Dim newFileName As String

    DoEvents

    Workbooks.Open Filename:= _
        "C:\book1.xlsx"

    moduleName = Application.InputBox(Prompt:="Enter the name of the data module.", _
    Title:="Data Module Title", Default:="DMTitle-")

    If moduleName = "False" Then End

    'Saves file with the new name.

    newFileName = "Comments for " & moduleName & ".xslx"
    ActiveWorkbook.SaveAs Filename:=newFileName
End Sub

这篇关于从快捷键运行宏时，输入框不会出现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！