CODEWITHSUNDEEP
excelvba
Wouter Adolfsen
Wouter Adolfsen

Reputation: 23

How to dynamically refer to userforms

I'm trying to avoid hardcoding a userform's name into subs that fill out and later retrieve data from said userform. The sub I've included here is the one that is supposed to put the values from the userform into cells on a sheet.

(I'm still pretty new to this)

I've tried several approaches from other solutions to similar problems on stackoverflow to no avail. The closest I've come to was Screen.ActiveForm but sadly that's for Access, not excel.

I've also tried creating a public object variable but couldn't get that to work either.

The only thing that worked was hardcoding Userform1 into the subs, but that seems like an inefficient way to go about this. I don't see why someone would have to write a new module every time they setup another userform.

''''''''''''''''' Userform '''''''''''''''''''

Private Sub CommandButton1_Click() ' Ok

' Set CurrentForm = Me.Caption
Interactive_Userforms.EditAdd

End Sub


''''''''''''''''' Module '''''''''''''''''''


Public StartRow, StartColumn, n_TextBoxes As Integer
Public sheet As String
' public CurrentForm as object

Sub EditAdd()

Dim i, j, k  As Integer

i = StartRow
j = StartColumn
k = 1

Do While screen.activeform.Controls("TextBox" & k).Value <> ""

    Worksheets(sheet).Cells(i, j).Value = screen.activeform.Controls("TextBox" & k).Value

    Debug.Print (Worksheets(sheet).Cells(i + StartRow, j + StartColumn).Value)

    i = i + 1
    k = k + 1

Loop

End Sub

What I would expect to be able to do is to eventually have any userform be able to call this sub to retrieve the values on its textbox controls and nicely put them into a worksheet.

Upvotes: 1

Views: 598

Answers (2)

pgSystemTester
pgSystemTester

Reputation: 9897

I feel like this answer is too simple given your code, but it seems like you should be able to just include a parameter for the macro to capture the Form calling it.

Something like...

Private Sub CommandButton1_Click() ' Ok

Call Interactive_Userforms, Me

End Sub

and then your public module would have...

Sub EditAdd(myUserForm As UserForm)
'... etc....
        Worksheets(Sheet).Cells(i, j).Value = myUserForm("TextBox" & k).Value
'... etc.

End Sub

Upvotes: 1

Bilal
Bilal

Reputation: 357

If you just want EditAdd Sub to be called by userforms, you can change your EditAdd signature to Sub EditAdd(Form as Object) or Sub EditAdd(Form as Userform) then you can pass userform instance itself as "Me" when you call EditAdd

Private Sub CommandButton1_Click() ' Ok

' Set CurrentForm = Me.Caption
Interactive_Userforms.EditAdd(Me)

End Sub

Upvotes: 3

Related Questions