VBA- Trying to combine; For each cell in range and Else

G

Glenwalcuch

New member
Joined
Sep 3, 2015
Messages
1
Reaction score
0
Points
0
All I need is for the following code to work. Should be simple;

Code: 
Private Sub Worksheet_Calculate() 

    For Each cell In Range("W7:W17") 
        If cell.Value = "Payment Not Required" Then 
        ActiveSheet.CheckBoxes("Check Box 58").Visible = True 
        Else 
        ActiveSheet.CheckBoxes("Check Box 58").Visible = False 
        End If 
        Next 
        If cell.Value > 0 Then 
        ActiveSheet.CheckBoxes("Check Box 61").Visible = True 
        Else 
        ActiveSheet.CheckBoxes("Check Box 61").Visible = False 
        End If 
End Sub

As ever, any help would be greatly appreciated. Upon a successful amendment any further guidance on how to expand the solution to contain other conditions (If's and else's) would also be of use in a bid to learn and be more self sufficient.

Thanks,

Glen​
 
Don't know what you're trying to do, but even if both of the check boxes were inside the For Each - Next loop, (by moving Next to the end), cells W7 thru W16 would have no bearing on the end result for the check boxes.
 
Is this what you're looking for ?

Code: 
Private Sub Worksheet_Calculate()

    If WorksheetFunction.CountIf(Range("W7:W17"), "Payment Not Required") > 0 Then
        ActiveSheet.CheckBoxes("Check Box 58").Visible = True
    Else
        ActiveSheet.CheckBoxes("Check Box 58").Visible = False
    End If
    
    If WorksheetFunction.CountIf(Range("W7:W17"), "> 0") > 0 Then
        ActiveSheet.CheckBoxes("Check Box 61").Visible = True
    Else
        ActiveSheet.CheckBoxes("Check Box 61").Visible = False
    End If

End Sub

Should also have a look at this here.
 
You must log in or register to reply here.
Back
Top