This msgbox code works when in Auto_open, but not when called.

Here is code that works in all other workbooks when it's the "Auto_Open"
script, but doesn't work when called from another script (and other vb code
works fine in same scenario):

************************************
Sub PrintOnColouredPaper_Ask()

MyMsgBox = MsgBox("Put either pink or green paper into the printer.",
vbOKCancel + vbExclamation, "Print?")

If MyMsgBox = 1 Then
Application.Dialogs(xlDialogPrint).Show

End If

End Sub
************************************

What am I doing wrong, pls?

Thank you! :oD

Works ok for me in Excel97 when called from another sub. The only
change was I added a "Dim MyMsgBox As Integer" line in the sub because I
have variable declaration required (highly recommended).
How does it fail for you? What is the value of MyMsgBox after the
MsgBox call?



StargateFanFromWork wrote:
> Here is code that works in all other workbooks when it's the "Auto_Open"
> script, but doesn't work when called from another script (and other vb code
> works fine in same scenario):
>
> ************************************
> Sub PrintOnColouredPaper_Ask()
>
> MyMsgBox = MsgBox("Put either pink or green paper into the printer.",
> vbOKCancel + vbExclamation, "Print?")
>
> If MyMsgBox = 1 Then
> Application.Dialogs(xlDialogPrint).Show
>
> End If
>
> End Sub
> ************************************
>
> What am I doing wrong, pls?
>
> Thank you! :oD
>
>

On Tue, 20 Dec 2005 02:21:34 GMT, LenB <[email protected]> wrote:

>Works ok for me in Excel97 when called from another sub. The only
>change was I added a "Dim MyMsgBox As Integer" line in the sub because I
>have variable declaration required (highly recommended).
>How does it fail for you? What is the value of MyMsgBox after the
>MsgBox call?

I'm sorry. I completely forgot to add the the error msg!

I have this exact msgbox code in the Auto_Open of 3 other xl2k sheets
and they all work fine there. The _only_ difference in each is a
slight variation in the text to fit each individual case.

But when I do a straight subsitute of this for vb code for working
code in another sheet as part of a floating toolbar, I get the
following error msg and am taken to the vbe:

"Compile error: variable not defined"

and

"MyMsgBox ="

is highlighted. This is so odd that it works as Auto_Open but not
here.

As I mentioned above, simply copy/pasted working code from one sheet
and pasted into working code area of another and I've re-checked. I
substitute codes back and the floating toolbar continues to work.
It's something in this one that is a problem, it seems.

Is there anything that can be done to fix? My floating toolbar is
missing the print part, as things stand now <g>.

>StargateFanFromWork wrote:
>> Here is code that works in all other workbooks when it's the "Auto_Open"
>> script, but doesn't work when called from another script (and other vb code
>> works fine in same scenario):
>>
>> ************************************
>> Sub PrintOnColouredPaper_Ask()
>>
>> MyMsgBox = MsgBox("Put either pink or green paper into the printer.",
>> vbOKCancel + vbExclamation, "Print?")
>>
>> If MyMsgBox = 1 Then
>> Application.Dialogs(xlDialogPrint).Show
>>
>> End If
>>
>> End Sub
>> ************************************
>>
>> What am I doing wrong, pls?
>>
>> Thank you! :oD
>>
>>

Aha! That makes it easy to fix. Add the line that I added (Dim
MyMsgBox As Integer) as the first line in your sub it will work.
You likely have the line "Option Explicit" in the declarations section
of the module containing the sub. Your other workbooks or sheets likely
don't have it, so the Dim statement isn't needed. That is my only idea
as why this one is failing and not the others.

Option Explicit is highly recommended, which is the same as the "require
variable declaration" option. It keeps out hard to find bugs where a
variable name is misspelled. I can explain more if you need it.

Len

StargateFan wrote:
> On Tue, 20 Dec 2005 02:21:34 GMT, LenB <[email protected]> wrote:
>
>
>>Works ok for me in Excel97 when called from another sub. The only
>>change was I added a "Dim MyMsgBox As Integer" line in the sub because I
>>have variable declaration required (highly recommended).
>>How does it fail for you? What is the value of MyMsgBox after the
>>MsgBox call?
>
>
> I'm sorry. I completely forgot to add the the error msg!
>
> I have this exact msgbox code in the Auto_Open of 3 other xl2k sheets
> and they all work fine there. The _only_ difference in each is a
> slight variation in the text to fit each individual case.
>
> But when I do a straight subsitute of this for vb code for working
> code in another sheet as part of a floating toolbar, I get the
> following error msg and am taken to the vbe:
>
> "Compile error: variable not defined"
>
> and
>
> "MyMsgBox ="
>
> is highlighted. This is so odd that it works as Auto_Open but not
> here.
>
> As I mentioned above, simply copy/pasted working code from one sheet
> and pasted into working code area of another and I've re-checked. I
> substitute codes back and the floating toolbar continues to work.
> It's something in this one that is a problem, it seems.
>
> Is there anything that can be done to fix? My floating toolbar is
> missing the print part, as things stand now <g>.
>
>
>>StargateFanFromWork wrote:
>>
>>>Here is code that works in all other workbooks when it's the "Auto_Open"
>>>script, but doesn't work when called from another script (and other vb code
>>>works fine in same scenario):
>>>
>>>************************************
>>>Sub PrintOnColouredPaper_Ask()
>>>
>>> MyMsgBox = MsgBox("Put either pink or green paper into the printer.",
>>>vbOKCancel + vbExclamation, "Print?")
>>>
>>> If MyMsgBox = 1 Then
>>> Application.Dialogs(xlDialogPrint).Show
>>>
>>> End If
>>>
>>>End Sub
>>>************************************
>>>
>>>What am I doing wrong, pls?
>>>
>>>Thank you! :oD
>>>
>>>
>
>

