Do not execute Double Click Event when UserForm is Showing

Is there a way to not execute the Event below if any UserForm is showing?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

If a UserForm is showing Exit Sub Else Continue with code..

End Sub

MikeH
Fri May 09 14:18:01 CDT 2008

Hi,

You could in VB editor right click the userform and click properties and set
the 'ShowModal' property to TRUE

Mike

"RyanH" wrote:

> Is there a way to not execute the Event below if any UserForm is showing?
>
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
>
> If a UserForm is showing Exit Sub Else Continue with code..
>
> End Sub

RyanH
Fri May 09 14:33:03 CDT 2008

I knew about that, but if the user wants to minimize Excel they would need to
finish the UserForm, Close it, then minimize Excel. In some cases the user
needs to keep the UserForm open and minimize Excel to access other programs.
In the mean time I do not want my Double Click Event to execute if a UserForm
is open, because it will screw up some formatting I have. Is there another
way?

"Mike H" wrote:

> Hi,
>
> You could in VB editor right click the userform and click properties and set
> the 'ShowModal' property to TRUE
>
> Mike
>
> "RyanH" wrote:
>
> > Is there a way to not execute the Event below if any UserForm is showing?
> >
> > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> > Boolean)
> >
> > If a UserForm is showing Exit Sub Else Continue with code..
> >
> > End Sub

Rick
Fri May 09 14:37:13 CDT 2008

Assuming you are showing your UserForm as vbModeless and assuming your
UserForm is named UserForm1 (change the code for your actual UserForm name),
put this as the first statement in the BeforeDoubleClick event...

If UserForm1.Visible Then Exit Sub

Rick


"Mike H" <MikeH@discussions.microsoft.com> wrote in message
news:BEA5C20B-8C0B-4FF9-9555-75D820D10B34@microsoft.com...
> Hi,
>
> You could in VB editor right click the userform and click properties and
> set
> the 'ShowModal' property to TRUE
>
> Mike
>
> "RyanH" wrote:
>
>> Is there a way to not execute the Event below if any UserForm is showing?
>>
>> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
>> Boolean)
>>
>> If a UserForm is showing Exit Sub Else Continue with code..
>>
>> End Sub

Peter
Fri May 09 15:53:48 CDT 2008

If UserForms.Count Then exit sub

If you want to test if a particular userform is both loaded and visible

If UserForms.Count Then
For i = 0 To UserForms.Count - 1
If UserForms(i).Name = "UserForm2" Then
bUF2loaded = True
bUF2Visible = UserForms(i).Visible
Exit For
End If
Next
End If

Regards,
Peter T

"RyanH" <RyanH@discussions.microsoft.com> wrote in message
news:B48D998D-73C2-4ED3-9503-5E7E181D6932@microsoft.com...
> Is there a way to not execute the Event below if any UserForm is showing?
>
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
>
> If a UserForm is showing Exit Sub Else Continue with code..
>
> End Sub