Setting the code sheet name

Resolved · Medium Priority · Version 2007

Fri 14 Jan 2011, 15:33 Delegate Rishi said...

Rishi has attended:
Excel VBA Advanced course

Setting the code sheet name

I like to refer to sheets using the "(Name)" property of the sheet which is set in the VBAproject window. E.g. can set to shtEmployee and then reference the first cell for example by shtEmployee.range("A1")

The advantage of this is that if a user changes the name of the sheet in Excel, all the VBA code stil works.

However, when creating a new sheet with VBA I don't know how to set this name property to something which I can then refer to later.

Wed 19 Jan 2011, 10:39 Trainer Anthony said...

RE: setting the code sheet name

Hi Rishi, thanks for your query.

IF you don't specific the name of the sheet - say by just using Range("A1") instead of Sheets(shtEmployee).Range("A1) - the code will run on whatever sheet has the focus. In order for you to work on a specific sheet, however, even if the sheet has been renamed, you'll need to have a unique identifier on that worksheet. For example, a particular heading or code. Then you loop through the workbook finding that unique identifier, set shtEmployee to be equal to the name of that found sheet and then run your code as normal. Fiddly, but it can be done.

Hope this helps,

Anthony

Wed 19 Jan 2011, 11:23 Delegate Craig said...

RE: setting the code sheet name

Hi,

When in the Excel Objects Screen it shows you the sheet name property that has been allocated to all sheet objects, this stays unique even when the worksheet is renamed.

So surely you could name these via the properties and then call them that way?

Kind regards,
Craig

Wed 19 Jan 2011, 11:49 Edited on Wed 19 Jan 2011, 12:31 Delegate Craig said...

RE: setting the code sheet name

Forgot to mention that everytime you add a new sheet to the workbook, it will be given the next available object sheet number of if a sheet has been deleted previously it will take that number as it's object Name. So you should be able to work out looking at your object window what the new object name will be using this theory.

Doing a sheet count should also get you the last object sheet name, if again you have no gaps in your sheet numbers due to deletions.

The code to do this is as follows:-
----
Sub check()
Sheets(Sheet2.Name).Select

End Sub
----
Sheet2 = the object sheet name.

Hope this helps.
Craig