VBA不支持控件数组,郁闷呀! 你把下面这段代码贴到Sheet1的代码模块中: Option ExplicitPrivate Sub CheckBox1_Click() If (CheckBox1 = True) Then Range("A3:K3").Interior.ColorIndex = 40 Else Range("A3:K3").Interior.ColorIndex = xlNone End IfEnd SubPrivate Sub CheckBox2_Click() If (CheckBox2 = True) Then Range("A4:K4").Interior.ColorIndex = 40 Else Range("A4:K4").Interior.ColorIndex = xlNone End IfEnd SubPrivate Sub CheckBox3_Click() If (CheckBox3 = True) Then Range("A5:K5").Interior.ColorIndex = 40 Else Range("A5:K5").Interior.ColorIndex = xlNone End IfEnd SubPrivate Sub CheckBox4_Click() If (CheckBox4 = True) Then Range("A6:K6").Interior.ColorIndex = 40 Else Range("A6:K6").Interior.ColorIndex = xlNone End IfEnd SubPrivate Sub CheckBox5_Click() If (CheckBox5 = True) Then Range("A7:K7").Interior.ColorIndex = 40 Else Range("A7:K7").Interior.ColorIndex = xlNone End IfEnd SubPrivate Sub CheckBox6_Click() If (CheckBox6 = True) Then Range("A8:K8").Interior.ColorIndex = 40 Else Range("A8:K8").Interior.ColorIndex = xlNone End IfEnd SubPrivate Sub CheckBox7_Click() If (CheckBox7 = True) Then Range("A9:K9").Interior.ColorIndex = 40 Else Range("A9:K9").Interior.ColorIndex = xlNone End IfEnd SubPrivate Sub CheckBox8_Click() If (CheckBox8 = True) Then Range("A10:K10").Interior.ColorIndex = 40 Else Range("A10:K10").Interior.ColorIndex = xlNone End IfEnd SubPrivate Sub CheckBox9_Click() If (CheckBox9 = True) Then Range("A11:K11").Interior.ColorIndex = 40 Else Range("A11:K11").Interior.ColorIndex = xlNone End IfEnd SubPrivate Sub CommandButton1_Click() Dim i%, j& Dim objRangA As Range, objRangB As Range
j = 3 While (Sheet2.Range("A" & j).Text > ""): j = j + 1: Wend If (CheckBox1 = True) Then Set objRangA = Sheet1.Range("A" & (3)) Set objRangB = Sheet2.Range("A" & j) For i = 1 To 11 objRangB.Columns(i).Formula = objRangA.Columns(i).Text Next j = j + 1 End If If (CheckBox2 = True) Then Set objRangA = Sheet1.Range("A" & (4)) Set objRangB = Sheet2.Range("A" & j) For i = 1 To 11 objRangB.Columns(i).Formula = objRangA.Columns(i).Text Next j = j + 1 End If If (CheckBox3 = True) Then Set objRangA = Sheet1.Range("A" & (5)) Set objRangB = Sheet2.Range("A" & j) For i = 1 To 11 objRangB.Columns(i).Formula = objRangA.Columns(i).Text Next j = j + 1 End If If (CheckBox4 = True) Then Set objRangA = Sheet1.Range("A" & (6)) Set objRangB = Sheet2.Range("A" & j) For i = 1 To 11 objRangB.Columns(i).Formula = objRangA.Columns(i).Text Next j = j + 1 End If If (CheckBox5 = True) Then Set objRangA = Sheet1.Range("A" & (7)) Set objRangB = Sheet2.Range("A" & j) For i = 1 To 11 objRangB.Columns(i).Formula = objRangA.Columns(i).Text Next j = j + 1 End If If (CheckBox6 = True) Then Set objRangA = Sheet1.Range("A" & (8)) Set objRangB = Sheet2.Range("A" & j) For i = 1 To 11 objRangB.Columns(i).Formula = objRangA.Columns(i).Text Next j = j + 1 End If If (CheckBox7 = True) Then Set objRangA = Sheet1.Range("A" & (9)) Set objRangB = Sheet2.Range("A" & j) For i = 1 To 11 objRangB.Columns(i).Formula = objRangA.Columns(i).Text Next j = j + 1 End If If (CheckBox8 = True) Then Set objRangA = Sheet1.Range("A" & (10)) Set objRangB = Sheet2.Range("A" & j) For i = 1 To 11 objRangB.Columns(i).Formula = objRangA.Columns(i).Text Next j = j + 1 End If If (CheckBox9 = True) Then Set objRangA = Sheet1.Range("A" & (11)) Set objRangB = Sheet2.Range("A" & j) For i = 1 To 11 objRangB.Columns(i).Formula = objRangA.Columns(i).Text Next j = j + 1 End If Set objRangA = Nothing Set objRangB = NothingEnd Sub
要循环很简单 Private Sub CommandButton1_Click() Dim aChecks() Dim i As Long
aChecks = Array(CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5, CheckBox6, CheckBox7, CheckBox8, CheckBox9) For i = 0 To UBound(aChecks) Debug.Print i, aChecks(i) Next End Sub
Private Sub CommandButton1_Click() Dim aChecks() Dim i As Long
aChecks = Array(CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5, CheckBox6, CheckBox7, CheckBox8, CheckBox9) For i = 0 To UBound(aChecks) Debug.Print i, aChecks(i) Next End Sub '--------------------- 欢迎来我的小店坐坐: 程序员雅琪的小店
你把下面这段代码贴到Sheet1的代码模块中:
Option ExplicitPrivate Sub CheckBox1_Click() If (CheckBox1 = True) Then
Range("A3:K3").Interior.ColorIndex = 40
Else
Range("A3:K3").Interior.ColorIndex = xlNone
End IfEnd SubPrivate Sub CheckBox2_Click() If (CheckBox2 = True) Then
Range("A4:K4").Interior.ColorIndex = 40
Else
Range("A4:K4").Interior.ColorIndex = xlNone
End IfEnd SubPrivate Sub CheckBox3_Click() If (CheckBox3 = True) Then
Range("A5:K5").Interior.ColorIndex = 40
Else
Range("A5:K5").Interior.ColorIndex = xlNone
End IfEnd SubPrivate Sub CheckBox4_Click() If (CheckBox4 = True) Then
Range("A6:K6").Interior.ColorIndex = 40
Else
Range("A6:K6").Interior.ColorIndex = xlNone
End IfEnd SubPrivate Sub CheckBox5_Click() If (CheckBox5 = True) Then
Range("A7:K7").Interior.ColorIndex = 40
Else
Range("A7:K7").Interior.ColorIndex = xlNone
End IfEnd SubPrivate Sub CheckBox6_Click() If (CheckBox6 = True) Then
Range("A8:K8").Interior.ColorIndex = 40
Else
Range("A8:K8").Interior.ColorIndex = xlNone
End IfEnd SubPrivate Sub CheckBox7_Click() If (CheckBox7 = True) Then
Range("A9:K9").Interior.ColorIndex = 40
Else
Range("A9:K9").Interior.ColorIndex = xlNone
End IfEnd SubPrivate Sub CheckBox8_Click() If (CheckBox8 = True) Then
Range("A10:K10").Interior.ColorIndex = 40
Else
Range("A10:K10").Interior.ColorIndex = xlNone
End IfEnd SubPrivate Sub CheckBox9_Click() If (CheckBox9 = True) Then
Range("A11:K11").Interior.ColorIndex = 40
Else
Range("A11:K11").Interior.ColorIndex = xlNone
End IfEnd SubPrivate Sub CommandButton1_Click() Dim i%, j&
Dim objRangA As Range, objRangB As Range
j = 3
While (Sheet2.Range("A" & j).Text > ""): j = j + 1: Wend
If (CheckBox1 = True) Then
Set objRangA = Sheet1.Range("A" & (3))
Set objRangB = Sheet2.Range("A" & j)
For i = 1 To 11
objRangB.Columns(i).Formula = objRangA.Columns(i).Text
Next
j = j + 1
End If
If (CheckBox2 = True) Then
Set objRangA = Sheet1.Range("A" & (4))
Set objRangB = Sheet2.Range("A" & j)
For i = 1 To 11
objRangB.Columns(i).Formula = objRangA.Columns(i).Text
Next
j = j + 1
End If
If (CheckBox3 = True) Then
Set objRangA = Sheet1.Range("A" & (5))
Set objRangB = Sheet2.Range("A" & j)
For i = 1 To 11
objRangB.Columns(i).Formula = objRangA.Columns(i).Text
Next
j = j + 1
End If
If (CheckBox4 = True) Then
Set objRangA = Sheet1.Range("A" & (6))
Set objRangB = Sheet2.Range("A" & j)
For i = 1 To 11
objRangB.Columns(i).Formula = objRangA.Columns(i).Text
Next
j = j + 1
End If
If (CheckBox5 = True) Then
Set objRangA = Sheet1.Range("A" & (7))
Set objRangB = Sheet2.Range("A" & j)
For i = 1 To 11
objRangB.Columns(i).Formula = objRangA.Columns(i).Text
Next
j = j + 1
End If
If (CheckBox6 = True) Then
Set objRangA = Sheet1.Range("A" & (8))
Set objRangB = Sheet2.Range("A" & j)
For i = 1 To 11
objRangB.Columns(i).Formula = objRangA.Columns(i).Text
Next
j = j + 1
End If
If (CheckBox7 = True) Then
Set objRangA = Sheet1.Range("A" & (9))
Set objRangB = Sheet2.Range("A" & j)
For i = 1 To 11
objRangB.Columns(i).Formula = objRangA.Columns(i).Text
Next
j = j + 1
End If
If (CheckBox8 = True) Then
Set objRangA = Sheet1.Range("A" & (10))
Set objRangB = Sheet2.Range("A" & j)
For i = 1 To 11
objRangB.Columns(i).Formula = objRangA.Columns(i).Text
Next
j = j + 1
End If
If (CheckBox9 = True) Then
Set objRangA = Sheet1.Range("A" & (11))
Set objRangB = Sheet2.Range("A" & j)
For i = 1 To 11
objRangB.Columns(i).Formula = objRangA.Columns(i).Text
Next
j = j + 1
End If
Set objRangA = Nothing
Set objRangB = NothingEnd Sub
CommandButton1 的 Click() 事件中就可以用循环来处理了。
Private Sub CommandButton1_Click()
Dim aChecks()
Dim i As Long
aChecks = Array(CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5, CheckBox6, CheckBox7, CheckBox8, CheckBox9)
For i = 0 To UBound(aChecks)
Debug.Print i, aChecks(i)
Next
End Sub
Dim aChecks()
Dim i As Long
aChecks = Array(CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5, CheckBox6, CheckBox7, CheckBox8, CheckBox9)
For i = 0 To UBound(aChecks)
Debug.Print i, aChecks(i)
Next
End Sub
'---------------------
欢迎来我的小店坐坐: 程序员雅琪的小店