×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Work sheet names and VBA macros in Excel

Work sheet names and VBA macros in Excel

Work sheet names and VBA macros in Excel

(OP)
Hi,

I have a question about worksheet names and VBA macros in Excel. Is it possible to change the name of a worksheet in Excel (the name that can be seen in the bottom of the page) whiteout changing the VBA macros connected to them?

Thanks in advance/ Markus

RE: Work sheet names and VBA macros in Excel

Markus,

Yes!  Many of the better Excel VBA programming books recommend this, since your macro code will break, otherwise, if the user changes a sheet tab name.  Here's how:

In the Visual Basic Editor (VBE), select the worksheet your macro code interacts with from the Project window.  Notice that the Name property for the worksheet (Properties window) is the same as the current sheet tab name.  Highlight this name and change it to whatever you want.  Now your code can reference the worksheet as it would any other object.  Here is some example code, where the worksheet name has been changed to Main as instructed above:

CODE

Sub MyMacro()
Dim Total As Single

   Total = Main.Cells(4,1).Value
   ...
   ...
End Sub


Regards,
Mike

RE: Work sheet names and VBA macros in Excel

(OP)
Thanks for applying but it did not really answer my question. I have already made my VBA code, which is referring to "Sheet1", "Sheet2" and so on. I would like to change my "Sheet1" to "Calculation" and "Sheet2" to "Data" without changing my code. Is this possible?

RE: Work sheet names and VBA macros in Excel

Normally code is not Worksheet or even Workbook dependent unless you make it so.  Even then, internally, Excel remembers the original name for certain calls.  The best test is to rename the sheet and run your macro to see what happens.

RE: Work sheet names and VBA macros in Excel

Markus,

If you have code like

CODE

Worksheets("Sheet1").Range("A1")
in your macros, and you change the worksheet tab from Sheet1 to Calculation your macro will certainly choke.  However, you should be able to do a Find/Replace to make the process of changing the sheet name in your code somewhat less painful.


Regards,
Mike

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources