run sub routine with multiple conditions

[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

 

[George Nicholson]

A slightly different approach. This is aircode. It also might not reflect
exactly what you are trying to do. It but might give you some ideas in any
case.

Simplify. No array. Create one boolean variable. Set it to True at the
start. After that, only assign it a value if a False condition is
encountered. Only run your 2nd routine if the variable has remained True
through all testing.

I think you will find that a single Select Case, listing all your conditions
in a straightforward manner, is much easier to maintain, modify and debug
that a bunch of nested Ifs that start to resemble a mess of spaghetti if you
are lucky, a haystack if you are not.


Private Sub CommandButton1_Click()
Dim ctl As Control
Dim bolCheck as Boolean
Dim i as Integer
Dim wks as Worksheet

Set wks = "MyTarget"

bolCheck = True
For i = 1 to 23
Set ctl = UserForm1.Controls("Checkbox" & i)
If ctl = True Then
'This checkbox is selected. Is its related condition True?
Select Case i
Case 1
If Not wks.Cells(10 + i,12).value > wks.Cells(9 +
i,12).value Then bolCheck = False
Case 2
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i,
6).Value Then bolCheck = False
Case 3, 21
' Sample syntax if 2 checkboxes shared the same condition
for some reason
If Not wks.Cells(10 + i, 99).Value > wks.Cells(9 + i,
99).Value Then bolCheck = False
Case 4
' and so on for all 23 boxes
End Select
End If
If bolCheck = False Then
'(Optional)
'A related condition is False. No need to continue checking any more
boxes or conditions.
Exit For
End If
Next i

If bolCheck = True Then
'Do whatever it is you want to do if all conditions related to the
checked boxes are True
Call BlueRow
End If

End Sub

Sub BlueRow()
' color rows blue
Dim i as Integer
Dim wks as Worksheet

Set wks = "MyTarget"

For i = 1 to 23
If UserForm1.Controls("Checkbox" & i) = True Then
wks.Range(Cells(10 + i, 2), wks.Cells(10 + i,
16)).Interior.ColorIndex = 5
End If
Next i
End Sub

 

[hans via OfficeKB.com]

Thank you George for replying so quickly. I very much appreciate the code you
send.
I am not a professional level programmer but I think I can manage to figure
out the logic of your
approach and use it for my program.
I will respond as soon as I am done. Thanks again
Hans

 

[hans via OfficeKB.com]

George, I have adapted your code to my situation. Though I am intrigued about
using
a boolean variable for this problem, I cannot get the rows formatted blue yet.

The problem does not seem to be syntax, because I do not get an error code.
Is it that I have to relate the checkboxes to specific conditions first
before I can use
your code ?
If so, how would that have to be done?

This is a stockmarket analysis where I would choose any combination of
columns
for testing.
For example col3(price), col6(volume) and col7(upvolume). At another time I
might want
to look at col9 and col10 for example instead. But whatever the combination,
they always
have to true together at the same time.
Can you help with this ?
Here is the adapted code:

Private Sub CommandButton1_Click()
Dim ctl As Control
Dim bolCheck As Boolean
Dim i As Integer
Dim wks As Worksheet

Set wks = Worksheets("day1")

bolCheck = True
For i = 1 To 23
Set ctl = UserForm1.Controls("Checkbox" & i)
If ctl = True Then
'This checkbox is selected. Is its related condition True?
Select Case i

Case 1
If Not wks.Cells(10 + i, 3).Value > wks.Cells(9 + i, 3).Value
Then bolCheck = False
Case 2
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 3
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 4
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 5
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 6
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 7
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 8
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 9
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 10
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 11
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 12
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 13
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 14
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 15
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 16
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 17
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 18
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 19
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 20
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 21
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 22
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 23
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False

End Select
End If
If bolCheck = False Then
'(Optional)
'A related condition is False. No need to continue checking any more
'boxes or conditions.
Exit For
End If
Next i

If bolCheck = True Then
'Do whatever it is you want to do if all conditions related to the
'checked boxes are True
Call BlueRow

End If

End Sub

Sub BlueRow()
' color rows blue
Dim i As Integer
Dim wks As Worksheet

Set wks = Worksheets("day1")

For i = 1 To 23
If UserForm1.Controls("Checkbox" & i) = True Then
wks.Range(Cells(10 + i, 2), wks.Cells(10 + i, 16)).Interior.
ColorIndex = 5
End If
Next i
End Sub

 

[George Nicholson]

The code I provided is intended too change rows to Blue *only* if ALL the
related conditions for ALL selected checkboxes are True. If any related
condition is false, no rows will become Blue. Its all or nothing. Is that
what you want?

