passing a variable from sheet to form to another sheet
 

hi to all

My worksheet has a button (btnShowList) that, when clicked, opens a form
(frmShowList).

This form also has a button (btnBuildTeamSheet) that calls a sub
(BuildTeamSheet) that adds a new worksheet, formats it, adds formulas, etc.

Here's my problem. I need to somehow pass the NAME of the original
worksheet to the SUB that builds the new worksheet. I'm just not sure how
to do this.

Thks
anny

passing a variable from sheet to form to another sheet
 

Isn't that sheet the activesheet when the first button is pressed - and I
would assume it is still the activesheet when the macro starts,
so

sName = Activesheet.Name

at the beginning of the macro.

Or have a public variable in a general module and set it in btnShowList


Private Sub btnShowList_Click()
sName = me.name


in a general module at the top

Public sName as String

public variables in a general module have project level scope.


--
Regards,
Tom Ogilvy


"anny" wrote in message
...
hi to all

My worksheet has a button (btnShowList) that, when clicked, opens a form
(frmShowList).

This form also has a button (btnBuildTeamSheet) that calls a sub
(BuildTeamSheet) that adds a new worksheet, formats it, adds formulas,
etc.

Here's my problem. I need to somehow pass the NAME of the original
worksheet to the SUB that builds the new worksheet. I'm just not sure how
to do this.

Thks
anny

passing a variable from sheet to form to another sheet
 

Hi anny,

You could try...

In your BuildTeamSheet() sub, make this declaration...

Dim wksSource As Worksheet

then, before adding the new worksheet, add this line...

Set wksSource = ActiveSheet

to get a reference to the worksheet object. This assumes the subject sheet
is active when the user clicks btnBuildTeamSheet. You can now refer to it
directly in the BuildTeamSheet() sub as wksSource, and use it like this...

wksSource.Range("A1").Value = 'whatever
OR
With wksSource
'do something
End With

HTH
Regards,
Garry