renaming back the sheet index of a worksheet while retaining its original name

LFKim2018

Active Member
Joined
Mar 24, 2018
Messages
267
I came across an article that prevent a worksheet from being deleted without using sheet protect. IN THE WORKSHEET BEFORE DELETE SUB> it copied & renamed the sheet - and after the orig sheet was deleted - it renamed back the orig name (nice trick)... but the sheet index is now different... my query is how to rename back the sheet index and still retain the worksheet name

example - original sheet index= sheet20; sheet name = "Entry Form"
assuming the workbook's last index = sheet32
after the process> the new sheet index will be sheet33 sheet name (still) = "Entry Form"
the sheet20 will be gone from the list
How could I change it back to sheet20>"Entry Form"

Many Thanks
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
You are making your question hard to follow with too many underlinings, bold and italic :)

You can amend the sheet code name in VBA window \ View \ Properties \ click on sheet in left window \ amend Name in properties window to Sheet20
 
Last edited:
Upvote 0
You are making your question hard to follow with too many underlinings, bold and italic :)

You can amend the sheet code name in VBA window \ View \ Properties \ click on sheet in left window \ amend Name in properties window to Sheet20

Thank you for your reply.
sorry for being too emphatic..
your solution is manual, what I need is the vba code...
thanks again
 
Upvote 0
What code do you currently have?
 
Upvote 0
Mr Fluff
VBA window \ View \ Properties \ click on sheet in left window \ amend Name in properties window to Sheet20
I want to know how to write in vba the above procedure so that it can be used in a program of my workbook as subroutine.
Thank you for your reply
 
Upvote 0
What code do you currently have that creates a copy of the sheet in the Worksheet_BeforeDelete() event?
 
Upvote 0
Mr Fluff

this is the code:

inside Sheet20 "pc"

Private Sub Worksheet_BeforeDelete()
Dim MyName As String
Dim TempName As String
MyName = ThisWorkbook.ActiveSheet.Name
TempName = "TEMP"
ThisWorkbook.ActiveSheet.Name = TempName
ThisWorkbook.ActiveSheet.Copy _
After:=Sheets(ThisWorkbook.ActiveSheet.Index)
ThisWorkbook.ActiveSheet.Name = MyName

kindly add here - the code to rename back the sheet index while retaining the original sheet name (if at all possible)
End Sub

it copied & renamed the sheet - and after the orig sheet was deleted - it renamed back the orig name ... but the sheet index is now different...

original worksheet name = "pc", sheet index = sheet 20
after the above sub procedure> sheet name still is "pc", but the sheet index is now 32 (the last index of the workbook);
sheet20 was deleted from the list.
I hope this clears up matter

THANK YOU VERY MUCH FOR YOUR TIME
 
Upvote 0
Try
Code:
Private Sub Worksheet_BeforeDelete()
Dim MyName As String
Dim TempName As String
Dim cnName As String
cnName = Me.CodeName
MyName = Me.Name
TempName = "TEMP"
Me.Name = TempName
ThisWorkbook.VBProject.VBComponents(Me.CodeName).Name = "cntemp"
Me.Copy After:=Me
ThisWorkbook.ActiveSheet.Name = MyName
ThisWorkbook.VBProject.VBComponents(ThisWorkbook.ActiveSheet.CodeName).Name = cnName
End Sub
BUT for this to work you will have to goto > File > Options > Trust Center > Trust Center settings > Macro Settings > Select "Trust access to the VBA project object model"
And do that for every computer the code will be running on.
 
Upvote 0
Try
Code:
Private Sub Worksheet_BeforeDelete()
Dim MyName As String
Dim TempName As String
Dim cnName As String
cnName = Me.CodeName
MyName = Me.Name
TempName = "TEMP"
Me.Name = TempName
ThisWorkbook.VBProject.VBComponents(Me.CodeName).Name = "cntemp"
Me.Copy After:=Me
ThisWorkbook.ActiveSheet.Name = MyName
ThisWorkbook.VBProject.VBComponents(ThisWorkbook.ActiveSheet.CodeName).Name = cnName
End Sub
BUT for this to work you will have to goto > File > Options > Trust Center > Trust Center settings > Macro Settings > Select "Trust access to the VBA project object model"
And do that for every computer the code will be running on.

Mr Fluff
Thank you for your reply.
ThisWorkbook.VBProject.VBComponents(Me.CodeName).Name = "cntemp"
when I ran the sub - the above bold italic phrase light up as error - what could be the problem?

many thanks
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top