Make Second Sheet from left Activesheet (ordinal position #2)

[manthony]

Hi, I do not know if this is super easy or challenging, but I am trying in set my active worksheet to be the second sheet from the left.

When my workbook opens, a form prompts you for a username/password which allows certain sheets to become visible depending on what you put in.

I want to call a macro to run on the second visible sheet from the left. I am having trouble coming up with the correct code to make the active sheet the second sheet from the left.

A potential issue is that the name of the second sheet from the left changes depending upon the username/password entered into the form. Any ideas, I am super appreciative for the help.

 

[Rick Rothstein]

Try something like this...

  Dim X As Long, Cnt As Long
  For X = 1 To Sheets.Count
    If Cnt = 2 Then
      Sheets(X).Activate
      Exit For
    ElseIf Sheets(X).Visible Then
      Cnt = Cnt + 1
    End If
  Next

 

[Jaafar Tribak]

I am not sure I understand .. Are you referring to 2 sheets to the left of the currently active worksheet ?
What if the activesheet is the first worksheet and there are no sheets to its left ?

 

[manthony]

Hi, thank you for the help. The code works, but does not have the desired result. My apologies for not being clear. My first sheet is a vacation request form (always visible). The second sheet is the amount of vacation a person has (this sheet goes from very hidden to visible). I have about 20 different very hidden sheets the second sheet could be.

For example, when you open the workbook and enter your login credentials, depending on the credentials entered a second premade sheet will appear. For example if Joe logs in then the second sheet unhidden/visible will be a premade sheet titled Joe or if Steve logs in then the second sheet unhidden/visible will be a premade sheet titled Steve and then run the rest of a macro behind it. I really just need to tell the macro to run on second tab from the left (the Joe or the Steve sheet, depending on who logs in). Any ideas, and thank you again for the help.

 

[Rick Rothstein]

I do not understand exactly what my code is not doing for you. Once the second visible worksheet has been activated, all range references without a specific sheet reference attached to it should be using that second visible worksheet automatically. Are you saying that is not happening?

 

[Fluff]

How about

Sheets(UserName).Activate

Where Username comes from the login

 

[manthony]

Thank you all, Fluff and Rick I greatly appreciate you help. Rick your code works perfectly. The issue turned out to be part of my later macro throwing everything on back onto the first sheet. Thank you both for the help. : )