It also assumes (based on your original post), that each checkbox (1 to 23)
corresponds to a specific row that may or may not be changed to Blue.
Case 1 compares Cells(11, 3) to Cells(10, 3). if Checkbox 1 is
selected. Row 11 might turn blue
Case 2 compares Cells(12, 6) to Cells(11, 6). if Checkbox 2 is
selected. Row 12 might turn blue
Case 3 compares Cells(13, 6) to Cells(12, 6). if Checkbox 3 is
selected. Row 13 might turn blue
etc
Is that your intention?

Is it that I have to relate the checkboxes to specific conditions first
before I can use your code ?

Well, yes. As you have it Case 2 thru 23 are identical, so they could be
written:
......
Case 2 to 23
If Not wks.Cells(10 + i, 6) > wks.Cells(9 + i, 6) Then bolCheck =
False
End Select
......
And accomplish the same thing.


George

 

[hans via OfficeKB.com]

Yes, rows should be formatted blue only if all the related conditions for all
selected checkboxes are true. And if any related condition is false, then no
row should be formatted blue. You are correct, its all or nothing,
Here a description of what I am trying to do:
I have table of data where the first row represents the stock data of the day
and other calculated values. Each subsequent row represents another day
with its calculated values. So this table then has many rows and 16
columns of data.
I will then, using checkboxes choose a combination of several different
conditions for testing.
The program will loop through the first row and tests wether the combination
of all the different conditions on this particular day are true. If so, this
row
will be colored blue.
A typical condition would be: is todays volume higher than yesterdays?
ie. (if cells(10 + i,6)>cells(9 + i,6) Then).And there would be several more
of this kind

It will then move to the next row and repeat the test with those same
conditions
until it reaches the end of the table. I will then have a table where some
rows are
colored blue because they satisfied all selected conditions.
The purpose of this is to use the most promising combinations of conditions
for
forecasting bullish or bearish stock trends. It is a quick way to check out
your stock
system.
I accidentally copied the wrong “Case†lines previously, the code below is
the
correct one. I apologize for this and for any other confusion I caused
earlier.
Do you think this project can be done ?

Private Sub CommandButton1_Click()
Dim ctl As Control
Dim bolCheck As Boolean
Dim i As Integer
Dim wks As Worksheet

Set wks = Worksheets("day1")

bolCheck = True
For i = 1 To 23
Set ctl = UserForm1.Controls("Checkbox" & i)
If ctl = True Then
'This checkbox is selected. Is its related condition True?
Select Case i

Case 1
If Not wks.Cells(10 + i, 3).Value > wks.Cells(9 + i, 3).Value
Then bolCheck = False 'spread
Case 2
If Not wks.Cells(10 + i, 3).Value < wks.Cells(9 + i, 3).Value
Then bolCheck = False 'vol
Case 3
If Not wks.Cells(10 + i, 4).Value > wks.Cells(9 + i, 4).Value
Then bolCheck = False
Case 4
If Not wks.Cells(10 + i, 4).Value < wks.Cells(9 + i, 4).Value
Then bolCheck = False
Case 5
If Not wks.Cells(10 + i, 5).Value > wks.Cells(9 + i, 5).Value
Then bolCheck = False
Case 6
If Not wks.Cells(10 + i, 5).Value < wks.Cells(9 + i, 5).Value
Then bolCheck = False
Case 7
If Not wks.Cells(10 + i, 6).Value > wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 8
If Not wks.Cells(10 + i, 6).Value < wks.Cells(9 + i, 6).Value
Then bolCheck = False
Case 9
If Not wks.Cells(10 + i, 7).Value > wks.Cells(9 + i, 7).Value
Then bolCheck = False
Case 10
If Not wks.Cells(10 + i, 7).Value < wks.Cells(9 + i, 7).Value
Then bolCheck = False
Case 11
If Not wks.Cells(10 + i, 8).Value > wks.Cells(9 + i, 8).Value
Then bolCheck = False
Case 12
If Not wks.Cells(10 + i, 8).Value < wks.Cells(9 + i, 8).Value
Then bolCheck = False
Case 13
If Not wks.Cells(10 + i, 9).Value > wks.Cells(9 + i, 9).Value
Then bolCheck = False
Case 14
If Not wks.Cells(10 + i, 9).Value < wks.Cells(9 + i, 9).Value
Then bolCheck = False
Case 15
If Not wks.Cells(10 + i, 10).Value > wks.Cells(9 + i, 10).
Value Then bolCheck = False
Case 16
If Not wks.Cells(10 + i, 10).Value < wks.Cells(9 + i, 10).
Value Then bolCheck = False
Case 17
If Not wks.Cells(10 + i, 11).Value > wks.Cells(9 + i, 11).
Value Then bolCheck = False
Case 18
If Not wks.Cells(10 + i, 11).Value < wks.Cells(9 + i, 11).
Value Then bolCheck = False
Case 19
If Not wks.Cells(10 + i, 12).Value > wks.Cells(9 + i, 12).
Value Then bolCheck = False
Case 20
If Not wks.Cells(10 + i, 12).Value < wks.Cells(9 + i, 12).
Value Then bolCheck = False
Case 21
If Not wks.Cells(10 + i, 13).Value > wks.Cells(9 + i, 13).
Value Then bolCheck = False
Case 22
If Not wks.Cells(10 + i, 13).Value < wks.Cells(9 + i, 13).
Value Then bolCheck = False
Case 23
If Not wks.Cells(10 + i, 14).Value > wks.Cells(9 + i, 14).
Value Then bolCheck = False

