Open User Form in Different Worksheet

[Denny57]

I Have a series of command buttons on Sheet 1 which pass / call information from that W/s to corrsponding User Forms. I have an addtional command button on Sheet 1 which calls information from Sheet 2 into a different User Form (for the purpose of this enquiry - Form 6).

My basic (but growing) knowledge of VBA led me to create the following code to activate the User Form and call the informaton from Sheet 2.

Private Sub UserForm_Initialize()
Worksheets("Sheet 2").Activate
TextBox1.Value = Range("B21").Text
etc.....

However, this caused Form 6 to display on Sheet 2.
In time I am hoping to use a single sheet from which to call / display multiple User Forms , but for the present is there a simple way to instruct the Form 6 to open on Sheet 1 whilst calling the required information from Sheet2?

Thanks in advance

 

[DanteAmor]

It doesn't matter which sheet you open the form on, in fact, it's best to open the form on a generic sheet.
What you should put in your code is the reference to the sheet you need, for example:

Private Sub UserForm_Initialize()
  TextBox1.Value = Sheets("Sheet 2").Range("B21").Text
End Sub

Always in any range or cell you must precede the reference to the sheet from which you need the information or where you are going to put information, example:

Private Sub CommandButton1_Click()
  Sheets("Sheet1").Range("D10").Value = TextBox1.Value
End Sub

 

[NdNoviceHlp]

DanteAmor seems to have resolved your issue but I though that I would add it's also a good idea to fully qualify your textbox reference as well in case you have a userform hidden while showing another ie. Sheets("Sheet1").Range("D10").Value = Userform1.TextBox1.Value You might also be want to search for the effects of Application.ScreenUpdating. HTH. Dave

 

[Denny57]

It doesn't matter which sheet you open the form on, in fact, it's best to open the form on a generic sheet.
What you should put in your code is the reference to the sheet you need, for example:

Private Sub UserForm_Initialize()
  TextBox1.Value = Sheets("Sheet 2").Range("B21").Text
End Sub

Always in any range or cell you must precede the reference to the sheet from which you need the information or where you are going to put information, example:

Private Sub CommandButton1_Click()
  Sheets("Sheet1").Range("D10").Value = TextBox1.Value
End Sub

Dante

I created the following basic generic commands and code baseed on your solution and which function exactly as the 'notes for each in a test file

Test Code

Private Sub cmdAddRecord_Click()
'This adds details from a User Form in Sheet 1 to cells in Sheet 2
    Sheets("Sheet2").Range("A1").Value = txtTextBox1.Value
    Sheets("Sheet2").Range("A2").Value = txtTextBox2.Value
End Sub

Private Sub cmdCallRecord_Click()

'This calls information from the Data sheet and populates the User Form
    txtTextBox1.Value = Sheets("Sheet 2").Range("A1").Text
    txtTextBox2.Value = Sheets("Sheet 2").Range("A2").Text
End Sub

So I created a new worksheet called "Forms" and moved the Command Buttons to open all 6 of the User Forms on this one sheet.

I then changed the form Initialize code to the required syntax with the result that the form opens on the "Forms" worksheet as expected but all the fields are empty.

Please can you see what is incorrect in the following code which might be preventing the fields from being populated.

Private Sub frmReturnsAndAccount_Initialize()

    txtEuromillionsRegular.Value = Sheets("Returns & Account").Range("B21").Text
    txtEuromillionsExtra.Value = Sheets("Returns & Account").Range("B22").Text
    txtEuromillionsAdditional.Value = Sheets("Returns & Account").Range("B23").Text
    txtLottoRegular.Value = Sheets("Returns & Account").Range("B5").Text

End Sub

I feel that there might be something in my file which prevents data from populating user forms with data from other sheets as I have tried similar code to your solution with similar results

 

[DanteAmor]

Please can you see what is incorrect in the following code which might be preventing the fields from being populated.

For Activate or Initialize events you must use UserForm, you must not change the name.

Here's another way to simplify the sheet reference:

Private Sub UserForm_Activate()
  Dim sh As Worksheet
  Set sh = Sheets("Returns & Account")
  txtEuromillionsRegular.Value = sh.Range("B21").Value
  txtEuromillionsExtra.Value = sh.Range("B22").Value
  txtEuromillionsAdditional.Value = sh.Range("B23").Value
  txtLottoRegular.Value = sh.Range("B5").Value
End Sub

Or this way:

Private Sub UserForm_Initialize()
  With Sheets("Returns & Account")
    txtEuromillionsRegular.Value = .Range("B21").Value
    txtEuromillionsExtra.Value = .Range("B22").Value
    txtEuromillionsAdditional.Value = .Range("B23").Value
    txtLottoRegular.Value = .Range("B5").Value
  End With
End Sub

Find other tips in this video:

 

[Denny57]

Dante

Many thanks for all your help.