Using Userforms

[Richard]

I have created a userform which works well and places the
resulting answers into the correct cells. However, when i
launch the userform from a different sheet in the
workbook, i find that it tries to look up and write to
that sheet, rather than the one it is supposed to. Can you
fix the sheet the userform relates to?

Thanks

Richard

 

[Harald Staff]

Hi Richard.

Sure:
ThisWorkbook.Sheets(2).Cells(3, 6).Value = Me.Textbox1.Text

 

[Harald Staff]

Hi Richard.

Sure:
ThisWorkbook.Sheets(2).Cells(3, 6).Value = Me.Textbox1.Text

or
ThisWorkbook.Sheets("Sheet2").Cells(3, 6).Value = Me.Textbox1.Text
if you want to identify by name and not by location.

HTH. Best wishes Harald
Followup to newsgroup only please

 

[Richard]

Thanks for your answers - can i put the worksheet
reference into the properties box relating to the
userform, or do i have to put it in the main area?

 

[Harald Staff]

Ok, you're not using VBA code but the ControlSource / RowSource kinda
properties. Then simply change each of them from
A2
to
Sheet1!A2
and forget my earlier suggestions.

 

[Beto]

I have created a userform which works well and places the
resulting answers into the correct cells. However, when i
launch the userform from a different sheet in the
workbook, i find that it tries to look up and write to
that sheet, rather than the one it is supposed to. Can you
fix the sheet the userform relates to?

You can reference a cell by its Range, Sheet and Workbook.

Ex:
1.- Workbook("MyWorkbook.xls").Sheets("MySheet").Range("A1")
2.- Sheets("MySheet").Range("A1")
3.- Range("A1")

1.- Will go to the exact position, taking into account Workbook name,
Sheet name and Range position.
2.- Will take into account Sheet name an range, so if you're in a
different workbook and the sheet name exists, it will write there.
3.- Will only go and see the Range on the activeworkbook, activesheet.

Regards,