# Open User Form in Different Worksheet



## Denny57 (Dec 16, 2022)

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 (Dec 16, 2022)

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 (Dec 17, 2022)

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 (Dec 17, 2022)

DanteAmor said:


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


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 (Dec 17, 2022)

Denny57 said:


> 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 (Dec 17, 2022)

Dante

Many thanks for all your help.


----------

