Results 1 to 7 of 7
  1. #1
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Double WindowDeactivate (2002)

    I have a multi-sheet workbook that imports sales order details, and presents it in various formats including links to other workbooks.
    Some of these links are converted into hyperlinks, so that a user can simply click on it to open the relevant workbook. When they do, certain bits of information are copied from the originating workbook into the newly opened workbook.
    The worksheet these hyperlinks appear on is "JS", and the macro that does the copy and paste is "GetWS", and the process that triggers this is:

    <code>Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    If ActiveSheet.Name = "JS" Then Call GetWS
    End Sub</code>

    So far so good. I can click on the hyperlink, the linked-to workbook is opened and GetWS runs.

    However, Excel is seeing the WindowDeactivate twice. If I step through it all, I notice that until GetWS has completed, the newly opened workbook does not appear on the taskbar, and once it does, Excel seems to see this as another chance to run WindowDeactivate, and thus GetWS again.

    I run a check when GetWS starts to see if the new workbook has opened and if so exit, so this is not much more than an annoyance, but I'd be interested to know why.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Double WindowDeactivate (2002)

    I don't know why, but events often occur more than once. Sometimes you can suppress repeats by setting Application.EnableEvents = False at the beginning of an event procedure and ... = True at the end, but that does not help here. You can test in GetWS, or you could use a static variable in the event procedure:

    Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    Static blnPreventRun As Boolean
    If blnPreventRun = False Then
    If ActiveSheet.Name = "JS" Then Call GetWS
    End If
    blnPreventRun = Not blnPreventRun
    End Sub

  3. #3
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Double WindowDeactivate (2002)

    Thanks, I'll give that a think, but whilst faffing about I have come across an advantage in it running twice.

    For reasons of clarity, I omitted to include in the above WindowDeactivate code the line
    <code> Application.CommandBars("Web").Visible = False</code>

    The range of workbooks that can be opened by the hyperlink go back over many years, and only the more recent ones include code to turn off the Web toolbar on opening. What I am finding is that the new workbook opens without the Web toolbar, but when 'control' returns to WindowDeactivate, the damned thing has appeared! (The second run of WindowDeactivate naturally turns it off.)

    Is there some way to banish the Web toolbar from Excel and Word for ever? Or even longer?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Double WindowDeactivate (2002)

    Try <code>Application.CommandBars("Web").Enabled = False</code>
    It seems to persist between Excel sessions.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Double WindowDeactivate (2002)

    The instruction
    <code>
    Application.CommandBars("Web").Enabled = False
    </code>
    should banish the Web toolbar forever. It needs to be run once in each Office application.

  6. #6
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Double WindowDeactivate (2002)

    I'm now getting Double Forum.Response = True
    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Many thanks to you both.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Double WindowDeactivate (2002)

    Garlic and a silver bullet should take care of that! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

Posting Permissions

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