H
hans via OfficeKB.com
Hi everybody
Essentially my question is, how can I select any combination of checkboxes
each with their own conditions attached, and have a routine run only if all
of these selected conditions are true at the same time. Here is what I have
tried so far.
On my userform I have 23 checkboxes. Each checkbox has a different condition
attached to it. For example:
If myarray(3) = “checkbox3†AND Cells(10 + coun,12).value > Cells(9 + coun,12)
..value Then
A For Each Loop will collect all selected checkboxes in a dynamic array, and
a Do Loop will then test each row in a table of stock data from column a9 to
p9. The row that meets all selected conditions will be formatted blue.
Thanks for any help.
Option Explicit
Option Base 1
Dim coun
Private Sub CommandButton1_Click()
Dim ctl As Control
Dim myarray()
Dim a
'collect clicked checkboxes in an array
For Each ctl In UserForm1.Controls
If TypeName(ctl) = "CheckBox" Then
If ctl.Value = True Then
a = a + 1
ReDim Preserve myarray(1 To a)
myarray(a) = ctl.Caption 'add checkbox name to array members
End If
End If
Next ctl
'loop through rows of data
Application.ScreenUpdating = False
coun = 1
Sheets("day1").Select
Range("c9").Select
Do Until Selection = ""
'here is where my problem starts. How to get all the
'selected conditions to act on each row of data at the
same time.
'below are some of those conditions.
‘if myarray(1) = "CheckBox1" And Cells(10 + coun, 12).Value > Cells
(9 + coun, 12).Value then
‘If myarray(2) = "CheckBox2" And Cells(10 + coun, 6).Value > Cells
(9 + ‘coun, ‘6).Value then
'there are 21 more checkboxes and conditions.It then
continues:
'if all the conditions in myarray are met then run routine
“ blueâ€
blue
End If
End If
coun = coun + 1
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Sub blue()
'color rows blue
Range(Cells(10 + coun, 2), Cells(10 + coun, 16)).Interior.ColorIndex =
5
End sub
Essentially my question is, how can I select any combination of checkboxes
each with their own conditions attached, and have a routine run only if all
of these selected conditions are true at the same time. Here is what I have
tried so far.
On my userform I have 23 checkboxes. Each checkbox has a different condition
attached to it. For example:
If myarray(3) = “checkbox3†AND Cells(10 + coun,12).value > Cells(9 + coun,12)
..value Then
A For Each Loop will collect all selected checkboxes in a dynamic array, and
a Do Loop will then test each row in a table of stock data from column a9 to
p9. The row that meets all selected conditions will be formatted blue.
Thanks for any help.
Option Explicit
Option Base 1
Dim coun
Private Sub CommandButton1_Click()
Dim ctl As Control
Dim myarray()
Dim a
'collect clicked checkboxes in an array
For Each ctl In UserForm1.Controls
If TypeName(ctl) = "CheckBox" Then
If ctl.Value = True Then
a = a + 1
ReDim Preserve myarray(1 To a)
myarray(a) = ctl.Caption 'add checkbox name to array members
End If
End If
Next ctl
'loop through rows of data
Application.ScreenUpdating = False
coun = 1
Sheets("day1").Select
Range("c9").Select
Do Until Selection = ""
'here is where my problem starts. How to get all the
'selected conditions to act on each row of data at the
same time.
'below are some of those conditions.
‘if myarray(1) = "CheckBox1" And Cells(10 + coun, 12).Value > Cells
(9 + coun, 12).Value then
‘If myarray(2) = "CheckBox2" And Cells(10 + coun, 6).Value > Cells
(9 + ‘coun, ‘6).Value then
'there are 21 more checkboxes and conditions.It then
continues:
'if all the conditions in myarray are met then run routine
“ blueâ€
blue
End If
End If
coun = coun + 1
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Sub blue()
'color rows blue
Range(Cells(10 + coun, 2), Cells(10 + coun, 16)).Interior.ColorIndex =
5
End sub