If and statements.

[Avogadro]

Can anyone help me refine this macro.

Private Sub Tax_Click()
Application.ScreenUpdating = False

If Range("v6").Value <> ("No Tax") And Range("f19").Value <> blank Then
 CheckBox1.Value = True
End If

If Range("v6").Value <> ("No Tax") And Range("f20").Value <> blank Then
 CheckBox2.Value = True
 End If

If Range("v6").Value <> ("No Tax") And Range("f21").Value <> blank Then
 CheckBox3.Value = True
End If

If Range("v6").Value <> ("No Tax") And Range("f22").Value <> blank Then
 CheckBox4.Value = True
End If

If Range("v6").Value <> ("No Tax") And Range("f23").Value <> blank Then
 CheckBox5.Value = True
End If

If Range("v6").Value <> ("No Tax") And Range("f24").Value <> blank Then
 CheckBox6.Value = True
End If

If Range("v6").Value <> ("No Tax") And Range("f25").Value <> blank Then
 CheckBox7.Value = True
End If

If Range("v6").Value <> ("No Tax") And Range("f26").Value <> blank Then
 CheckBox8.Value = True
End If

If Range("v6").Value <> ("No Tax") And Range("f27").Value <> blank Then
 CheckBox9.Value = True
End If

 If Range("v6").Value <> ("No Tax") And Range("f28").Value <> blank Then
 CheckBox10.Value = True
End If

 If Range("v6").Value <> ("No Tax") And Range("f29").Value <> blank Then
 CheckBox11.Value = True
End If

If Range("v6").Value <> ("No Tax") And Range("f30").Value <> blank Then
 CheckBox12.Value = True
 End If

If Range("v6").Value <> ("No Tax") And Range("f31").Value <> blank Then
 CheckBox13.Value = True
End If

If Range("v6").Value <> ("No Tax") And Range("f32").Value <> blank Then
 CheckBox14.Value = True
End If

 If Range("v6").Value <> ("No Tax") And Range("f33").Value <> blank Then
 CheckBox15.Value = True
End If

 If Range("v6").Value <> ("No Tax") And Range("f34").Value <> blank Then
 CheckBox16.Value = True
End If

If Range("v6").Value <> ("No Tax") And Range("f35").Value <> blank Then
 CheckBox17.Value = True
 End If

If Range("v6").Value <> ("No Tax") And Range("f36").Value <> blank Then
 CheckBox18.Value = True
End If

If Range("v6").Value <> ("No Tax") And Range("f37").Value <> blank Then
 CheckBox19.Value = True
End If

If Range("v6").Value <> ("No Tax") And Range("f38").Value <> blank Then
 CheckBox20.Value = True
 End If
 
End Sub

 

[dmt32]

Hi Dave:

That works incredibly well. Can you explain this line:

Me.OLEObjects("CheckBox" & i).Object.Value = CBool(Me.Range("v6").Value <> ("No Tax") And Len(Me.Cells(18 + i, 6)) > 0)

Jim

Hi,
glad solution worked ok for you.

To try & explain:
You have 7 ActiveX Checkboxes on your worksheet with their default names (CheckBox1, CheckBox2 etc) The For Next loop cycles through each checkbox by using integer Variable i to index them.

     Dim i As Integer
    For i = 1 To 7
        Me.OLEObjects("CheckBox" & i).Object.Value
    Next i

to set the state of the checkbox (True / False) you do so by testing to see if the ranges meet your required conditions which is done by this part:

= CBool(Me.Range("v6").Value <> ("No Tax") And Len(Me.Cells(18 + i, 6)) > 0)

Whilst Range V6 is static, I use variable i to index the row number in column 6 (F)
The Len Function determines if the Cell contains anything.

If both ranges meet your required conditions I use CBool Type Conversion Function
to return Boolean value True otherwise False to set the checkbox state.

Hope Helpful

Dave

 

[dmt32]

dmt32, nice code. Though I haven't tried it. But it is so smaller than the original one.

Solution was written on assumption code was for a UserForm - see updated solution for ActiveX worksheet control.

Dave

 

[snjpverma]

Thanks Dave

 

[Avogadro]

Thank you, still a bit over my head but I am learning. Would you be interested in helping me debug another macro? Much more complicated.

 

[dmt32]

Thank you, still a bit over my head but I am learning. Would you be interested in helping me debug another macro? Much more complicated.

If you require help with a new excel related problem then start a new thread. This will afford others to offer you help.

Dave

 

[emailtooaj]

Could this code be modified to change the value of the cell in-lieu of "activating" the corresponding checkbox?
Example:
So instead of this
If Range("f19").Value <> blank Then CheckBox1.Value = True

Change it to this...
If Range("f19").Value <> blank Then "d22".Value = True

Well you can just test V6 once - that makes it slightly tidier:-

Private Sub Tax_Click()

Application.ScreenUpdating = False

  If Range("v6").Value <> "No Tax" Then
    If Range("f19").Value <> blank Then CheckBox1.Value = True
    If Range("f20").Value <> blank Then CheckBox2.Value = True
    If Range("f21").Value <> blank Then CheckBox3.Value = True
    If Range("f22").Value <> blank Then CheckBox4.Value = True
    If Range("f23").Value <> blank Then CheckBox5.Value = True
    If Range("f24").Value <> blank Then CheckBox6.Value = True
    If Range("f25").Value <> blank Then CheckBox7.Value = True
    If Range("f26").Value <> blank Then CheckBox8.Value = True
    If Range("f27").Value <> blank Then CheckBox9.Value = True
    If Range("f28").Value <> blank Then CheckBox10.Value = True
    If Range("f29").Value <> blank Then CheckBox11.Value = True
    If Range("f30").Value <> blank Then CheckBox12.Value = True
    If Range("f31").Value <> blank Then CheckBox13.Value = True
    If Range("f32").Value <> blank Then CheckBox14.Value = True
    If Range("f33").Value <> blank Then CheckBox15.Value = True
    If Range("f34").Value <> blank Then CheckBox16.Value = True
    If Range("f35").Value <> blank Then CheckBox17.Value = True
    If Range("f36").Value <> blank Then CheckBox18.Value = True
    If Range("f37").Value <> blank Then CheckBox19.Value = True
    If Range("f38").Value <> blank Then CheckBox20.Value = True
  End If
 
End Sub

But you haven't defined blank anywhere, so I'd recommend replacing it throughout the code by "" (two quotation marks) OR inserting this immediately after the Private Sub Tax_Click() line:-

Const blank as String = ""

 

[Ruddles]

Generally, the IF statement is IF [condition] THEN [action], so:-

If Range("f19") <> blank Then Range("d22") = True

I repeat though, blank doesn't have any special value in VBA, so you will have to set it to whatever value you want it to represent before you use it. personally I would always use "" to represent an empty (zero-length) character string.

 

[Roderick_E]

Sniffle.... my code account for all 3 types of checkboxes

Private sub tax_click
'this handles all 3 types of checkboxes since that wasn't specified
Application.ScreenUpdating = False
On Error Resume Next
For x = 1 To 18
If Range("v6").Value <> ("No Tax") And Range("f" & 18 + x).Value <> blank Then
ActiveSheet.Shapes("Check Box " & x).OLEFormat.Object.Value = True 'for worksheet form controls
ActiveSheet.OLEObjects("CheckBox" & x).Object.Value = True 'for worksheet ActiveX controls
userform1.Controls("CheckBox" & x).Value = True 'for userform controls
End If
Next x
Application.ScreenUpdating = true
Resume Next

end sub