Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a subroutine - how to?
[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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a subroutine - how to?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a subroutine - how to?
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a subroutine - how to?
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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a subroutine - how to?
Thanks Sam
i am applying subrotines all over the place now - great !! Roger "Sam Wilson" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calling a VBA add in from a macro subroutine | Excel Programming | |||
Calling a subroutine in a loop | Excel Programming | |||
calling a subroutine outside a spreadsheet | Excel Programming | |||
Function Calling Subroutine | Excel Programming | |||
Calling the Solver via a subroutine | Excel Programming |