Loop thru controls on another workbooks userform

[rigray]

OK I have searched and searched on how to do this properly. Here goes. I have 2 workbooks. In the first workbook I have some userforms (nested userforms that create a user interface for data input that is then saved to worksheets). Each userform of course has controls. The 2nd workbook has the macros. I have a read and write macro in this workbook that is invoked at appropriate times for reading and writing. In some cases, I have special write scenarios that require a little more processing. I have been able to use the application.run approach to run the macros in the 2nd workbook from the 1st workbook userforms.

Here's where I'm stomped. The times where I have to special write scenarios, in order to do this, while in the 2nd workbook's macros, I need to refer back to the 1st workbook launch an alternative userform and then loop thru the controls (textboxes) and update certain textbox values. To attempt this, I was performing Application.Run ("'" & wb.Name & "'!showNA" which will open and initialize the other userform in the 1st workbook. Then I want to loop thru it's controls using For Each ctrl In vbcompfrm.Designer.Controls of which I can not get to work and have tried many different syntax and combinations. Help! I do have the extensibility reference checked in tools as well. I am a self taught vba hobbyist so please go easy on my approach :D Thank you so much.

'code in workbook 2'
Public Sub test(ByRef FRM)
Dim ctrl As MSForms.Control
Dim vbcompfrm As VBComponent
Dim SubStr As String
Dim MyArr() As Variant
Dim key As String
Dim ATTR As String

ReDim MyArr(1)
MyArr(0) = "first"
MyArr(1) = "second"

Set ws = wb.Worksheets("testws")
    Application.Run ("'" & wb.Name & "'!showNA")    'this will show the userform NA form the first workbook'
    'Application.Run ("'" & wb.Name & "'!hideNA")
    With UserForms("tbls_RTU_NA")
        Set vbcompfrm = wb.VBProject.VBComponents("NA")    'this is the userform in the first workbook, we want to loop thru the controls'
        For Each ctrl In vbcompfrm.Designer.Controls    'not sure how to make this work'
            With ctrl
                For i = LBound(MyArr) To UBound(MyArr)
                    If .ControlTipText = MyArr(i) Then
                        datasplit = Split(MyArr(i), "_")
                        key = datasplit(0)
                        ATTR = datasplit(1)
                        .Text = SearchDataArray(StandardTableDataArray, "NA_" & key, ATTR)    'don't forget we need to set the value of the new text to the array value once we match the key
                    End If
                Next i
            End With
        Next ctrl
    End With

 

[smozgur]

Here is an amazing method posted by @Juan Pablo González 16 years ago.

Reference Object on User Form in separate Workbook

I know how to run a macro that resides in a module in another workbook. Application.Run "'MacroSource.xls'!Macro1" What I would like to do is control an object that resides on a user form in another workbook and I’m having problems with the statement. For example, I like to have a user press...

www.mrexcel.com

Only two steps, then you have all access to the user form in the other workbook without dealing with VBProject or Designer.

Basically, create a public sub procedure in a standard module in the workbook which contains the user form named tbls_RTU_NA.

Function GetUserform() As tbls_RTU_NA
    Set GetUserform = tbls_RTU_NA
End Function

See the return type of the function? It returns the "user form" named tbls_RTU_NA. The user form is a registered class object once it is added to a project. So it is an object type itself.

Create the following sub procedure in a standard module in the other workbook that is supposed to control the user form in the workbook wb. I can't see the first workbook name you set as wb object in your code, so I will refer to it as Workbook("workbook_with_userform.xlsm"), you can set it however you need.

Sub MainMacro()
Dim objUserForm As Object
Dim wb As Workbook
    Set wb = Workbook("workbook_with_userform.xlsm")
    ' Execute the public procedure in the other workbook to retrieve its user form object
    Set objUserForm = Application.Run(wb.Name & "!GetUserForm")
    ' Now we have the user form object, we can do whatever we want since the object variable refers to the actual object
    objUserForm.Caption = "This caption changed by another workbook"
    ' Show the user form
    objUserForm.Show
End Sub

Since you have the object reference to the user form in the MainMacro, you can now do anything with any control on it. I just changed the form caption.

Please try this simple implementation first, and see how it works, then try to apply in your project.
Let me know if you have difficulty, because I tested it, and it works great.

 

[rigray]

Here is an amazing method posted by @Juan Pablo González 16 years ago.

Reference Object on User Form in separate Workbook

I know how to run a macro that resides in a module in another workbook. Application.Run "'MacroSource.xls'!Macro1" What I would like to do is control an object that resides on a user form in another workbook and I’m having problems with the statement. For example, I like to have a user press...

www.mrexcel.com

Only two steps, then you have all access to the user form in the other workbook without dealing with VBProject or Designer.

Basically, create a public sub procedure in a standard module in the workbook which contains the user form named tbls_RTU_NA.

