How to Convert Data Types, Force Concatenated String into an Object and .Show

[Duvdev]

Hello,

Not sure if I am attempting to do something that is not possible but it seems like it should be a very simple/easy procedure. I have a series of previously written UserForms that take user entered values and populates values throughout a document. The userForms have all been tested and work correctly with no issue.
I wanted to take the UserForms functionality a bit further to up processes. Then following the UserForm assignment I was planning on running a macro after each UserForm. If I use a separate macro to simply show the UserForm (example: UserForm1.Show) I have no issue “calling” the individual UserForms.

I have attempted to iterate through the code using something similar to the following:
Sub UserForm_Example ()
Dim Int1 As Integer
Int1 = 0
Dim ObjName As Variant
ObjName = “Object” & Int1 & “_Information”
Dim Status As String

VBA.UserForms(ObjName).Show
Call FirstMacro

Int1 = Int1 + 1
Loop

End Sub


For this example, I would have say 10 different UserForms titles as “Object0_Information”, “Object1_Information”, …

At the ObjName concatenation I am running into a ‘424 Run-Time error, Object Required’ meaning that I need to somehow convert the ObjName results into an object, feed the results into a separate object or maybe use the .Show method in some other way.

Please let me know if anyone has any ideas for making this work. Seems like it should be a really really simple method but I cannot seem to figure it out.

Thank you for your time!

[Frosty]

The UserForms collection is a collection of currently loaded UserForms. So I think you're getting that error because the user form you want to call simply isn't loaded.

If you've just started Word, as an example, and you type ?UserForms.Count into the immediate window from within Word, then you'll have a count of 0 (since, presumably, no user forms have been loaded by any auto macros).

What you seem to want to do is analogous to knowing the macro name you want to run, but instead of simply calling it by it's name, you want to be able to call it by a String name... like Application.Run allows you to do
Application.Run "MySub"
instead of
Call MySub
or even more simply
MySub

I don't think there is something analogous to Application.Run for accessing user forms.

I'm a little confused by the request, though... since certainly there are other ways to do this. You have a button (typically the "OK" or "DONE" button) on your user form - why don't you use the click event within the user form to call FirstMacro?

Or why not simply have a series of calls
UserForm1.Show
Call FirstMacro
UserForm2.Show
Call FirstMacro

How many user forms are you really talking about?

[Paul_Hossler]

I don't understand all that you're trying to do, other than loop through the UF's and do something

Assuming that all of the userforms are loaded, you have to iterate the index and compare the name you're looking for

Maybe something like this but with error checking




Option Explicit
Sub test()

Dim O As Object

Load UserForm1
Load UserForm2
Load UserForm3

Set O = ReturnUserform("userform2")

If O Is UserForm1 Then
MsgBox O.Name
ElseIf O Is UserForm2 Then
MsgBox O.Name
ElseIf O Is UserForm3 Then
MsgBox O.Name
Else
MsgBox "Nothing"
End If
End Sub

Function ReturnUserform(s As String) As Object
Dim i As Long

Set ReturnUserform = Nothing

For i = 1 To UserForms.Count
If UCase(UserForms(i - 1).Name) = UCase(s) Then
Set ReturnUserform = UserForms(i - 1)
Exit Function
End If
Next I
End Function

[Duvdev]

Hello Frosty and Paul,

I apologize for the delay in response as I was away from my code for a few days for a long weekend.

You are both correct that I am basically attempting to first call a customized UserForm followed by a macro. Currently I am working with seven different "sets", each of which includes 15 UF's. Over time I plan to increase the number of sets and will likely increase the UF's to 25 total. After realizing how many of these sets I have to develop, I started thinking along the lines of making VB do the work (to speed up the process, decrease typo's and decrease code size). I found that it wasn't an issue to go in and copy/paste the code 15 times but I figured there must be a better way.

It does in fact appear that I both had the issue that the UF was not loaded, and that I was not physically "telling" VB to look at the string name. While stepping through the code I noticed that VB was not actually trying to load the current UF. I don't think I had the necessary code to make VB assign the string name for the UF, followed by actually launching the UF.

At first glance it looks to me like the code posted by Paul has exactly what I need. I will adjust the code to match my UF names and give it a shot but I do believe this is exactly what I was looking to achieve. Also I will post back as soon as I can regarding the status of the project. Sorry for asking odd questions, this is a little out of my league but I am giving it my best to advance my code (and learn along the way).

I really appreciate the help!

[Frosty]

You may want to look at reusing some of your forms, instead of creating so many. I'm guessing a lot could be accomplished simply by changing the caption properties of your labels for your inputs when you call a particular form in order to display to the user different info to be used in the form. One generic form where you load it.

If your form is called myForm1... You can load it directly with
Load myForm1

but you could also use it twice with
dim my1 as myForm1
dim my2 as myForm1

set my1 = New myForm1
set my2 = New myForm1

[Frosty]

That is a much more manageable structure if you later need to make modifications to your form(s)