Excel VBA_类代码实例集锦 下载本文

1, 类动态数组控件

‘2007VBA技巧

‘快盘\\Mytb\\更新\\类\\类动态数组控件.xlsm ‘2013-6-16 类模块代码:

Public WithEvents frm As MSForms.UserForm Public WithEvents myText As MSForms.TextBox Public Index As Integer

Private Sub myText_Change() Index = Mid(myText.Name, 8)

If frm.Controls(\

frm.Label1.Caption = \控件事件:Change\

\控件名称:\ \属性:\End If End Sub

Private Sub myText_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Index = Mid(myText.Name, 8)

If frm.Controls(\

frm.Label1.Caption = \控件事件:DblClick\

\控件名称:\ \属性:\End If End Sub

KeyUp事件与Change事件重迭,二者取其一

Private Sub myText_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Index = Mid(myText.Name, 8)

If frm.Controls(\

frm.Label1.Caption = \控件事件:KeyUp\

\控件名称:\ \按键值:&H\End If End Sub

Private Sub myText_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Select Case Index Case 3

Userform2.Label2.Caption = \Case 8

Userform2.Label2.Caption = \Case 4

Userform2.Label2.Caption = \Case 9

Userform2.Label2.Caption = \Case Else

Userform2.Label2.Caption = \End Select End Sub

模块1代码:

Public a(1 To 14) As myText Sub formshow() Userform2.Show End Sub

窗体代码:

Private Sub CommandButton1_Click() Dim i&, t$ For i = 1 To 14

If a(i).myText.Text <> \ t = t & \控件名称:\属性:\& vbCrLf

End If Next i MsgBox t End Sub

Private Sub UserForm_Initialize() Dim i&

For i = 1 To 14

Set a(i) = New myText

Set a(i).myText = Me.Controls(\ Set a(i).frm = Me Next i End Sub

工作表代码:

Private Sub CommandButton1_Click() Userform2.Show

End Sub

2, 复选框选择

‘快盘\\Mytb\\更新\\类\\类0928..xls

‘当复选框选择到7个时,其它的复选框不能再选择。当复选框选择小于7个,其它的复选框还能继续选择。

类模块代码:

Public WithEvents che As MSForms.CheckBox Public WithEvents frm As MSForms.UserForm Private Sub che_Change() '类的数据改变事件 Dim index As Long

index = Mid(che.Name, 9) '取出checkboxN中的数字N If frm.Controls(\ a = a & Format(index, \ n = n + 1 If n = 7 Then

For i = 1 To 18

b = Format(i, \ If InStr(a, b) = 0 Then

frm.Controls(\ End If Next Else End If Else

n = n - 1

a = Replace(a, Format(index, \ For i = 1 To 18

frm.Controls(\ Next End If End Sub

模块1代码:

Public newclass(1 To 18) As che类, n&, a$ Sub formshow() UserForm1.Show End Sub

窗体代码:

Private Sub UserForm_Initialize()