Hey guys,
I was wondering - is it possible to dynamically chance the name of at a worksheet that already exists based on the contents of a cell on another sheet?
I.e. If the name of a sheet is linked to the value of the Cell A1 in the "Data" sheet and I change A1 to read "Group1" it changes the name of the sheet as well?
Thanks for your time,
Rich
You want to change sheet name or you want to chenge reference to other sheet based on A1 sheet name?
I'd like the name of the sheet to change
A change to a sheet name requires VBA. VBA can detect when you enter a new value into A1. However, is A1 a formula, or something you type in? I have assumed that you type it in, but if it's a formula we need a modified solution.
The tricky aspect is that even if VBA detects a change, it will see the new value but not the value it was before the change. Therefore you have to capture the "before" value of A1 somewhere else. I have reserved the cell A2 for this purpose but you can select any cell you want. It can even be on a different sheet, even a hidden sheet reserved just for that purpose.
The attached file gives an example of how this works.
Hi 6String,
Thanks for the help, but for some reason my version of it isn't working.
Unfortunately, the nature of the data on the spreadsheet means I can't send you the sheet, but I have pasted in the modified version of the entirety of the VB I am using in this project below (the additional code is a button activated hide and reveal of certain rows)
Have I made an error somewhere in my editing?
Please Login or Register to view this content.
Thanks for your time so far!
Rich
[bold] Mod's note - code tags added for you - this time[/bold]
Last edited by FDibbins; 10-13-2013 at 11:26 AM.
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Sorry FDibbins - will make sure in future
Yes, see error correction below in red. The name of this Sub is always Worksheet_Change regardless of the name of the worksheet:
Please Login or Register to view this content.
Also, what module did you put the code in? Is it in the module for the sheet called DATA (hint: It has to be or it won't work)?
Also, although it is not wrong, it is not necessary to put "DATA!" in the range references for code within the Data worksheet.
OK - I've attached an image of the code in situ - unfortunately it still isn't working...
Code.jpg
Any further suggestions for what might be causing the issue?
I see another error that I have to take the blame for. Not sure what happened because I tested it. There is a line of code in the wrong place. This line will never be executed here. If this Sub runs once, then it will never run again until you restart Excel. That may be what happened to you.
You can clear this problem out by going to the code window, hitting CTRL-G to bring up the Immediate window, then typePlease Login or Register to view this content.
and then hit ENTER.Please Login or Register to view this content.
Or you can simply close Excel then restart it and then open your file.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks