Results 1 to 6 of 6

Thread: Thisworkbook.activate does not work
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2005
    Posts
    400
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Thisworkbook.activate does not work

    Hi and thanks in advance!

    I have the following code:
    Code:
    Sub AAA()
    On Error Resume Next:                       Application.ScreenUpdating = True
                                                'ActiveWindow.Visible = True
      Dim wActWkb As String:                    wActWkb = ActiveWorkbook.Name
      Dim wThisWkb As String:                   wThisWkb = ThisWorkbook.Name
                                                'Application.ScreenUpdating = True
                                                '  Workbooks(wThisWkb).Activate
                                                '  Windows(wThisWkb).Activate
                                                '  ThisWorkbook.Activate
                                                'Application.ScreenUpdating = False
        Application.Wait Now() + TimeValue("00:00:01")
        Debug.Print wThisWkb, ActiveWorkbook.Name, WKBnew.Name, ActiveWorkbook.Name
        'I get:
        'AAA.xlsm            AAA.xlsm            BBB.xlsm            AAA.xlsm
        If wThisWkb <> ActiveWorkbook.Name Or WKBnew.Name = ActiveWorkbook.Name Then
          ThisWorkbook.Activate
        End If
          X = X
        Debug.Print wThisWkb, ActiveWorkbook.Name, WKBnew.Name, ActiveWorkbook.Name   'XXXXXXX
        'I get:
        'AAA.xlsm            AAA.xlsm            BBB.xlsm            AAA.xlsm
        'BUT If I put a Stop within the row: 'XXXXXXX and I press F8 step byt Step I get
        'AAA.xlsm            BBB.xlsm            BBB.xlsm            BBB.xlsm
    
    What's going ON???
    I am trying to Activate the book ; thisworkbook, but any of the following codes work:
    Code:
    Sub AAA()
      Dim wActWkb As String:                    wActWkb = ActiveWorkbook.Name
      Dim wThisWkb As String:                   wThisWkb = ThisWorkbook.Name                                          
                                                Application.ScreenUpdating = True
                                                  Workbooks(wThisWkb).Activate
                                                  Windows(wThisWkb).Activate
                                                  ThisWorkbook.Activate
    end sub
    No matter If I use:
    Code:
    Sub AAA()
      Dim ActWkb As Workbook:                    set ActWkb = ActiveWorkbook.Name
      Dim ThisWkb As Workbook:                   set ThisWkb = ThisWorkbook.Name                                          
                                                Application.ScreenUpdating = True
                                                  ThisWkb.Activate
                                                  Windows(ThisWkb.name).Activate
                                                  Windows(ThisWkb.fullname).Activate
                                                  ThisWorkbook.Activate
    
    end sub
    I only get the desired action when using F8 inside my VBA code

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Thisworkbook.activate does not work

    Sorry, please can you be more specific as to the line of code that is the problem?
    My favorite Excel Add-in:= Nutilities

  3. #3
    Board Regular
    Join Date
    Jan 2014
    Location
    Dublin, Ireland
    Posts
    1,421
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Thisworkbook.activate does not work

    As above what =ODIN= said, but also what is WKBnew supposed to be as its not defined or set to anything?
    Learn something new everyday.

    be sure to use code tags

    Code:
    [ code ]
    [ / code ]
    ' no spaces

  4. #4
    Board Regular
    Join Date
    Mar 2005
    Posts
    400
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Thisworkbook.activate does not work

    Quote Originally Posted by =ODIN= View Post
    Sorry, please can you be more specific as to the line of code that is the problem?
    I have two books open
    the Activeworkbook and thisworkbook

    but I only can activate thisworkbook using F8 within the VBA window

    I know how to use Scrreupdating, application.wait...

    but I can't get thisworkbook.activate and do not know why

    you can use any of the macros attached,

    what's going on is explained in the comments lines see 'I get:

    Thanks!
    Last edited by drom; Feb 4th, 2019 at 08:57 AM.

  5. #5
    Board Regular
    Join Date
    Mar 2005
    Posts
    400
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Thisworkbook.activate does not work

    Hi sorry,
    I am using 2 different books, WKBnew = ActWKB and ThisWorkbook , but for this exanple I was modifying the code, to make it easier

    wActWkb or wWKBnew can be the same or

    ActWkb =
    WKBnew
    Last edited by drom; Feb 4th, 2019 at 09:01 AM.

  6. #6
    Board Regular
    Join Date
    Dec 2009
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Thisworkbook.activate does not work

    Your code is written in a very confusing way, but

    try maybe this i what you mean.
    Code:
     Dim ActWkb As Workbook:                    set ActWkb = ActiveWorkbook
      Dim ThisWkb As Workbook:                   set ThisWkb = ThisWorkbook
    My favorite Excel Add-in:= Nutilities

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
  •