Do not execute Double Click Event when UserForm is Showing

[RyanH]

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

 

[Mike H]

Hi,

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

Mike

 

[RyanH]

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?

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Peter T]

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