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
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