End Select
End If
If bolCheck = False Then
'(Optional)
'A related condition is False. No need to continue checking any more
'boxes or conditions.
Exit For
End If
Next i

If bolCheck = True Then
'Do whatever it is you want to do if all conditions related to the
'checked boxes are True
'Call BlueRow
blue
End If

End Sub

Sub BlueRow()
' color rows blue
Dim i As Integer
Dim wks As Worksheet

Set wks = Worksheets("day1")

For i = 1 To 23
If UserForm1.Controls("Checkbox" & i) = True Then
wks.Range(Cells(10 + i, 2), wks.Cells(10 + i, 16)).Interior.
ColorIndex = 5
End If
Next i
End Sub

 

[George Nicholson]

OK. Sounds like you only want to evaluate ONE row at a time? Check that ONE
row against all selected conditions and turn it blue if all conditions are
true? Only then do we move on to the next row and do the same, etc. That's
fine, but different from my original understanding (which was that all
condition have to be true for all rows for anything to change color. I was
applying "all or nothing" to the entire table, not a single row.). It will
require some code changes.

What is the starting row? What determines end-of-table? Currently we are
using the same variable to loop through both checkboxes and rows. We have to
change that.

 

[snah via OfficeKB.com]

The table is usually 50 to 100 rows long. The starting row is A9 to P9.
The table is updated every day by adding the current stock data and
some calculated values. The table therefore is growing longer each
day by one row.
And yes, you are correct, I only would like to evaluate one row at a
time, and if all selected conditions are true, have that row colored blue.
The program then loops to the next row and repeats the testing with
those same conditions etc.
If you need more information George please let me know. Thanks
Hans

 

[George Nicholson]

See how this does:
- Row loop added
- Logic reworked a bit
- Case statements changed to utilize row counter
Keep in mind that this is all untested air-code

Private Sub CommandButton1_Click()
Dim ctl As Control
Dim bolCheck As Boolean
Dim i As Integer 'Checkbox counter
Dim r as Long 'Row counter
Dim wks As Worksheet

Set wks = Worksheets("day1")
r = 9

' Stop if Col A of next row is empty (nothing to compare to, so last row can
never be Blue)
Do Until Len(Trim(wks.Cells(r + 1,1))) = 0
bolCheck = True
For i = 1 To 23
Set ctl = UserForm1.Controls("Checkbox" & i)
If ctl = True Then
'This checkbox is selected. Is its related condition True?
Select Case i
Case 1
'Is Col C of Next row greater than Col C of Current row?
If Not wks.Cells(r + 1 , 3).Value > wks.Cells(r,3).Value Then
bolCheck = False 'spread
Case 2
'Is Col C of Next row less than than Col C of Current row?
If Not wks.Cells(r + 1, 3).Value < wks.Cells(r,3).Value Then
bolCheck = False 'vol
Case 3
'Is Col D of Next row greater than Col D of Current row?
If Not wks.Cells(r + 1, 4).Value > wks.Cells(r, 4).Value Then
bolCheck = False
Case 4
'Is Col D of Next row less than Col D of Current row?
If Not wks.Cells(r + 1, 4).Value < wks.Cells(r, 4).Value Then
bolCheck = False
'...... other cases
Case 23
'Is Col N of Next row greater than Col N of Current row?
If Not wks.Cells(r + 1, 14).Value > wks.Cells(r, 14).Value
Then bolCheck = False
End Select
End If
If bolCheck = False Then
'(Optional)
'A related condition is False. No need to continue checking any more
'boxes or conditions.
Exit For
End If
Next i

If BolCheck = True Then
' All selected conditions are true for this row (*or no conditions
(checkboxes) were selected*)
wks.Range(Cells(r, 2), wks.Cells(r, 16)).Interior.ColorIndex = 5
End If
r = r + 1
Loop

 

[hans via OfficeKB.com]

George, it works. Last night I finished the adaptation of your code, and it
works like a charm. The use of the Boolean variable was the answer I
was searching for so long. I added another routine that would in addition
to the rows, also color chart data points of a hi lo stock chart blue.
So now I can see right away whether these combination of those varies
conditions have a bullish or bearish potential for prediction.
Thanks so much for your time and code.
Hans