问题描述
我已经在Excel中构建了一个表单。它由3个命令按钮和一个包含复选框的框组成。复选框根据Excel表格中的表格动态填充在 userform_initialize
(用户自定义的简单方式)。框架的原因是可以有很多复选框,我希望用户能够滚动浏览。 我的目标是创建键盘快捷键为形式。我陷入困境是因为我不知道哪些复选框将会存在,所以我无法强制写入 KeyDown
处理程序。我意识到,如果我可以在表单级别处理事件处理程序,那也会更好。 Googling已经找到我的表单的 KeyPreview
属性。不幸的是,VBA IDE中的属性窗口不显示,而当我尝试通过设置 Me.KeyPreview = True
在 userform_initialize
VBA抛出一个编译错误:方法或数据成员没有找到 - 我期望给定它不在属性窗口,但值得一试。
我觉得有一些我很明显失踪,所以我想我会问,在花时间学习如何写,然后重写表单完全像MSDN示例代码一样的类:
。
我幸运吗?
我承认自己处于我的VBA知识的极限,我正在寻求扩展。任何一般的概念或上下文我应该是红色将不胜感激。
更新
我正在考虑 GetAsyncKeyState
和 Application.Onkey
。
根据我的理解, GetAsyncKeyState
只能在无限的 DoEvents
循环。我尝试启动一个希望该表单仍然加载,但当然没有 - 我被困在循环中。
Application.Onkey
是我不能将事件功能分配给userform模块中的键。这让我困惑,因为其他事件处理程序可以进入用户模块。实际上,我将它放在 Userform_Initialize
程序中。是因为它不是表单事件而是应用程序事件?
编辑
我似乎有一些有用的东西,但是对于这里描述的奇怪的问题:
:
放入一个名为KeyPreview的类: / p>
Option Explicit
Dim WithEvents u As MSForms.UserForm
Dim WithEvents t As MSForms。 TextBox
Dim WithEvents ob As MSForms.OptionButton
Dim WithEvents lb As MSForms.ListBox
Dim WithEvents dp As MSComCtl2.DTP icker
事件KeyDown(ByVal KeyCode As Integer,ByVal Shift As Integer)
'事件KeyPress(ByVal KeyAscii As Integer)
Private FireOnThisKeyCode As Integer
朋友子AddToPreview(父作为UserForm,KeyCode为整数)
Dim c As Control
设置u =父
FireOnThisKeyCode = KeyCode
对于每个c在父级。控件
选择案例类型名称(c)
案例TextBox
设置t = c
案例OptionButton
设置ob = c
案例ListBox
设置lb = c
案例DTPicker
设置dp = c
结束选择
下一步c
结束Sub
Private Sub u_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,ByVal Shift As Integer)
如果KeyCode = FireOnThisKeyCode然后RaiseEvent KeyDown(KeyCode,Shift)
End Sub
Private Sub t_KeyDown (ByVal KeyCode As MSForms.ReturnInteger,ByVal Shift As Integer)
如果KeyCode = FireOnThisKeyCode然后RaiseEvent KeyDown(KeyCode,Shift)
End Sub
Private Sub ob_KeyDown(ByVal KeyCo de As MSForms.ReturnInteger,ByVal Shift As Integer)
如果KeyCode = FireOnThisKeyCode然后RaiseEvent KeyDown(KeyCode,Shift)
End Sub
Private Sub lb_KeyDown(ByVal KeyCode As MSForms。 returnInteger,ByVal Shift As Integer)
如果KeyCode = FireOnThisKeyCode然后RaiseEvent KeyDown(KeyCode,Shift)
End Sub
Private Sub dp_KeyDown(KeyCode As Integer,ByVal Shift As Integer)
如果KeyCode = FireOnThisKeyCode然后RaiseEvent KeyDown(KeyCode,Shift)
End Sub
要放入用户窗体:
Option Explicit
Dim WithEvents kp As KeyPreview
Private Sub UserForm_Initialize()
Set kp = New KeyPreview
kp.AddToPreview Me,114
End Sub
Private Sub kp_KeyDown(ByVal KeyCode As Integer ,ByVal Shift As Integer)
MsgBoxF3被按下...
End Sub
它适用于 TextBoxes
, OptionButtons
, ListBoxes
和 DTPickers
。其他可以重点关注的控件也需要处理。
I've built a form in Excel. It consists of 3 command buttons and a frame containing checkboxes. The checkboxes are dynamically populated at userform_initialize
based on tables in an excel sheet (the idea being easy user customization). The reason for the frame is that there can be a lot of checkboxes and I want the user to be able to scroll through them.
My goal now is to create keyboard shortcuts for the form. Where I get stuck is that I can't brute force write KeyDown
handlers for each of the checkboxes because I don't know which ones will exist. I realize that it would also just be better if I could have the event handler at the form level. Googling has found me the form's KeyPreview
property. Unfortunately, the properties window in VBA IDE doesn't show it and when I try to access it programmatically by setting Me.KeyPreview = True
at userform_initialize
VBA throws a compile error: "Method or data member not found" - what I would expect given it isn't in the properties window, but was worth a try.
I feel like there's something I'm obviously missing so I thought I'd ask before spending time learning how to write and then rewriting the form entirely as a class as in the MSDN example code:https://msdn.microsoft.com/en-us/library/system.windows.forms.form.keypreview(v=vs.110).aspx.Am I that lucky?
I confess to being at the limit of my VBA knowledge and I'm looking to go expand on it. Any general concepts or context I should red would be greatly appreciated.
UPDATE
I'm now thinking about GetAsyncKeyState
and Application.Onkey
.
From what I understand, GetAsyncKeyState
only works within an infinite DoEvents
loop. I tried initiating one hoping the form would still load but of course it didn’t – I’m stuck in the loop.
The problem with Application.Onkey
is that I can't assign the event function to the key within the userform module. This puzzles me because other event handlers can go in the userform module. In fact, I’d put it in the Userform_Initialize
procedure. Is it because it's not a form event but an application event?
EDIT
I seem to have something that works, but for the strange issue described here:Event handling class will not fire unless I use a breakpoint when initializing formThank you @UGP
Here is an example how it could work, found here:
To put in a class named "KeyPreview":
Option Explicit
Dim WithEvents u As MSForms.UserForm
Dim WithEvents t As MSForms.TextBox
Dim WithEvents ob As MSForms.OptionButton
Dim WithEvents lb As MSForms.ListBox
Dim WithEvents dp As MSComCtl2.DTPicker
Event KeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer)
'Event KeyPress(ByVal KeyAscii As Integer)
Private FireOnThisKeyCode As Integer
Friend Sub AddToPreview(Parent As UserForm, KeyCode As Integer)
Dim c As Control
Set u = Parent
FireOnThisKeyCode = KeyCode
For Each c In Parent.Controls
Select Case TypeName(c)
Case "TextBox"
Set t = c
Case "OptionButton"
Set ob = c
Case "ListBox"
Set lb = c
Case "DTPicker"
Set dp = c
End Select
Next c
End Sub
Private Sub u_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = FireOnThisKeyCode Then RaiseEvent KeyDown(KeyCode, Shift)
End Sub
Private Sub t_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = FireOnThisKeyCode Then RaiseEvent KeyDown(KeyCode, Shift)
End Sub
Private Sub ob_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = FireOnThisKeyCode Then RaiseEvent KeyDown(KeyCode, Shift)
End Sub
Private Sub lb_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = FireOnThisKeyCode Then RaiseEvent KeyDown(KeyCode, Shift)
End Sub
Private Sub dp_KeyDown(KeyCode As Integer, ByVal Shift As Integer)
If KeyCode = FireOnThisKeyCode Then RaiseEvent KeyDown(KeyCode, Shift)
End Sub
To put in the userform:
Option Explicit
Dim WithEvents kp As KeyPreview
Private Sub UserForm_Initialize()
Set kp = New KeyPreview
kp.AddToPreview Me, 114
End Sub
Private Sub kp_KeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer)
MsgBox "F3 was pressed..."
End Sub
It works with TextBoxes
, OptionButtons
, ListBoxes
and DTPickers
. Other Controls that could get focus will need to be handled aswell.
这篇关于无法将Userform.KeyPreview设置为true的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!