I should have mentioned also that you can change the code name of the
worksheet to make it more meaningful. Just bring up the Properties
window and then select the worksheet in the Project window. Change the
Name property of the selected worksheet. This changes the code so you
can use more meaningful names like shBudget rather than just Sheet1.
This change of names has nothing to do with the tab name of the
worksheet, so it won't be affected if the user renames a sheet.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Sun, 13 Dec 2009 13:16:30 -0700, "Robert Crandal"
wrote:

Suppose I define the following subroutine in one of my VBA modules:

Public Sub DoCalculations()
invoiceDate = Sheet1.Range("A1").Value
invoiceTotal = Sheet1.Range("A2).Value
Sheet1.Range("A30).Value = invoiceTotal + 20
End Sub

The above subroutine is limited to using Sheet1, so how
I can I modify this subroutine to accept any sheet codename
as a parameter??? Please keep in mind, I prefer to use
sheet codenames rather than the names on my sheet
tabs, since users can change the names on tabs at any time.

So, I want to modify the subroutine definition as follows:

Public Sub DoCalculations (ByVal SheetCodeName as String)
'
' Revised code here
'
End Sub

The revised routine above will accept a sheet codename in string
format, then I want the same code above to be executed on
an arbitrary/given sheet referenced by codename.

I hope that makes sense...

thank you