Hi,

Is there anyway to make vbApplicationModal = False...?

I have an Excel macro which opens up a message box - when this message box
is open, I want the user to be able to use othe Excel workbooks except for
the one displaying the message box...

There doesn't seem to be a way to do this...

for some reason vbApplicationModal seems to be the default, as well as the
only option (ie. default is true, and the only option is to set it as true -
booyeah!)

If all your users are using xl2k+, then why not use a userform instead?

k1sr wrote:

Hi,

Is there anyway to make vbApplicationModal = False...?

I have an Excel macro which opens up a message box - when this message box
is open, I want the user to be able to use othe Excel workbooks except for
the one displaying the message box...

There doesn't seem to be a way to do this...

for some reason vbApplicationModal seems to be the default, as well as the
only option (ie. default is true, and the only option is to set it as true -
booyeah!)

--

Dave Peterson

Hi Dave,

It still seems to be modal - I'm not familiar with User Forms - do I bring
up the User From simply with UserForm1.Show or is there another way, as this
way leaves me still unable to use other excell worksheets...

cheers

You can make a UserForm non-modal by using this statement to initially show
it...

UserForm1.Show vbModeless

Substitute the name of your actual UserForm for the example name UserForm1
that I used.

Rick


"k1sr" wrote in message
...
Hi Dave,

It still seems to be modal - I'm not familiar with User Forms - do I bring
up the User From simply with UserForm1.Show or is there another way, as
this
way leaves me still unable to use other excell worksheets...

cheers

By default user forms open modal, so use the following method to open it
non-modal

UserForm1.Show (0)

--

Regards,
Nigel




"k1sr" wrote in message
...
Hi Dave,

It still seems to be modal - I'm not familiar with User Forms - do I bring
up the User From simply with UserForm1.Show or is there another way, as
this
way leaves me still unable to use other excell worksheets...

cheers

Thanks guys...

Is there any way to keep that worksheet modal, such that the application and
other workbooks/worksheets are freed up and can be used but the initial
worksheet cannot...

Maybe I just want too much...

Cheers

You could use this Workbook_SheetActivate code as a guide. First, it hides
the UserForm and then, depending on which worksheet is selected, it shows
the UserForm as either modal or modeless.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
UserForm1.Hide
If Sh.Name = "Sheet1" Then
UserForm1.Show
Else
UserForm1.Show vbModeless
End If
End Sub

By expanding this into an If-ElseIf-Else-EndIf structure, you can even
choose not to show the UserForm for some sheets, show it modal for others
and show it modeless for still others.

Rick


"k1sr" wrote in message
...
Thanks guys...

Is there any way to keep that worksheet modal, such that the application
and
other workbooks/worksheets are freed up and can be used but the initial
worksheet cannot...

Maybe I just want too much...

Cheers

THanks Rick,

I'm not sure that that's what I'm after...

Say I've got 2 workbooks open - WBK1 and WBK2

In WBK1 I do somethin to bring up a message box or user form, I want WBK1 to
remain modal, whereas WBK2 is modeless...

In short, access to WBK1 is blocked, however the user can access WBK2

Cheers

"Rick Rothstein (MVP - VB)" wrote:

You could use this Workbook_SheetActivate code as a guide. First, it hides
the UserForm and then, depending on which worksheet is selected, it shows
the UserForm as either modal or modeless.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
UserForm1.Hide
If Sh.Name = "Sheet1" Then
UserForm1.Show
Else
UserForm1.Show vbModeless
End If
End Sub

By expanding this into an If-ElseIf-Else-EndIf structure, you can even
choose not to show the UserForm for some sheets, show it modal for others
and show it modeless for still others.

Rick


"k1sr" wrote in message
...
Thanks guys...

Is there any way to keep that worksheet modal, such that the application
and
other workbooks/worksheets are freed up and can be used but the initial
worksheet cannot...

Maybe I just want too much...

Cheers