Function GetUserform() As tbls_RTU_NA
    Set GetUserform = tbls_RTU_NA
End Function

See the return type of the function? It returns the "user form" named tbls_RTU_NA. The user form is a registered class object once it is added to a project. So it is an object type itself.

Create the following sub procedure in a standard module in the other workbook that is supposed to control the user form in the workbook wb. I can't see the first workbook name you set as wb object in your code, so I will refer to it as Workbook("workbook_with_userform.xlsm"), you can set it however you need.

Sub MainMacro()
Dim objUserForm As Object
Dim wb As Workbook
    Set wb = Workbook("workbook_with_userform.xlsm")
    ' Execute the public procedure in the other workbook to retrieve its user form object
    Set objUserForm = Application.Run(wb.Name & "!GetUserForm")
    ' Now we have the user form object, we can do whatever we want since the object variable refers to the actual object
    objUserForm.Caption = "This caption changed by another workbook"
    ' Show the user form
    objUserForm.Show
End Sub

Since you have the object reference to the user form in the MainMacro, you can now do anything with any control on it. I just changed the form caption.

Please try this simple implementation first, and see how it works, then try to apply in your project.
Let me know if you have difficulty, because I tested it, and it works great.

Full disclosure, I did attempt this approach prior to posting originally. I ran into an issue I could not solve and I am not getting that same issue again. Run-time error '1004' cannot run the macro wb!showNA. The macro may not be available in this workbook or all macros may be disabled. I have resaved the workbook as .xlsm and retried with the same error. The error occurs at the Set otherFRM code when debugged.

'in workbook with userform'
Public Function showNA() As tbls_RTU_NA
    Set showNA = tbls_RTU_NA 'tbls_RTU_NA.Show
End Function

'in macro workbook'
Set otherFRM = Application.Run(wb.Name & "!showNA")    'error occurs here'
    otherFRM.Show
    otherFRM.Hide
    With UserForms("tbls_RTU_NA")
        For Each ctrl In otherFRM.Controls

 

[smozgur]

I did attempt this approach prior to posting originally. I ran into an issue I could not solve and I am not getting that same issue again.

That's the reason I recommended to try the two steps I explained in my message first. Because when you try to apply it in your code without experiencing it first, it means you are simply trying to patch your code instead of understanding how it is working, and the other parts in your own code might be causing problems. You should then debug step by step. The first problem looks like the wb assignment according to the error.

If the error shows the line you indicated then the first problem is something about the wb.Name, which is supposed be the name of the workbook with user form. Try to use the file name with extension instead of wb.Name, like this: Set otherFRM = Application.Run("workbook_with_userform.xlsm" & "!showNA"). If it works (as it should) then check the wb assignment where you set it in the code. It might be referring to a wrong workbook.

Then, you should also use otherFRM object reference instead of With UserForms("tbls_RTU_NA") since you have the pointer to the user form after setting it in the previous line.

Perhaps, you might want to paste the entire code in the macro workbook (from Sub to End Sub), then we might say something more.

 

[rigray]

Fair enough. I just felt confident enough in the code to do it directly. :D I've tested mutliple options with the application.run file name syntax. Same issue each time.

Public Sub Update(ByRef FRM)            'this FRM is the original calling form from workbook 1 that has the "otherFRM" also in it's vbproject
Dim ctrl As MSForms.Control
Dim otherFRM As Object
Dim SubStr As String
Dim MyArr() As Variant
Dim key As String
Dim ATTR As String

ReDim MyArr(1)
MyArr(0) = "07_RATING1"
MyArr(1) = "07_DREF"

'BKR TABLES MOS/SW UPDATE
Set ws = wb.Worksheets(NEWWCWS)
Set otherFRM = Application.Run("test.xlsm" & "!showNA")  
    otherFRM.Show
    otherFRM.hide
    With otherFRM
        For Each ctrl In otherFRM.Controls
            With ctrl
                For i = LBound(MyArr) To UBound(MyArr)
                    If .ControlTipText = MyArr(i) Then
                        datasplit = Split(MyArr(i), "_")
                        key = datasplit(0)
                        ATTR = datasplit(1)
                        .Text = SearchDataArray(StandardTableDataArray, "NA_" & key, ATTR)              
                    End If
                Next i
            End With
        Next ctrl
    End With
    Call WriteData(tbls_rtu_na, "CommandButton1")
    Unload tbls_rtu_na
end sub

 

[smozgur]

I know it sounds irrelevant, but where is the assignment for wb object variable?
Also, NEWWCWS?
And, tbls_rtu_na, isn't it supposed to be the otherFRM already?
My suggestion: always require variable declaration by using Option Explicit to catch the errors due to missing variables.

Important points that I didn't inquiry before by assuming you already checked them.
I assume test.xlsm is an open workbook that you are trying to run its macro.
And showNA is in the standard module, not in the user form class module.

