Calling a subroutine - how to?

  • Thread starter Thread starter Roger on Excel
  • Start date Start date
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
 
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
 
Dear Sam,

Thanks - I will try this later when I am home and give you some feedback.

I have a couple of questions though;

I noticed you use a different descriptor (xws rather than ws) in the
subroutine. Why is that?

Also, Should I place this code in the same location as the command button
codes for the userform?

Roger


Sam Wilson said:
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 said:
[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
Click to expand...
Click to expand...
 
Hi,

I used xws purely so that I didn't have ws referred to in both subs - in the
first sub ws is declared as a variable, in the second I needed another
worksheet which I could have called anything I wanted. xws just popped in to
my mind. It would work fine with ws declared in both is you wished, but it's
perhaps not best practice with one eye on future scalability.

Place the code in a module but change "Sub GetSheetData(...)" to "Public Sub
GetSheetData(...)"

Sam



Roger on Excel said:
Dear Sam,

Thanks - I will try this later when I am home and give you some feedback.

I have a couple of questions though;

I noticed you use a different descriptor (xws rather than ws) in the
subroutine. Why is that?

Also, Should I place this code in the same location as the command button
codes for the userform?

Roger


Sam Wilson said:
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 said:
[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
Click to expand...
Click to expand...
Click to expand...
 
Thanks Sam

i am applying subrotines all over the place now - great !!

Roger

Sam Wilson said:
Hi,

I used xws purely so that I didn't have ws referred to in both subs - in the
first sub ws is declared as a variable, in the second I needed another
worksheet which I could have called anything I wanted. xws just popped in to
my mind. It would work fine with ws declared in both is you wished, but it's
perhaps not best practice with one eye on future scalability.

Place the code in a module but change "Sub GetSheetData(...)" to "Public Sub
GetSheetData(...)"

Sam



Roger on Excel said:
Dear Sam,

Thanks - I will try this later when I am home and give you some feedback.

I have a couple of questions though;

I noticed you use a different descriptor (xws rather than ws) in the
subroutine. Why is that?

Also, Should I place this code in the same location as the command button
codes for the userform?

Roger


Sam Wilson said:
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



:

[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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top