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,
untested but try:

Private Sub Tax_Click()
    Dim i As Integer
    For i = 1 To 7
        Me.Controls("CheckBox" & i).Value = CBool(Range("v6").Value <> ("No Tax") And Len(Cells(18 + i, 6)) > 0)
    Next i
End Sub

Dave

 

[snjpverma]

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

 

[Ruddles]

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 = ""

 

[Caleeco]

Untested, but you could give this a go:

Private Sub Tax_Click()
Dim i As Long


If ActiveSheet.Range("V6").Value <> ("No Tax") Then
    For i = 19 To 38
        If Range("F" & i).Value <> "" Then ActiveSheet.Shapes("CheckBox" & i - 18).Value = xlOn
    Next i
End If


End Sub

Caleeco

 

[Roderick_E]

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

 

[Avogadro]

Hi Dave:

I tried this but I get an error at Me.Controls (method or data member not found.) The check box is an Activex.

 

[Avogadro]

Hi:

This code worked, It is an activex checkbox. I had to delete the resume x.

Thank you

 

[dmt32]

Hi Dave:

I tried this but I get an error at Me.Controls (method or data member not found.) The check box is an Activex.

try update:

Private Sub Tax_Click()
     Dim i As Integer    
    For i = 1 To 7
        Me.OLEObjects("CheckBox" & i).Object.Value = CBool(Me.Range("v6").Value <> ("No Tax") And Len(Me.Cells(18 + i, 6)) > 0)
    Next i
End Sub

This assumes that code is in your worksheets code page.

Dave

 

[Avogadro]

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