+ Reply to Thread
Results 1 to 7 of 7

Case Statement with Multiple Conditions

Hybrid View

ezrizer Case Statement with Multiple... 05-11-2014, 08:56 AM
TMS Re: Case Statement with... 05-11-2014, 09:20 AM
punna111 Re: Case Statement with... 05-11-2014, 09:26 AM
jindon Re: Case Statement with... 05-11-2014, 10:22 AM
ezrizer Re: Case Statement with... 05-12-2014, 06:59 AM
jindon Re: Case Statement with... 05-12-2014, 07:35 AM
ezrizer Re: Case Statement with... 05-12-2014, 09:13 AM
  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    41

    Case Statement with Multiple Conditions

    Hello,

    I'm trying to write a case statement for 4 different cases based on 2 worksheets and if they are visible or not. Ex.

    Case Neither x or y visible
    Do 1

    Case Both x and y visible
    Do 2

    Case Sheet x visible
    Do 3

    Case Sheet y visible
    Do 4

    There will always be other sheets visible so I need the cases to be specific. I've tried multiple combinations and can't get them to work as describe above. Biggest problem is when both or neither are visible.

    Thanks for the help.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,201

    Re: Case Statement with Multiple Conditions

    Maybe try:

    Option Explicit
    
    Sub Test()
    
    Dim ws2 As Worksheet: Set ws2 = Worksheet("Sheet2")
    Dim ws3 As Worksheet: Set ws3 = Worksheet("Sheet3")
    
    If ws2.Visible = xlSheetVisible And ws3.Visible = xlSheetVisible Then
        Call sub1
    End If
    If Not ws2.Visible = xlSheetVisible And Not ws3.Visible = xlSheetVisible Then
        Call sub2
    End If
    If ws2.Visible = xlSheetVisible And Not ws3.Visible = xlSheetVisible Then
        Call sub3
    End If
    If Not ws2.Visible = xlSheetVisible And ws3.Visible = xlSheetVisible Then
        Call sub4
    End If
    
    End Sub

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    10-22-2012
    Location
    hyd
    MS-Off Ver
    2010
    Posts
    159

    Cool Re: Case Statement with Multiple Conditions

    Hi,

    You can try:

    Private Sub CommandButton1_Click()
    
    If Worksheets(x).Visible Or Worksheets(y).Visible Then
    'do 1
    End If
    
    If Worksheets(x).Visible And Worksheets(y).Visible Then
    'do 2
    End If
    
    If Worksheets(x).Visible Then
    'do 3
    End If
    
    If Worksheets(y).Visible Then
    'do 3
    End If
    
    End Sub
    Regards,
    PRB.

    Right time to become Expert..

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Case Statement with Multiple Conditions

    Sub test()
        Dim x As Worksheet, y As Worksheet
        Set x = Sheets("sheet1")
        Set y = Sheets("sheet2")
        Select Case True
            Case x.Visible <> -1 And y.Visible <> -1
                ' do 1
            Case x.Visible = -1 And y.Visible = -1
                ' do 2
            Case x.Visible = -1
                ' do 3
            Case y.Visible = -1
                ' do 4
        End Select
    End Sub

  5. #5
    Registered User
    Join Date
    08-10-2012
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Case Statement with Multiple Conditions

    Thanks to all that replied, I tried jindon and TMS's offering and they both worked as asked but it appears that I didn't ask the question correctly. I didn't realize that not visible (hidden) and non existent weren't the same thing. If the sheets are simply hidden all works well but if the sheets don't exists I get debugger. I am actually copying sheets form another workbook so sometimes one sheet exists and not the other or visa-versa, sometimes both exist and sometimes neither exists. Can you please help me restructure the code base on the existence of the sheet rather than visibility.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Case Statement with Multiple Conditions

    In that case
    Sub test()
        Dim x As Worksheet, y As Worksheet
        On Error Resume Next
        Set x = Sheets("sheet1")
        Set y = Sheets("sheet2")
        On Error GoTo 0
        Select Case True
            Case x Is Nothing And y Is Nothing
                ' do 1
            Case Not x Is Nothing And Not y Is Nothing
                ' do 2
            Case Not x Is Nothing
                ' do 3
            Case Not y Is Nothing
                ' do 4
        End Select
    End Sub

  7. #7
    Registered User
    Join Date
    08-10-2012
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Case Statement with Multiple Conditions

    That did the trick jindon, many thanks!

+ 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. [SOLVED] is if-then or select case statement with multiple arguments possible?
    By kjy1989 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2013, 02:54 AM
  2. Using Case Select On Range With Multiple Conditions?
    By alulla in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 07-03-2013, 02:50 PM
  3. Replies: 4
    Last Post: 05-06-2013, 11:06 AM
  4. Select case instead of if statement formula in multiple cells (macro)
    By city in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-25-2012, 07:56 AM
  5. Case Statement using a Range of Conditions
    By Andrew Bird via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2005, 12:06 PM

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