Trying to add message box to macro myself...

[Von Pookie]

...and it is definately not working (of course, actually knowing what I was doing would probably help in this situation, but anyway).

I am trying to add to a current macro to get a message box to pop up before anything else happens.

If "Yes" is clicked, I want it to continue running the macro as it is.

If "No" is clicked, I want to only open one file and the open dialog box.

I've tried messing around with the code, but can't get a message box to show up. Several things I've tried have run the macro like I want it to when clicking yes, but no popup.

From what little I could find to help me on this here and the little prompts that come up in the editor, I came up with this:

 Private Sub MessageBox()
    If MsgBox("What would you like to do?", vbYesNoCancel + vbQuestion, "ECR Status") = vbYes Then
            Call Workbook_Open
        Else
            Workbooks.Open "C:Main Directory.xls"
            Application.GetOpenFilename (MultiSelect = True)
            ThisWorkbook.Close False
    End If
End Sub
            
Private Sub Workbook_Open()
On Error GoTo myErr
ChDir "C:ECR"
Workbooks.Open "C:Main Directory.xls"
Application.Wait Now + TimeValue("00:00:01")
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
Application.Wait Now + TimeValue("00:00:01")
Workbooks.Open "C:UPG List Revised.xls"

For Each w In Workbooks
If w.Name = "Main Directory.xls" Then
w.Activate
End If
Next w
ThisWorkbook.Close False


End
myErr:
Workbooks.Open "C:UPG List Revised.xls"

ThisWorkbook.Close False
End Sub

Please keep in mind that this is just trial and error, and no, I can't check the vb help; it wasn't installed.

So...any help as to what I did wrong?

Thanks a lot,

[Mark O'Brien]

You're not calling the MessageBox routine from your "Open" event.

Try this:

            

Private Sub Workbook_Open()



MessageBox

On Error GoTo myErr

'the rest of your code

[Von Pookie]

Thanks, Mark.

The message box comes up now, but it doesn't work correctly.

If I click "Yes," nothing happens, and the message box is still there. I did a 'step into' in the vb editor, and I appear to have put myself into a wonderful loop . It goes through the code until it hits the words "MessageBox" then it hops right back up there and repeats.

If I click "No" or "Cancel," it opens the Main Directory.xls workbook fine, but after that I get:

Runtime error 1004: method 'GetOpenFilename' of object '_Application' failed.

I'm missing something, I either don't know it, or I'm just not seeing it...

Thanks,

[Mark O'Brien]

To be honest, I would get rid of all of that code from the workbook_open section and use something like this (note this still has your error, but is the first step to a solution):

 Private Sub MessageBox()

    If MsgBox("What would you like to do?", vbYesNoCancel + vbQuestion, "ECR Status") = vbYes Then

      On Error GoTo myErr

        ChDir "C:\ECR\"

        Workbooks.Open "C:\Main Directory.xls"

        Application.Wait Now + TimeValue("00:00:01")

        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

        Application.Wait Now + TimeValue("00:00:01")

        Workbooks.Open "C:\UPG List Revised.xls"



        For Each w In Workbooks

          If w.Name = "Main Directory.xls" Then

            w.Activate

          End If

        Next w

        ThisWorkbook.Close False

        

        Else

            Workbooks.Open "C:Main Directory.xls"

            Application.GetOpenFilename (MultiSelect = True)

            ThisWorkbook.Close False

    End If

 

Exit Sub

        

myErr:

         Workbooks.Open "C:\UPG List Revised.xls"



         ThisWorkbook.Close False

End Sub

            

Private Sub Workbook_Open()

 MessageBox

End Sub

[Mark O'Brien]

could you describe what you want this code to do? Tell me what the name of the workbook that it's in as well please.

I'm having a hard time looking at the code and I'm not surprised your getting into a vicious loop. (I hate coding using events with a passion, I try to keep it as simple as possible if I have to)

[Von Pookie]

Thanks, Mark. I'll try to explain it.

All of this code is in a blank workbook named ECR. When I get an ECR form, and I need to either "open" it or "close" it.

When I get a form to "open," I need to use at least 3 different workbooks and enter the information from the form.

Two of the workbooks are "permanent," whereas I can just call them up by their name, but one workbook can change depending on what letter is on the form (A, B, C, etc.) There is a workbook for each letter.

The code currently opens the 2 workbooks, then brings up the open dialog box so I can choose the other workbook(s) I may need.

When I get a form to "close," I only need 2 workbooks; one of the permanent workbooks, and I would need to choose the other workbook(s) again.

What I am trying to do is get the code to bring up a message box before it does anything else asking if I want to "open" or "close" a form.

If I choose "yes," I want it to run the code like it currently does (open the 2, bring up the open dialog to choose the 3rd, switch to a certain book, then close the workbook that the code is running in).

If I choose "no," I want it to just open one of those workbooks, bring up the open dialog to open the other, then close the workbook that the code is running in.

I really hope that makes some sort of sense. If you need anything else explained, let me know.

This is probably going to sound stupid, but it was an idea I just had. Anyway, would it be possible to just put "if vbYes Then continue" or something like that to just have it run the code like it was?

Thanks again,

_________________
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-04-25 06:39 ]

[Von Pookie]

w00t! I got it to work! I got it to work!

I'm sure there may be a different way of doing it, but I got it to work, darnit!

Mark, your suggestion for changing the Workbook_Open thing worked. Then to get the "else" stuff to do what I wanted it to, I copied/pasted from the code I already had and eventually got it (though I wish there was a choice for buttons labeled "Open" and "Close". If there's a way to change them so they do, I couldn't find it)!

This is what I've ended up with:

Private Sub MessageBox()

    If MsgBox("To open an ECR, choose Yes. To close an ECR, choose No", vbYesNo + vbQuestion, "ECR Status") = vbYes Then

      

      On Error GoTo myErr

        ChDir "C:DesktopECR"

        Workbooks.Open "C:DesktopECRMain Directory.xls"

        Application.Wait Now + TimeValue("00:00:01")

        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

        Application.Wait Now + TimeValue("00:00:01")

        Workbooks.Open "C:DesktopECRUPGUPG List Revised.xls"



        For Each w In Workbooks

          If w.Name = "Main Directory.xls" Then

            w.Activate

          End If

        Next w

        ThisWorkbook.Close False

        

        Else

            On Error GoTo myErr

            ChDir "C:DesktopECR"

            Workbooks.Open "C:DesktopECRMain 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)

         For Each w In Workbooks

          If w.Name = "Main Directory.xls" Then

            w.Activate

          End If

        Next w

        ThisWorkbook.Close False

        

        Next

        End If

Exit Sub

        

myErr:

         Workbooks.Open "C:DesktopECRUPGUPG List Revised.xls"

         ThisWorkbook.Close False

End Sub

            

Private Sub Workbook_Open()

 MessageBox

End Sub