On Tue, 20 Dec 2005 16:06:54 GMT, LenB <[email protected]> wrote:

>Aha! That makes it easy to fix. Add the line that I added (Dim
>MyMsgBox As Integer) as the first line in your sub it will work.

It did! Kewl. <g>

>You likely have the line "Option Explicit" in the declarations section
>of the module containing the sub. Your other workbooks or sheets likely
>don't have it, so the Dim statement isn't needed. That is my only idea
>as why this one is failing and not the others.

Yes, that is very true. Because that's what was in the code
initially.

>Option Explicit is highly recommended, which is the same as the "require
>variable declaration" option. It keeps out hard to find bugs where a
>variable name is misspelled. I can explain more if you need it.

If I'm understanding correctly, no matter what code I'm
using/modifying, I should have this "Option Explicit"? Neat. I
imagine that I would always have to be on the lookout for "Dim"
whatever in every other code below that? (i.e., in this case, I just
added the line you say "Dim MyMsgBox As Integer" after the sub's
name).

Thank you.

>Len
>
>StargateFan wrote:
>> On Tue, 20 Dec 2005 02:21:34 GMT, LenB <[email protected]> wrote:
>>
>>
>>>Works ok for me in Excel97 when called from another sub. The only
>>>change was I added a "Dim MyMsgBox As Integer" line in the sub because I
>>>have variable declaration required (highly recommended).
>>>How does it fail for you? What is the value of MyMsgBox after the
>>>MsgBox call?
>>
>>
>> I'm sorry. I completely forgot to add the the error msg!
>>
>> I have this exact msgbox code in the Auto_Open of 3 other xl2k sheets
>> and they all work fine there. The _only_ difference in each is a
>> slight variation in the text to fit each individual case.
>>
>> But when I do a straight subsitute of this for vb code for working
>> code in another sheet as part of a floating toolbar, I get the
>> following error msg and am taken to the vbe:
>>
>> "Compile error: variable not defined"
>>
>> and
>>
>> "MyMsgBox ="
>>
>> is highlighted. This is so odd that it works as Auto_Open but not
>> here.
>>
>> As I mentioned above, simply copy/pasted working code from one sheet
>> and pasted into working code area of another and I've re-checked. I
>> substitute codes back and the floating toolbar continues to work.
>> It's something in this one that is a problem, it seems.
>>
>> Is there anything that can be done to fix? My floating toolbar is
>> missing the print part, as things stand now <g>.
>>
>>
>>>StargateFanFromWork wrote:
>>>
>>>>Here is code that works in all other workbooks when it's the "Auto_Open"
>>>>script, but doesn't work when called from another script (and other vb code
>>>>works fine in same scenario):
>>>>
>>>>************************************
>>>>Sub PrintOnColouredPaper_Ask()
>>>>
>>>> MyMsgBox = MsgBox("Put either pink or green paper into the printer.",
>>>>vbOKCancel + vbExclamation, "Print?")
>>>>
>>>> If MyMsgBox = 1 Then
>>>> Application.Dialogs(xlDialogPrint).Show
>>>>
>>>> End If
>>>>
>>>>End Sub
>>>>************************************
>>>>
>>>>What am I doing wrong, pls?
>>>>
>>>>Thank you! :oD

On Mon, 19 Dec 2005 18:31:14 -0500, "StargateFanFromWork"
<[email protected]> wrote:

>Here is code that works in all other workbooks when it's the "Auto_Open"
>script, but doesn't work when called from another script (and other vb code
>works fine in same scenario):
>
>************************************
>Sub PrintOnColouredPaper_Ask()
>
> MyMsgBox = MsgBox("Put either pink or green paper into the printer.",
>vbOKCancel + vbExclamation, "Print?")
>
> If MyMsgBox = 1 Then
> Application.Dialogs(xlDialogPrint).Show
>
> End If
>
>End Sub
>************************************
>
>What am I doing wrong, pls?
>
>Thank you! :oD


Good Morning.

To give closure, the code above now looks like this with the addition
of the "Dim" line:


************************************
Sub PrintOnColouredPaper_Ask()

Dim MyMsgBox As Integer

MyMsgBox = MsgBox("Put either pink or green paper into the
printer.", vbOKCancel + vbExclamation, "Print?")

If MyMsgBox = 1 Then
Application.Dialogs(xlDialogPrint).Show

End If

End Sub
************************************

Code works when called from other code now. Thank you. :oD