On 2002-03-13 15:27, karraj wrote:
I have got some legacy VBA code that has sheet names hardwired in the code. So, whenever any of the Worksheets name in the workbook is changed, I need to change that in the VBA code.
Is there a way to change the VBA code so that whenever user changes teh name of the worksheet, it is automatically changed in the code!
Thanks,
K
Not really....BUT what you should do is make
a note of the worksheets CODE name and then
do a Replace........
What I'm trying to explain is that the Tab
name / Sheet name has a Code name...if you
are in the VBA project explorer you will
see these names to the left of the Bracketed
sheet/tab name. eg.
Sheet1(Mysheet1 name on the tab)
Sheet2(Mysheet2 name on the tab)
etc. The code name remains the same even if
you change the sheet/Tab name.
So you should replace ALL references to a sheet
name to the code name eg using the above as
an example your current code may read......
Sheets("mysheet1 name on the tab").Select
ActiveCell.FormulaR1C1 = "23"
Sheets("mysheet2 name on the tab").Select
ActiveCell.FormulaR1C1 = "23"
Sheets("mysheet1 name on the tab").Range("B14").FormulaR1C1 = "66"
changes to;
Sheet1.Select
ActiveCell.FormulaR1C1 = "23"
Sheet2.Select
ActiveCell.FormulaR1C1 = "23"
Sheet1.Range("B14").FormulaR1C1 = "66"
HTH
Ivan