renamed chart sheets

[orsm6]

Hi all - at work we have a workbook containing a dashboard sheet. there are about 50 buttons there which have simple macros attached that take you to the chart sheet that's coded in the macro. we had issues in the past of the sheet name e.g. chart1 being renamed to something like chart101 and every other chart sheet along the bottom foolows suit e.g. chart102, chart103 etc etc.

I went and renamed every single sheet with something like P1, P2 etc and re-wrote the macros which took hours..... and now none of the macros work because all of the sheet names have been renamed back to chart 177, chart 178 and so on.

what is the best way around this? I feel i have another few hours work ahead of me, but I need a way to stop the renaming happening or something as this is annoying as hell.

TIA

 

[johnnyL]

Use the code name of the sheet in your macros instead of the sheet name. The code name will always be the same, whether the sheet name changes or not.

 

[orsm6]

Use the code name of the sheet in your macros instead of the sheet name. The code name will always be the same, whether the sheet name changes or not.

I'm sure i tried that.. but will certainly look at it again in case i hadn't. ta mate.

 

[johnnyL]

Like I said, the sheet code name will normally not change, they can be, but this is not normal for everyday user.

The con to using the sheet code name is that it is read only, as far as I know, in other words, you can use the sheet code name to read data from that sheet, but you won't be able to use it to write data to that sheet.

That is easily handled by 'asking what the sheet name is' that is currently attached to the sheet code name, from then on you can use the sheet name to handle reads and writes.

Sheets("RenamedSheet1").Range("A1")            ' is an example of using the sheet name
Sheet1.Range("A1")                    ' is an example of using the sheet code name

CurrentSheetNameForSheet1CodeName = Sheet1.Name        ' is an example of asking for the sheet name that is currently attached to code name sheet1

 

[orsm6]

View attachment 45401View attachment 45402View attachment 45403


Like I said, the sheet code name will normally not change, they can be, but this is not normal for everyday user.

The con to using the sheet code name is that it is read only, as far as I know, in other words, you can use the sheet code name to read data from that sheet, but you won't be able to use it to write data to that sheet.

That is easily handled by 'asking what the sheet name is' that is currently attached to the sheet code name, from then on you can use the sheet name to handle reads and writes.

Sheets("RenamedSheet1").Range("A1")            ' is an example of using the sheet name
Sheet1.Range("A1")                    ' is an example of using the sheet code name

CurrentSheetNameForSheet1CodeName = Sheet1.Name        ' is an example of asking for the sheet name that is currently attached to code name sheet1

i've tried

sheets("chart3").activate

and with select and they both do not work. The macro is launched from a button on a different sheet to where the chart is.

 

[johnnyL]

I will whip up some code for you tomorrow to test, I have to get some sleep right now.

 

[orsm6]

I will whip up some code for you tomorrow to test, I have to get some sleep right now.

sorry i have actually solved it i think

Chart3.Activate

 

[johnnyL]

sorry i have actually solved it i think

Chart3.Activate

Yep, that would be an example of using the code name.

Let us know how it goes.

 

[orsm6]

Yep, that would be an example of using the code name.

Let us know how it goes.

It works.

 

[johnnyL]

Glad to hear it.