R
Roger on Excel
[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
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