我有一个简单的小 Excel 宏,用于打开模板、询问文件名并保存文件。它从 Microsoft VBA 窗口运行没有问题,但当从 Excel 使用快捷键时,它会打开文件,但不显示输入框。
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 http://support.microsoft.com/kb/555263 article
Excel 设计为在按住 Shift 键的同时从用户界面打开工作簿时不运行 Auto_Open 和 Workbook_Open 代码。不幸的是,当通过 VBA 代码打开工作簿时,这种(所需的)行为也适用。
解析来自上面的链接(万一链接失效了)
此问题的解决方法(仅适用于 Windows® 平台)是检测是否按下了 Shift 键并等待其释放,然后再发出 Workbooks.Open 命令:
'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
我刚刚尝试过,下面的内容似乎有效。添加DoEvents
之前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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)