Loop thru controls on another workbooks userform

rigray

New Member
Joined
Jul 12, 2011
Messages
9
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.

VBA Code:
'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
 

rigray

New Member
Joined
Jul 12, 2011
Messages
9
also, it was definitely the name of my file. I finished the other coding and changed my workbook back to the name - prject.xls and it gave me the same error as earlier. Interesting.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,270
also, it was definitely the name of my file. I finished the other coding and changed my workbook back to the name - prject.xls and it gave me the same error as earlier. Interesting.
It is surely interesting... The only difference looks to be the prject.xls is an old Excel file type. And honestly, I am not sure why you need to rename test.xlsm as prject.xls. As far as I know, the proper way to rename and xlsm file as an xls file would be "Save As" in the older Excel version.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,718
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top