Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sub Workbook open() & Worksheet_SelectionChange (Office 97 SR2)

    I have questions about the Private Sub Workbook_Open() and the
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Subroutines.
    1) If I use Private Sub Workbook_Open() to automatically open a second file, say Filename2.XLS, can I also have a similar command built into Private Sub Workbook_Open() of Filename2.XLS that will automatically open Filename3.XLS or is it better to place both Workbooks.Open statements in the VBA for the first file (the one I open manually); also, can I place other code in Private Sub Workbook_Open(), like, to run other code like refreshing a query or copying from one WS to another?
    2) If I have code in the Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) module that Activates another WS and then returns to the first WS, won't that set up an infinite loop (I mean, when focus returns to the WS with the code entered into its Sub Worksheet_SelectionChange Module, won't it re-execute the code that changes focus back to the second sheet)? Also, suppose I call another macro from this module (one that gives focus to another WS), what happens when control returns to the code in the Worksheet_SelectionChange Module?
    Thanks for sharing information re this.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Sub Workbook open() & Worksheet_SelectionChange (Office 97 SR2)

    Hi epic60sman

    1) No you should not do that, because the stack may over flow. I suggest you open each workbook separaetly. In addition to that, your code should be streamlined so that you can tell what is being executed.

    2) AFAIK Worksheet_SelectionChange will only fire when the selection changes on the worksheet that contains the code. I don't think you will run into a loop. So as you are on the other sheet the code is inactive because the sheet is not the active one.

    But again I have to be your friend and ask you why are you using these events to run code. usually you want to place code there that reacts to the events they handle, such as when a user changes a selection and you don't want them to do so, you tell them and return them to where they should be.

    Let me know if I helped.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sub Workbook open() & Worksheet_SelectionChange (Office 97 SR2)

    Re your 2nd question, a loop should only happen if there is a -change- to the specific Worksheet_SelectionChange sheet when focus returns to it. Setting focus on it without making any change shouldn't cause a problem. I have gotten into a loop by using Workbook_SheetSelectionChange to delete cells on the Workbook_SheetSelectionChange Target range, which infinitely recursed. I wasn't moving between WB's however.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Workbook open() & Worksheet_SelectionChange (Office 97 SR2)

    1- There is one problem with having code in the Workbook_Open event of a workbook that is opened from code. If I remember correctly, the open event does not fire if the workbook is opened from VBA code. There is a way to get it to fire, but I don't remember offhand how to do it.

    2- First, why are you changing the focus to another sheet and then back. It is almost never necessary to change the focus in code, and it is always much slower to do so. So, if you aren't trying to let the user see something, then DON'T change the focus unless it is required. That said, yes you could end up in an infinite loop. The way to prevent that is to use Application.EnableEvents = False before changing the focus, and Application.EnableEvents = True after.
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Workbook open() & Worksheet_SelectionChange (Office 97 SR2)

    Hi Wassim:
    So, this would be the way to open 2 files automatically upon opening of the 3rd (the one with the code):
    Private Sub Workbook_Open()
    Dim oWB As Workbook
    On Error Resume Next
    Set oWB = Workbooks("AbstractorsByMOD.XLS")
    On Error GoTo 0
    If oWB Is Nothing Then
    Workbooks.Open FileName:="CDAC_OADATA1ShareIQCTRENDSVarByUserAbst ractorsByMOD.xls"
    Else
    End If
    Windows("HNSAll_2001.xls").Activate
    Worksheets("Main2").Activate
    Worksheets("Main2").Range("A1:A1").Select
    End Sub
    Re your advice concerning use of Worksheet_SelectionChange, that is exactly why I was using it but I wanted to be sure because I had used it in the past and gotten into trouble.
    Thanks for your quick response (by the way, what does AFAIK stand for).

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Workbook open() & Worksheet_SelectionChange (Office 97 SR2)

    Sorry, Legare. I did not mean that Iwas changing the focus using VBA code. But this prompts me to be a bit more specific. Let me pose 2 ways of handling something to see what would happen with each. Suppose my code(in the Worksheet_SelectionChange Module) checks to see if 2 cells are equal and if not it activates another WS and refreshes a query and then reactivates the original Worksheet. Then, same situation but, instead of activating the other WS directly, I do it by calling another Sub. In the later case, control automatically passes back to original WS. Are these equally as good ways of handling this situation?
    Also, are you saying that I should use Application.EnableEvents = False before 'activating the 2nd WS' or before calling a sub or rather after running the code on the other WS or after running the sub that is called? Or, am I hopelessly confused?

  7. #7
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Sub Workbook open() & Worksheet_SelectionChange (Office 97 SR2)

    epic60sman

    Yes this would be one way of doing it. I would also suggest that you put this code in a different sub altogether and call it from the Workbook_Open(). This is due to the fact that when we had Excel 5.x way back when and we had the Auto_Open sub, it was advised to have the Auto_Open be as short as possible. This I guess has stuck with me, I am sure it is all OK now, but I am not one who takes chances... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    As for the AFAIK its an abbreviation of/for "As Far As I Know..."

    Wassim
    PS You don't need to select the range to work with it... Selecting will slow your code. <img src=/S/snore.gif border=0 alt=snore width=32 height=15>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Workbook open() & Worksheet_SelectionChange (Office 97 SR2)

    When you activate a different workbook, you change the focus to that workbook. So, I was equating the two.

    There is no need to activate a workbook to check to see if two cells are equal. It is much slower to do so. I havent used queries, so I can't say if it is or is not necessary to activate the workbook and sheet to refresh the query. If it is not necessary, it would be better to not do so.

    You would need to use a Application.EnableEvents = False before any statement(s) that would trigger the event you don't want triggered, and an Application.EnableEvents = True immediately after the statement(s). You should not leave events disabled any longer than absolutely necessary, you might miss some other event (like a recalculate) that is important.
    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Workbook open() & Worksheet_SelectionChange (Office 97 SR2)

    Hi Legare,

    <<There is one problem with having code in the Workbook_Open event of a workbook that is opened from code. If I remember correctly, the open event does not fire if the workbook is opened from VBA code. There is a way to get it to fire, but I don't remember offhand how to do it.>>

    No, that was the case with the xl5 Auto_Open sub, you needed to explicitly run that whan opening a file from VBA using the RunAutoMacros method. Workbook_Open runs unless specified otherwise.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Workbook open() & Worksheet_SelectionChange (Office 97 SR2)

    Thanks. I knew I remembered something.
    Legare Coleman

Posting Permissions

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