0 votes
in VBA by Beginner (30 points)

so in my previous code, i left it at UserForm3. and i have the routine below to handle that. but since changing it to btn_Gen, now i get an "object error" because the routine checks for all loaded userforms. so how can i expose all the userforms named userform#.

    QtyUF = miNoOfUserForms + 1
    ufName = "UserForm" & QtyUF

and here is the function:

Function miNoOfUserForms() As Integer
    
    Const iUSERFORM_TYPE    As Integer = 3
    
    Dim iNoOfUserForms      As Integer
    Dim objVBComponent      As Object
    
    iNoOfUserForms = 0
    
    For Each objVBComponent In ThisWorkbook.VBProject.VBComponents
        
        If objVBComponent.Type = iUSERFORM_TYPE Then
            iNoOfUserForms = iNoOfUserForms + 1
        End If
        
    Next objVBComponent
    
    miNoOfUserForms = iNoOfUserForms
    
End Function

so the function returns the number of total userforms when i need it to return the number of userforms named userform#

if you need to see the file, it is here on excelforum.

https://www.excelforum.com/excel-programming-vba-macros/1267352-cross-posted-from-wellsr-com-getting-a-subscript-out-of-range.html#post5078774

by Beginner (30 points)
yes exactly what i am experiencing. so the function "miNoOfUserForms" needs to be changed to only search for userforms named UserForm#. can you help me on this?
by Expert (916 points)
You may try to assign -1 instead of 0 to the variable at this line (I can't test now)

iNoOfUserForms = -1
by Beginner (30 points)
yah, i cant do that. what if there are userforms not  named userform#? there could be any number of userforms and that would trip up the function. I am going to the post where i got the original function from and see if he can help out.
by Beginner (30 points)
+1

so a member from another forum (excel forum) made the code for me that works.

see below:

Public Function CountUserForm() As Integer
    Dim oForm As Object
    For Each oForm In ActiveWorkbook.VBProject.vbcomponents
        If oForm.Type = 3 And oForm.Name Like "UserForm*" Then
            CountUserForm = CountUserForm + 1
        End If
    Next
End Function

you can mark this as solved.

by Expert (916 points)
Glad you can solve it. Regards

1 Answer

0 votes
by Super Expert (3.2k points)
 
Best answer

This question is marked as answered based on @damcg's comment:

so a member from another forum (excel forum) made the code for me that works.

see below:

Public Function CountUserForm() As Integer
    Dim oForm As Object
    For Each oForm In ActiveWorkbook.VBProject.vbcomponents
        If oForm.Type = 3 And oForm.Name Like "UserForm*" Then
            CountUserForm = CountUserForm + 1
        End If
    Next
End Function

you can mark this as solved.

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...