Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Setting the code sheet name
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Setting the code sheet name
Resolved · Medium Priority · Version 2007
Reply Fri 14 Jan 2011, 15:33Delegate 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.
For upcoming training course dates see: Pricing & availability
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
Reply Wed 19 Jan 2011, 11:23Delegate 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
Reply Wed 19 Jan 2011, 11:49
Edited on Wed 19 Jan 2011, 12:31Delegate 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
|