Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Tweaking existing VBA code...

  1. #1
    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

    ...however, I don't know how to write VBA code. What I'm using, I got help with from here.

    (I had all of this typed already, but apparently my login expired...grr...anyway...)

    The code I'm currently using opens 2 separate workbooks and prompts for the others to open.

    I usually start entering information in a certain workbook every time. How can I set it to switch to this one workbook after it opens the one I select?

    Thanks!
    Kristy

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Put the following code in the workbook that you don't want active:

    Private Sub Workbook_Open()
    Workbooks("BookToActivate.xls").Activate
    End Sub
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    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, Al, but I have a little question about that.

    After the macro runs, the workbook that I selected to open is the one that stays on top. This is not always the same workbook. Would I have to put that in every workbook that I might have to open?

    I honestly was assuming there would be some kind of command somewhere to look at what files are open and bring this certain one to the front, as it were.

    Hitting ctrl+tab to flip through them isn't really a big pain or anything, I was just hoping there was a way around it.
    Kristy

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about the following code:

    For Each w In Workbooks
    If w.Name = "NameOfBookToActivate.xls" Then
    w.Activate
    End If
    Next w
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    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

    Would that one go into the existing code?

    (Just checking, but I can't try it now until Monday)
    Kristy

  6. #6

    Join Date
    Mar 2002
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    "There are two means of adding to the miseries of life: music and cats." - C. O. Jones

  7. #7
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Post your code and I will integrate it.

  8. #8
    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

    Al,

    This is what I currently have:

    Private Sub Workbook_Open()
    On Error GoTo myErr
    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)
    ThisWorkbook.Close False

    Next

    End
    myErr:
    ThisWorkbook.Close False
    End Sub
    I would like to have the Main Directory on top instead of the one I select.

    Thanks for all of your help,

    _________________
    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 06:43 ]

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

    Default

    Hi Kristy

    Can't you just put:

    Workbooks("BookIWant.xls").Activate

    as the last line of your "code" ?



  10. #10
    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

    On 2002-03-25 07:05, Dave Hawley wrote:
    Hi Kristy

    Can't you just put:

    Workbooks("BookIWant.xls").Activate

    as the last line of your "code" ?
    There are only 2 problems with that:

    1) Judging by the tone, you seem to assume that I know how to use VBA...which I don't (unfortunately). That's why I'm posting for help.

    2) I went ahead and tried your suggestion in several different places of the code...I couldn't get it to work. However, I'm assuming that it's just me that's the problem.

    Thanks,
    Kristy

Some videos you may like

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
  •