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
|
Similar Threads
