PDA

View Full Version : Solved: Is MyUserForm in ThisWorkbook



Ryan Remole
10-27-2010, 01:44 PM
Sub example()

'trying to determine if a particular UserForm is in 'ThisWorkbook'

Dim varObj As Object
'For Each varObj In VBProject 'or
'For Each varObj in MSForms 'or other???
If varObj.Name = "UserForm1" Then
var1 = 1
Exit For
End If
Next

If var1 = 1 Then MsgBox """UserForm1"" is in the workbook."

End Sub


I have a workaround to my issue, but I know someone can probably just quickly answer my question! Thanks for your help!

xld
10-27-2010, 02:01 PM
Does this help?



Sub example()
Const vbext_ct_StdModule As Long = 1
Const vbext_ct_ClassModule As Long = 2
Const vbext_ct_MSForm As Long = 3

'trying to determine if a particular UserForm is in 'ThisWorkbook'
Dim wb As Workbook
Dim varObjs As Object
Dim varObj As Object
Dim var1
For Each wb In Application.Workbooks
On Error Resume Next
Set varObjs = wb.VBProject.VBComponents
On Error GoTo 0
If Not varObjs Is Nothing Then
For Each varObj In varObjs
Select Case varObj.Type
Case vbext_ct_MSForm
MsgBox wb.Name
Exit For
End Select
Next varObj
End If
Next

If var1 = 1 Then MsgBox """UserForm1"" is in the workbook."

End Sub

Ryan Remole
10-27-2010, 03:49 PM
I do not see a way to extract a userform's (name), but your code adequately reflects if there are any userforms present. I can make this work! Thank you very much for your help!

xld
10-29-2010, 03:14 AM
The userform's name would be



varObj.Name

Ryan Remole
10-30-2010, 09:03 AM
Yes-that works perfectly. I don't remember now what I was smoking, but it must have been good. I think it only works on Saturdays! Thanks!


Sub asdlfk()
Dim varObj As Object, varObjs As Object
Set varObjs = ThisWorkbook.VBProject.VBComponents
If Not varObjs Is Nothing Then
For Each varObj In varObjs
Select Case varObj.Type
Case 3
If varObj.Name = "UserForm1" Then
var1 = 1
Exit For
End If
End Select
Next varObj
End If
If var1 = 1 Then MsgBox """UserForm1"" is in the workbook."
End Sub

mikerickson
10-30-2010, 09:18 AM
If only a True/False is needed, there is no need to loop through components.
Function WorkbookContainsUF(wb As Workbook, ufName As String) As Boolean
On Error Resume Next
WorkbookContainsUF = (wb.VBProject.VBComponents(ufName).Type = 3)
On Error GoTo 0
End Function

Sub test()
MsgBox WorkbookContainsUF(ThisWorkbook, "UserForm1")
End Sub

Ryan Remole
10-30-2010, 11:28 AM
Awsome! I love it!