Sub GetSheetData(xws as worksheet)

Me.txt1.Value = xws.Range("A1").Value
Me.txt2.Value = xws.Range("B32").Value
Me.txt3.Value = xws.Range("A15").Text
'etc etc
'Then return to original subroutine
End Sub


Private Sub cmdShowSheet1Items_Click()

dim ws as worksheet
set ws = worksheets("Sheet1")
call GetSheetData(ws)

end sub



"Roger on Excel" wrote:

[Excel 2003]

I use code with command buttons in userforms. At present I have the
following code

Private Sub cmdShowSheet1Items_Click()
WhichSheet = "Sheet 1"

Dim ws As Worksheet
Set ws = Worksheets("Sheet 1")

'Sheet Details
Me.txt1.Value = ws.Range("A1").Value
Me.txt2.Value = ws.Range("B32").Value
Me.txt3.Value = ws.Range("A15").Text
etc etc

End Sub

This works great at populating text boxes with information from the sheet.

However, I have 10 sheets with different data stored in the same cells (in
the code above), so at present I use 10 buttons each repeating the above code
each time but with a different sheet specified as the source.

Although this works fine, It is very cumbersome (I have hundreds of cells
that I call into the userform).

Alternatively I would like to have the "get sheet details" in a separate sub
routine (since these cell references never change across the sheets), called
up from the button click

I have tried separating this part of the code, but I am not sure how to
handle such a sub routine. Ideally I need something like :

Private Sub cmdShowSheet1Items_Click()


Dim ws As Worksheet
Set ws = Worksheets("Sheet 1")

Call GetSheetDetails???

End Sub


And then somewhere else I have

Subroutine GetSheetData?

Me.txt1.Value = ws.Range("A1").Value
Me.txt2.Value = ws.Range("B32").Value
Me.txt3.Value = ws.Range("A15").Text
etc etc
Then return to original subroutine
End Sub

Can anyone help as this would streamline my code immensely

Thanks,

Roger