Tweaking existing VBA code... - Page 2
Tweaking existing VBA code...
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 25

Thread: Tweaking existing VBA code...

  1. #11
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi Kristy

    RE: Judging by the tone

    No no, you have me wrong, there was no tone intented. I was just be earnest.

    As long as the Workbook is open, the code I posted should work if placed as the last line in your code. You should only have to change the name to suit.

    If you could paste your code here myself, or many others will help.



  2. #12
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I wasn't meaning anything bad by the "judging by the tone" remark, sorry .

    As for the code, I posted it this morning (see the first page of this thread).

    As the code is right now, it is set to close the workbook with the code in it after it is done running everything (I think that may be some of the problem). I tried putting it right before the thing for closing the workbook, at the very end, etc. I couldn't get it to go.

    *sigh* Stupid vba. (Yep...must be the vba's fault )
    Kristy

  3. #13
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Kristy I didn't realise you were doing this via the Workbook_open (guess I did assume )).

    Ok, completely untested, but it should help

    Option Explicit
    Dim bActivateIt As Boolean
    Private Sub Workbook_Deactivate()
    On Error Resume Next
    If bActivateIt = True Then
    Workbooks("MyBook.xls").Activate
    End Sub
    End Sub

    Private Sub Workbook_Open()
    On Error GoTo myErr
    bActivateIt = False
    ChDir "ECR"
    Workbooks.Open "UPG List Revised.xls"
    Workbooks.Open "Main Directory.xls"
    Which = Application.GetOpenFilename(MultiSelect:=True)
    'Which now contains an array of file names which you can enumerate through
    For I = 1 To UBound(Which)
    Workbooks.Open Which(I)
    bActivateIt = True

    'Not sure why you close ThisWorkbook on the _
    first loop? It means it will onl loop once.
    ThisWorkbook.Close False

    Next

    End
    myErr:

    ThisWorkbook.Close False
    End Sub


  4. #14
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    I couldn't get that to work either. Ugh.
    It gives me the following: "Compile error: variable not defined" and highlights the line "Private_Sub Workbook_Open()" in yellow, and selects the "Which =" in the line that lets me select from the open dialog box.

    Then, if I go to close excel, it brings up another error: "Comiple error: Block If without End If" and highlights "Private_Sub Workbook_Deactivate()" in yellow, and selects the first "End Sub" from what you added to the beginning of the code.

    As for closing the workbook, I set a blank workbook to run the code, and I have it close after it opens all the necessary workbooks so that it will not be in the way when switching between workbooks.

    All the code you see me post came from people here. So I have no clue about it running on the first loop or anything.

    _________________
    Kristy

    "There are two means of refuge from the miseries of life: music and cats." - Albert Schweitzer

    [ This Message was edited by: Von Pookie on 2002-03-25 12:43 ]

  5. #15
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Kristy

    The code must be placed in the Private Module of the Workbook that is to run the code upon opening. The easiest way to get there is to open the Workbook, right click on the Excel icon, top left next to "File" and select "View Code". Now delete any old code and paste in this


    Dim bActivateIt As Boolean
    Private Sub Workbook_Deactivate()
    On Error Resume Next

    If bActivateIt = True Then
    Workbooks("MyBook.xls").Activate
    End If

    End Sub

    Private Sub Workbook_Open()
    Dim Which

    On Error GoTo myErr
    bActivateIt = False
    ChDir "ECR"

    Workbooks.Open "UPG List Revised.xls"
    Workbooks.Open "Main Directory.xls"

    Which = Application.GetOpenFilename(MultiSelect:=True)
    'Which now contains an array of file names which you can enumerate through
    For I = 1 To UBound(Which)
    Workbooks.Open Which(I)
    Next

    myErr:
    bActivateIt = True
    ThisWorkbook.Close False
    End Sub

    Change "MyBook.xls" to the name of the Workbook you want to be activated when this one closes.

    If you have any other macros called "Private Sub Workbook_Open()" in any Standard modules you must also delete them.

    Save and close your Workbook, then re-open it.




  6. #16
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    Thanks for your help, but it's not working. I used an entirely new workbok to test it on. It doesn't open any of the files, bring up the open box or anything. It does close the file, though.

    Another thing is this: Just to try it, I copied my original code that I posted here, did the delete code thing from that like you mentioned, then pasted it and tried that again. It did the same thing.

    Why would the original code start acting like that as well?
    Kristy

  7. #17
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Kristy


    The errpr most likely lies in your original code, remove or comment out the: On Error GoTo myErr line. This will force a run time error, click on Debug and it will take you straight there.



  8. #18
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Dave,

    I found that error: a simple oversight on my part (I missed adding a full directory path back in ).

    It is opening everything again like it was, however it is still not bringing the sheet to the front, and staying on the last one opened.

    I'm beginning to think this just isn't going to work. *sigh*
    Kristy

  9. #19
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Kristy

    It will work sooner or later!

    Remove the "On error resume next" from the Workbook_Deactivate code and see what the problem is.






  10. #20
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Good morning, Dave! (at least it is for you. It's about 6:30pm here...and still Tuesday).

    I had this typed out all nice and neat once already, but apparently hit the wrong button somewhere.

    Anyway, the line that seems to be disliked this time is: Workbooks("Main Directory.xls").Activate in the Workbook_Deactivate part of the code.

    Thanks for all of your help. Who would have thought we would have to go through all of this trouble just to help me somewhat "automate" a stupid little task.

    Thanks again,
    Kristy

User Tag List

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
  •  

 

 
DMCA.com