Results 1 to 4 of 4

Thread: Activate a sheet without using "Activate"
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2016
    Posts
    109
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Activate a sheet without using "Activate"

    I have read that it is best not to use .Activate whenever possible. But I can't figure out how to do something without it.

    In my workbook, one thing it will do is copy sheets into a new workbook. Once it copies the sheets, I want the code to select the first workbook in the sheet, so that when the user opens that workbook later, the first one will be the one it opens on. I can do that with this code:

    Code:
    myWB.Sheets(1).Activate
    After this, my code closes the workbook (myWB) and then I activate the workbook with the code by:

    Code:
    ThisWorkbook.Activate
    Is there a way to do the same thing without using .Activate?

    Another reason that I want to avoid it is that even though I have ScreenUpdating set to False, when the Activate lines execute, I see the screen flash for a second.

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,839
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Activate a sheet without using "Activate"

    Code:
    Application.Goto myWB.Sheets(1).Cells(1, 1)
    But you will still get some screen flash.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular
    Join Date
    Aug 2016
    Posts
    109
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activate a sheet without using "Activate"

    Thanks. Is there no way to avoid the screen flash? I don't understand what is causing it.

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,143
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Activate a sheet without using "Activate"

    Quote Originally Posted by mcomp72 View Post
    Thanks. Is there no way to avoid the screen flash? I don't understand what is causing it.
    Don't notice any flash when activating one sheet. You could try activating that sheet when you open the workbook with a workbook_open event like this:
    Code:
    Private Sub Workbook_Open()
    Sheets(1).Activate
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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