Let me get it right this one as well: Do you call the Update() sub procedure from the workbook that has the user form which you are trying to reach in this procedure??? So, both workbooks are calling each other's codes? Although I am not able to understand the whole logic, why don't you also pass the otherFRM as the parameter as well? And honestly, where do you use the FRM object that you currently pass to the Update()?

So, there are so many questions in your project that is likely affecting the logic you are trying to build. Unfortunately this prevents to provide more information other than checking the environment and requirements like having showNA in a standard module, and giving the recipe for what you asked for that I am sure it is working without problems. If you could go through all these step by step, then you should figure out what's wrong with the implementation.

 

[Domenic]

I have only quickly skimmed through this thread, so hopefully I've got it right.

First, no need to first show your other form, and then hide it. You can simply load into memory. Then, when you subsequently call WriteData and unload your other form, use the variable that has been assigned the other form. So something like this...

    Set otherFRM = Application.Run("test.xlsm" & "!showNA")
   
    Load otherFRM
   
    With otherFRM
        For Each ctrl In .Controls
            'etc
            '
            '
            '
        Next ctrl
    End With

    Call WriteData(otherFRM, "CommandButton1")

    Unload otherFRM

Hope this helps!

 

[rigray]

I know it sounds irrelevant, but where is the assignment for wb object variable?
Also, NEWWCWS?
And, tbls_rtu_na, isn't it supposed to be the otherFRM already?
My suggestion: always require variable declaration by using Option Explicit to catch the errors due to missing variables.

Important points that I didn't inquiry before by assuming you already checked them.
I assume test.xlsm is an open workbook that you are trying to run its macro.
And showNA is in the standard module, not in the user form class module.

Let me get it right this one as well: Do you call the Update() sub procedure from the workbook that has the user form which you are trying to reach in this procedure??? So, both workbooks are calling each other's codes? Although I am not able to understand the whole logic, why don't you also pass the otherFRM as the parameter as well? And honestly, where do you use the FRM object that you currently pass to the Update()?

So, there are so many questions in your project that is likely affecting the logic you are trying to build. Unfortunately this prevents to provide more information other than checking the environment and requirements like having showNA in a standard module, and giving the recipe for what you asked for that I am sure it is working without problems. If you could go through all these step by step, then you should figure out what's wrong with the implementation.

I'll try to restate the approach i'm going for. I'm trying to minimize/eliminate any rewriting of existing procedures and userform class modules. workbook 1 - contains multiple userforms. 1 userform is the master userform that calls all sub userforms via buttons, let's call these child userform 1 and 2 for simplicity which are called by the buttons on the master userform. Child userform 1 calls the macros in the macro workbook in order to udpate the database in workbook 1. The update macro in the macro workbook does need to call a different userform in workbook 1 (which isn't currently active) and update this userform with default values based on information selected in the currently open userform.

1) where is the assignment for wb object variable?
-it is in the open workbook 1 standard module not in a userform class module and is passing appropriately.
wb is defined as a workbook and set to thisworkbook in a standard module that initializes some variables for workbook 1, nonetheless i replaced the wb with actual workbook name and it still is providing the same error.

2) And, tbls_rtu_na, isn't it supposed to be the otherFRM already?
- i'm not sure i understand this question. If this clarifies, otherFRM is defined in the workbook one standard module as well.

Public Function showNA() As tbls_RTU_NA
    Set showNA = tbls_RTU_NA 'tbls_RTU_NA.Show
End Function

3) I assume test.xlsm is an open workbook that you are trying to run its macro.
-yes

4) And showNA is in the standard module, not in the user form class module.
-yes, and the code is shown above in 2)

5) Let me get it right this one as well: Do you call the Update() sub procedure from the workbook that has the user form which you are trying to reach in this procedure??? So, both workbooks are calling each other's codes?
- if i understand what you have said, you are almost correct. I call the update sub procedure from the workbook that has the userform. The update procedure will then call a second userform from the initial workbook (that is open of course). This second userform is then updated by this update procedure.

6)why don't you also pass the otherFRM as the parameter as well?
-as i mentioned i have many master workbooks that i'm trying to centralize the code to be called from the macro workbook. IN short, the answer to your question is consistency. All workbooks will not call this specific update function, but i'd like all of the code calling the macro workbooks to be the same. It probably doesn't make sense in explaining from your standpoint, but for instance.

Please let me know if this helps. I really think the code is hung up on calling a userform from the previous workbook that is already open, but I'm not sure.

 

[rigray]

It appears it did not like my workbook name that I manually placed in the code. I renamed the workbook to test.xlsm and it worked. Hmm. I accepted your first solution as the solution; however, both of you had code that was the solution. Thanks to you both.

 

[smozgur]

Glad to hear you got it working.
Thanks for the feedback.