Change Sheet CodeName and try to access it during running code does not work

Dear all,

I have the following problem:

1. Within my Excel-VBA-Code I refer to the internal code name of a sheet (e.g. Calculation. Range(rgRange))
2. If the user changes the sheet for which I programmed the reference “Calculation” I change the internal code name via ActiveWorkbook.VBProject.VBComponents(wsPVK1.CodeName).Name = "Calculation" (and provide the original sheet with another CodeName before)

Everything works fine until I want to access Calculation.xxx in the same procedure directly after changing it. The CodeName remains the same as before and the change becomes only valid after starting the/a procedure again.

What to do to make changes valid immediately?
Recompile within running code? (How?)
Wait for completion of change? (How?)

Thank you for ideas…

OK,
the whole code of the workbook contains more than 500 lines of code, so I cannot provide it to you. But I try to explain my problem better.

I have several worksheets where the code refers to the internal codename “Calculation”. However, the user can duplicate this sheet (also several times), rename it, etc. BUT only one of these duplicated sheets can serve as input for the code.

So I programed a UserForm in which the user can select the valid sheet. Since I use “Calculation” as internal CodeName in my program, I have to change the internal CodeName of the selected sheet to “Calculation”.

However: this change is not valid during the currently running code. Its becomes only valid, if I start the Sub or another Sub again!!

Hope, you can follow my explanations

Hi Arkadi,
yes, why complicated if it works easy

This idea stepped in my brain yesterday night also...

Anke

Why? In the first Version of my Excel only ONE sheet was allowed and Independent of renaming through the user I could Access it thrue its CodeName "Calucaltion". Now users are allowed to have several Sheets and they can rename them all! and select which sheet should be input-sheet for the program. In consequence I am no more able to adress data in this sheet by its Codename.

This is my Explanation. If I was wrong, please explain.

P.S.: now I will store the CodeName of a selected sheet, retrieve the current Name (Independent of renaming ist in between) and address the selected sheet by its Name (since it is not possible to address it vial variable CodeName)