0

I am trying to use VBA to create the same action you would do if you had an Excel spreadsheet open with 3 sheet and you want to toggle between sheets exactly like when you click on the sheet tab at the bottom of the workbook (i.e. Toggle between Sheet1, Sheet2, Sheet3 by clicking on the sheet tabs)

I've tried some thing:

book.Worksheets(1).Activate = True

book.Worksheets(1).Activate

sheet.Visible = True

But nothing seems to work. I'm not sure Activate is what I want. I think that just makes the sheet active, but not visible. Visible seems to do nothing. How can this be accomplished?

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
Mike
  • 4,099
  • 17
  • 61
  • 83
  • `book.Worksheets(1).Select` ? – chancea Dec 12 '14 at 21:08
  • Never mind my answer, Fridays my brain is slow. `.Activate` is the code you are looking for. Something else must be wrong with your code. Read http://stackoverflow.com/questions/15919455/what-is-the-difference-between-sheets-select-and-sheets-activate – chancea Dec 12 '14 at 21:21
  • 3
    if it's in the same workbook, just try `ThisWorkbook.Sheets(1).Activate`, otherwise, make sure `book` is correct and use `book.Sheets(1).Activate` – Michael Dec 12 '14 at 21:38
  • Also, are you trying to use this in a UserForm? Do you want 1 button that when clicked will "loop" to the next sheet and then at the end, start over or do you want 3 buttons, one for each sheet? – Michael Dec 12 '14 at 21:57
  • Hey Michael. Thanks for the question. Basically, when my script ends, it saves the workbook. When I go to open the workbook, it always opens displaying last page it created. I wanted the spreadsheet to open with the first sheet displayed. That's all I was really trying to accomplish. – Mike Dec 13 '14 at 00:08

2 Answers2

1
book.Worksheets(1).Activate

Is designed to perform the action you want "clicking on a tab"

You can also try

book.Worksheets(1).Select

Which can be used to select multiple tabs (see Have a look at What is the difference between Sheets.Select and Sheets.Activate?)

I am guessing something is not right either with your workbook reference or something else in your code. If you make a new excel file and just try Sheets("Sheet2").Activate it will select that tab.

Community
  • 1
  • 1
chancea
  • 5,858
  • 3
  • 29
  • 39
0

thanks for the feedback. I have been attempting to use com objects in python. they usually interop with each other quite well, but this is one thing that doesn't seem to work. I've tried all of your suggestions with no results.

I managed to find a workaround by just moving the sheets around using: 

sheet.Move(Before=book.Worksheets(1))

For some reason, when the sheet is moved, it also activates it which is my desired end result. It works for the scenario I am scripting, but may not for other people trying to do something similar.

Mike
  • 4,099
  • 17
  • 61
  • 83