+ Reply to Thread
Results 1 to 4 of 4

vba stop checking IF statements when condition is met

Hybrid View

  1. #1
    Registered User
    Join Date
    04-22-2016
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    22

    vba stop checking IF statements when condition is met

    The spreadsheet is a checklist.
    The users answer to each question by "Yes" or "No" (via a drop down)

    Before that, another code establishes which user profile is applicable ("A", "B" or "C") and hides other rows (that are by default not applicable to the concerned profile)

    This code below should hide or show rows depending on the answer to specific questions.
    and if the user profile is "B" (Sheet5.Cells(8, 10).Value = "B") , the three last IF statements statements should not be checked (because the profile code directly hides the corresponding rows)
    If profile is not "B" then all statements should be checked.
    I tried this but it doesn't work:
    if 'profile is "B" then
    stop
    end if

    thank you for any help.
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
        If Not Intersect(Target, Range("D24")) Is Nothing Then 'ch. systems
    
            Sheets("CL_assurance_package").Rows("26:30").EntireRow.Hidden = Not (Target.Value) = "Yes"
            Sheets("CL_assurance_package").Rows("26:30").EntireRow.Hidden = (Target.Value) = "No"
    
        End If
        
            If Not Intersect(Target, Range("D33")) Is Nothing Then 'ch.strategy
    
            Sheets("CL_assurance_package").Rows("35:36").EntireRow.Hidden = Not (Target.Value) = "Yes"
            Sheets("CL_assurance_package").Rows("35:36").EntireRow.Hidden = (Target.Value) = "No"
    
        End If
        
            If Not Intersect(Target, Range("D39")) Is Nothing Then 'ct. systems
    
            Sheets("CL_assurance_package").Rows("41:55").EntireRow.Hidden = Not (Target.Value) = "Yes"
            Sheets("CL_assurance_package").Rows("41:55").EntireRow.Hidden = (Target.Value) = "No"
    
        End If
    
    ' *** at this point if Sheet5.Cells(8, 10).Value = "B" I want the code to stop checking the statements below ***
    
    
    
    
    ' *** If value in Sheet5.Cells(8, 10) is different than "B" then continue checking all the statements***
    
            If Not Intersect(Target, Range("D58")) Is Nothing Then 'ct. operations
    
            Sheets("CL_assurance_package").Rows("60:106").EntireRow.Hidden = Not (Target.Value) = "Yes"
            Sheets("CL_assurance_package").Rows("60:106").EntireRow.Hidden = (Target.Value) = "No"
    
        End If
        
           If Not Intersect(Target, Range("D109")) Is Nothing Then 'ct. acounts
    
            Sheets("CL_assurance_package").Rows("111:123").EntireRow.Hidden = Not (Target.Value) = "Yes"
            Sheets("CL_assurance_package").Rows("111:123").EntireRow.Hidden = (Target.Value) = "No"
    
        End If
        
      
          If Not Intersect(Target, Range("D109")) Is Nothing Then 'cl. acounts
    
            Sheets("CL_assurance_package").Rows("126:160").EntireRow.Hidden = Not (Target.Value) = "Yes"
            Sheets("CL_assurance_package").Rows("126:160").EntireRow.Hidden = (Target.Value) = "No"
    
        End If
    Application.EnableEvents = True
    End Sub

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: vba stop checking IF statements when condition is met

    Try this method:

    Select Case is the same as using IF statements but with all your current IFs this makes it a bit easier to see what is going on


    'at this point if Sheet5.Cells(8, 10).Value = "B" I want the code to stop checking the statements below ***
    
    Select Case Sheet5.Cells(8, 10).Value
        Case Is <> "B"
    ' *** If value in Sheet5.Cells(8, 10) is different than "B" then continue checking all the statements***
        
                If Not Intersect(Target, Range("D58")) Is Nothing Then 'ct. operations
        
                Sheets("CL_assurance_package").Rows("60:106").EntireRow.Hidden = Not (Target.Value) = "Yes"
                Sheets("CL_assurance_package").Rows("60:106").EntireRow.Hidden = (Target.Value) = "No"
        
            End If
            
               If Not Intersect(Target, Range("D109")) Is Nothing Then 'ct. acounts
        
                Sheets("CL_assurance_package").Rows("111:123").EntireRow.Hidden = Not (Target.Value) = "Yes"
                Sheets("CL_assurance_package").Rows("111:123").EntireRow.Hidden = (Target.Value) = "No"
        
            End If
            
              If Not Intersect(Target, Range("D109")) Is Nothing Then 'cl. acounts
        
                Sheets("CL_assurance_package").Rows("126:160").EntireRow.Hidden = Not (Target.Value) = "Yes"
                Sheets("CL_assurance_package").Rows("126:160").EntireRow.Hidden = (Target.Value) = "No"
        
            End If
        Case Else
        ' do nothing
    End Select
    Last edited by kev_; 02-12-2017 at 11:02 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Registered User
    Join Date
    04-22-2016
    Location
    Belgium
    MS-Off Ver
    2010
    Posts
    22

    Re: vba stop checking IF statements when condition is met

    Excellent!
    Worked perfectly!

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: vba stop checking IF statements when condition is met

    Glad it was useful
    Thanks for the reps

    Please mark the thread as solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Checking age before calculating on the basis of IF condition
    By Zanys in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-08-2016, 10:35 PM
  2. [SOLVED] Can I nest IF statements, if the first one is checking for data?
    By Barnaclebot in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-15-2013, 03:25 PM
  3. [SOLVED] IF condition checking across 3 cells.
    By Ali79 in forum Excel General
    Replies: 10
    Last Post: 09-28-2012, 07:00 AM
  4. Replies: 7
    Last Post: 03-18-2012, 01:44 AM
  5. Excel 2007 : Macro to stop background checking?
    By JK1234 in forum Excel General
    Replies: 2
    Last Post: 03-19-2009, 11:03 AM
  6. Replies: 6
    Last Post: 10-31-2008, 11:52 AM
  7. True/false statements checking for range
    By krayziez in forum Excel General
    Replies: 5
    Last Post: 05-29-2006, 04:52 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1