EXCEL VBA：dblClick，Repetitive Code Improvement

问题描述

我有一堆类似构造的用户形式，其上有许多相同的对象。对于我的标签（超过50个），我创建了一个dblClick事件，以允许用户更改标题。这很正常 - 但是我想知道是否有办法改善我的代码。

有没有办法能够避免制作数十份相同代码的副本只是为了处理不同对象上的相同事件？

这是我的代码：

Item1_Label.Caption = InputBox（blah？，blah，Item1_Label.Caption）
如果Item1_Label.Caption =Then Item1_Label.Caption =Item 1
End Sub

Private Sub Item2_Label_dblClick（ByVal Cancel as MSForms.ReturnBoolean）
Item2_Label.Caption = InputBox （blah？，blah，Item2_Label.Caption）
如果Item2_Label.Caption =Then Item2_Label.Caption =Item 2
End Sub

Private Sub Item3_Label_dblClick（ByVal取消为MSForms.ReturnBoolean）
Item3_Label.Caption = InputBox（blah？，blah，Item3_Label.Caption）
如果Item3_Label.Caption =T母鸡Item3_Label.Caption =项目3
End Sub

'etcetera etcetera

我的userform中有超过50个克隆行代码。我认为有一个更好的方式来做，但是当我，我看不到如何将其应用于我的目的。

有没有办法来防止这个重复的代码？

谢谢，

Elias

解决方案

 'clsLabel 
 Public WithEvents oLabel As MSForms.Label 
 
 Public Sub oLabel_dblClick（ByVal取消为MSForms.ReturnBoolean）
 MsgBox oLabel.Caption 
 End Sub 
 
 
 
'表单代码
私有colLabels作为集合
 
 Private Sub UserForm_Initialize（）
 Dim o As Control，l As clsLabel 
 Set colLabels = New Collection 
对于每个o在Me.Controls 
如果TypeName（o）=Label然后
设置l =新的clsLabel 
设置l.oLabel = o 
 colLabels.Add l 
结束如果
下一个o 
 End Sub 
  

I have a bunch of similarly constructed userforms with many identical objects on them. For my labels (which number over 50), I have created a dblClick event to allow the users to change the caption. This is working fine-&-all, but I'm wondering if there's a way for me to improve my code.

Is there a way to be able to avoid making dozens of copies of the same code just to handle the same event on different objects?

Here is my code:

Private Sub Item1_Label_dblClick(ByVal Cancel as MSForms.ReturnBoolean)
    Item1_Label.Caption = InputBox("blah?", "blah", Item1_Label.Caption)
        if Item1_Label.Caption = "" Then Item1_Label.Caption = "Item 1"
End Sub

Private Sub Item2_Label_dblClick(ByVal Cancel as MSForms.ReturnBoolean)
    Item2_Label.Caption = InputBox("blah?", "blah", Item2_Label.Caption)
        if Item2_Label.Caption = "" Then Item2_Label.Caption = "Item 2"
End Sub

Private Sub Item3_Label_dblClick(ByVal Cancel as MSForms.ReturnBoolean)
    Item3_Label.Caption = InputBox("blah?", "blah", Item3_Label.Caption)
        if Item3_Label.Caption = "" Then Item3_Label.Caption = "Item 3"
End Sub

'etcetera etcetera

I have over 50 of these clone lines of code in my userform. I think there is a better way to do it, but when I looked up using a Class EventHandler, I couldn't see how to apply it for my purposes.

Is there a way to prevent this repetitive code?

Thanks,

Elias

解决方案

'clsLabel
Public WithEvents oLabel As MSForms.Label

Public Sub oLabel_dblClick(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox oLabel.Caption
End Sub



'form code
Private colLabels As Collection

Private Sub UserForm_Initialize()
Dim o As Control, l As clsLabel
Set colLabels = New Collection
For Each o In Me.Controls
    If TypeName(o) = "Label" Then
        Set l = New clsLabel
        Set l.oLabel = o
        colLabels.Add l
    End If
Next o
End Sub

这篇关于EXCEL VBA：dblClick，Repetitive Code Improvement的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！