Message box?

[Von Pookie]

Yes, I'm hoping to fiddle with that little bit of code everyone's helped me with yet again (a.k.a. I had an idea).

I'm not sure if my idea will work, but I'm looking for opinions/help with it.

The current code I have opens all necessary workbooks that I need to use when entering information from a form. When I get the form back, I enter information into excel to "close" it, and I only need to use 2 workbooks.

Could I have a message box pop up first thing asking if I want to "open" or "close", and then open only the workbooks needed for that particular function?

(One of the workbooks is selected through the open dialog box which the current macro opens).

If any part of that needs clarification, let me know and I'll try to explain it a tad better.

Thanks!

[Von Pookie]

[daleyman]

clarification would be good! maybe quote a little of your existing code?

[Von Pookie]

Private Sub Workbook_Open()
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


End
myErr:
ThisWorkbook.Close False
End Sub

That's what I currently have. I was just wondering if I could have a message box pop up before that runs to have it run the following:

If "Open" is chosen, run the current macro, since all of these workbooks are needed.

If "Close" is chosen, open only the Main Directory.xls and bring up the open dialog box to choose the other workbook to open.

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

[Englebert Dumpydick]

"Experience is that which provides you with the capability of recognising a mistake when you make it again." - Englebert Dumpydick

[Von Pookie]

You're about the second one to post some inane quote after one of my posts, (I'm assuming) simply because of the quote in my signature.

Oh, grow up.

If you can't at least try to help, why bother posting?

[Englebert Dumpydick]

On 2002-04-22 07:16, Von Pookie wrote:
You're about the second one to post some inane quote after one of my posts, (I'm assuming) simply because of the quote in my signature.

Oh, grow up.

If you can't at least try to help, why bother posting?

Ah ... you've made the mistake of responding.

There is absolutely nothing inane about the statement "Experience is that which provides you with the capability of recognising a mistake when you make it again." You may well want to bear it in mind before your next posting.

As you have correctly assumed, my response was prompted by your quote "There are two means of refuge from the miseries of life: music and cats." which is an excellent example of an inane statement and certainly an irrelevant one on a board like this.

To borrow from your last comment, if you can't just sign your name without this inane statement "why bother posting" ?

Warm regards,
Englebert Dumpydick

("Young persons tend to lack experience, but it doesn't matter since they know everything anyway." - E.D.)

[Von Pookie]

I did not come to argue about a stupid sig file. Anyway...

Any actual help with the original question is appreciated, even if it's just a "nope, it can't be done."

Thanks,

_________________
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-22 08:02 ]

[Joe Was]

Build a new macro which displays your MsgBox then calls your existing code.

In help lookup "Call" and "MsgBox" both have good examples in Excel help if you are a novice. JSW

[Von Pookie]

That is what I need help doing. Basically, I am nowhere near "novice" level when it comes to macros/VBA. Just can't do it.

And whoever installed Excel on the computer I'm using here at work didn't install the Excel VBA help file, so I can't find anything too helpful in the regular help. I've tried it before.

Thanks anyway,