Results 1 to 3 of 3

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    All I need is for the following code to work. Should be simple;

    PHP 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

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •