VBA - UserForm Populate table on hidden or non active sheet.

[AWM21]

Hello, I'm having an issue and I can't find a simple solution.

I have (2) UserForms that each fill tables in two separate sheets. Problem is, I want to run the forms from a Dashboard and the forms only fills cells on the sheet I'm active on.
I need UF1 to fill sheet2 and UF2 to fill sheet3 all while I'm viewing/active on the Dashboard Sheet1. Overall, I need to know what code to use to force UserForm entries to fill a specific table, regardless of what sheet the user is active on.

Thanks for your help. ?

Here is my current UF code.
UserForm1:

Private Sub submitBtn_Click()

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = Desc.Text
Cells(erow, 2) = Amount.Text
Cells(erow, 3) = Qty.Text

Clear_Form

End Sub

Private Sub UserForm_Click()

End Sub

Sub Clear_Form()

For Each ctrl In Me.Controls

    Select Case TypeName(ctrl)
        Case "TextBox"
            ctrl.Text = ""
       
    End Select

Next

End Sub

Private Sub UserForm_Initialize()

End Sub

UserForm2:

Private Sub CommandButton1_Click()

erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = Desc.Value
Cells(erow, 2) = Freq_Month.Value
Cells(erow, 3) = Amount.Value

Clear_Form

End Sub

Sub Clear_Form()

For Each ctrl In Me.Controls

    Select Case TypeName(ctrl)
        Case "TextBox"
            ctrl.Text = ""
           
    End Select
   
Next

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()

End Sub

 

[dmt32]

Hi,
solution should be straightforward - when working with non active sheet you need to qualify the range(s) to have the workbook and worksheet associated with them.

Private Sub submitBtn_Click()

    With Sheet2
    erow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        .Cells(erow, 1) = Desc.Text
        .Cells(erow, 2) = Amount.Text
        .Cells(erow, 3) = Qty.Text
    End With

    Clear_Form

End Sub

with your code I have enclosed the ranges within a With statement & you should note the period (.) or full stop in front of .Cells.

you can read more here:excel-vba Tutorial => Qualifying References

Dave

 

[AWM21]

Hi,
solution should be straightforward - when working with non active sheet you need to qualify the range(s) to have the workbook and worksheet associated with them.

Private Sub submitBtn_Click()

    With Sheet2
    erow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        .Cells(erow, 1) = Desc.Text
        .Cells(erow, 2) = Amount.Text
        .Cells(erow, 3) = Qty.Text
    End With

    Clear_Form

End Sub

with your code I have enclosed the ranges within a With statement & you should note the period (.) or full stop in front of .Cells.

you can read more here:excel-vba Tutorial => Qualifying References

Dave

Thank You Dave! This worked perfectly. I appreciate the help, and the link!
Thanks again!

 

[dmt32]

Thank You Dave! This worked perfectly. I appreciate the help, and the link!
Thanks again!

welcome - appreciate the feedback

Dave