1

Hi I tried to modify the following two lines of code by adding an if statement, but it didn't work. Does anyone know why?

Workbooks(wkbk_Record).Activate
Workbooks(wkbk_Record).Sheets("Tab1").Select

What's wrong with the following? Thank you

If SheetExists("Tab1", Workbooks(wkbk_Record)) Then    
    Workbooks(wkbk_Record).Activate
    Workbooks(wkbk_Record).Sheets("Tab1").Select    
Else    
    Workbooks(wkbk_Record).Activate
    Workbooks(wkbk_Record).Sheets("Tab2").Select
Vityata
  • 42,633
  • 8
  • 55
  • 100
BurtBee
  • 19
  • 1
  • 4

1 Answers1

2

Considering that you take the SheetExists from here - Test or check if sheet exists this is something that works:

Sub TestMe()

    If SheetExists("Tab1") Then
        Sheets("Tab1").Select
    ElseIf SheetExists("Tab2") Then
        Sheets("Tab2").Select
    Else
        MsgBox "No Sheet"
    End If

End Sub

 Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean

     Dim sht As Worksheet

     If wb Is Nothing Then Set wb = ThisWorkbook
     On Error Resume Next
     Set sht = wb.Sheets(shtName)
     On Error GoTo 0
     SheetExists = Not sht Is Nothing

 End Function

Or if you wanna see your colleagues get crazy, write it like this:

Sub ActivateTab2OrTab1()

    On Error Resume Next
    Sheets("Tab2").Select
    Sheets("Tab1").Select

End Sub
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 2
    OP - This is a good opportunity to learn how to set a Worksheet variable, instead of `Select`ing the worksheet. See [how to avoid `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/). – BruceWayne Jun 19 '18 at 14:54
  • Thank you for the comment. So I can use "Sub" under another "Sub" that already exists? Thanks ! – BurtBee Jun 19 '18 at 15:15
  • @BurtBee, yes, *If* you have ended the previous `Sub`routine with `End Sub` – Taylor Alex Raine Jun 19 '18 at 15:16
  • (oh ok, this is embedded in a really long sub, so I probably can't use it on its own with another sub) – BurtBee Jun 19 '18 at 15:18
  • Or could you explain why my codes don't work? I thought I was just modifying it little bit. Thanks ! – BurtBee Jun 19 '18 at 15:18
  • @BurtBee - no idea what does not work. Just from the first look - you are missing `Sub Something` , `End Sub` and `End If`. – Vityata Jun 19 '18 at 15:25
  • @BurtBee - you are welcome. If my asnwer has solved your query, you may consider [marking it as accepted](https://stackoverflow.com/help/someone-answers) – Vityata Jun 19 '18 at 15:39