Unable to rename/delete the worksheet in Excel - 2007 - to be posted in excel forum

[Sandeepcb]

Dear All,

I have encountered a strange problem while performing the works, all of a sudden I am not able to rename or delete the worksheet. I have checked protect sheet function and found that it is unprotected. I have even checked the excel functions, add-ins, but still unable to crack the problem. Somebody out there please help to fix this problem.

Warm regards
Sandeep

[Sandeepcb]

Hi all,

It seems like none of the forum friends were able to crack the shell. Anybody could suggest me a link which could explain various options. I still trying all the permutations to find a way out. I've never felt like this is an unusual issue which never happened in the life of excel, obviously there is a way out, but those who like to fry their brain please help.

Warm regards
Sandeep

Warm regards
Sandeep

[:) Sixthsense :)]

It's not sheet protection, it is a workbook protection which is protected by applying the Structure Protection of that workbook which will not allow you to add/delete/rename the sheets.

[Sandeepcb]

It's not sheet protection, it is a workbook protection which is protected by applying the Structure Protection of that workbook which will not allow you to add/delete/rename the sheets.

Thank you Sixthsense for your patience in attending this problem. So you mentioned about structure protection and please advice me on how to un protect the structure protection to enable the rename function.

I have tried the following path in excel 2007
Home --> Cells --> Format --> Organize sheets --> Rename sheet.

After the above process I was able to rename the sheet, but still I'm not able to change the name by right clicking on the sheet name area. the above process is bit tedious. I'm pretty sure that this is not the right solution to this problem. I hope Sixthsense you will be able to give the right way out.

[:) Sixthsense :)]

Check Review>>Changes>>(Un)Protect Workbook

[Sandeepcb]

Check Review>>Changes>>(Un)Protect Workbook

I have tried this, but it shows that "Review >> Changes >> Protect Workbook". i.e: it is unprotected at the moment. If it was protected what you said will work for sure. This is a slightly different scenario.

[:) Sixthsense :)]

If possible please share the workbook

[Sandeepcb]

Dear Sixthsense,

This problem is not occured to single workbook, bit its a common problem to all the files. This will happen even when I open a new workbook.

Warm regards
Sandeep

[:) Sixthsense :)]

I read similar thread like this earlier...

May be earlier you might have tried to restrict the sheet deletion via code, Is it right?

[Sandeepcb]

I read similar thread like this earlier...

May be earlier you might have tried to restrict the sheet deletion via code, Is it right?

No such a thing has not happened. I have not used any codes so far. Even I have read similar threads, but the solution was really ambiguous. Thats why I thought of posting this thread.

[:) Sixthsense :)]

Can you please share some screen shots by doing right click on sheet tab for getting better understanding

[Sandeepcb]

Can you please share some screen shots by doing right click on sheet tab for getting better understanding

excel sceenshot_unable to rename.jpg

PFA the screenshot of my working page. Here you will be able to see the greyed out area (rename/delete) and on top you can see that review function.

[:) Sixthsense :)]

I am unable to fix, I will call all members help for this one

[Sandeepcb]

I am unable to fix, I will call all members help for this one

Thanks a lot Sixthsense, for your sincere effort.

[ragulduy]

I've seen code from Masaru Kaji (Colo) that looks like this, to captue an event for inserting a row:

Please Login or Register  to view this content.

I've found that it seems to apply to all workbooks so it's possible your problem could be cause by a similar problem to this and you need to run the reset part to disable it. I guess it is feasible that you could run some code like this to disable a function of excel, which would then apply to your entire application. The numbers in arrIdNum = Array(295, 296, 945) correspond to the controls for inserting a row (by right click, insert menu and something else). So you would need to find the nubmer that corresponds to renaming/deleting sheets. Alternatively you could loop through every control and try resetting each one. However, I think if you open workbook with macros disabled, this functionallity should be turned off, so that would check if this is the issue.

It's a long shot and I'd be suprised if this had happened by accident, although I always thought it would make a good april fools prank to disable every control with this method.

[:) Sixthsense :)]

@ ragulduy,

Yes me too thought the same but OP refused it.

But the thing which you highlighted is TRUE and it needs to be re-enabled via code Or re installing will fix this issue

[Sandeepcb]

Hi Ragulduy,

I never thought the problem was this deep. Could you please show me the process to run this code. I've copied it to macro module 1 and saved it. when I run the macro nithing happened. The third function in the above code runs with a command "shift the cells". So it seems like the problem prevails.

Warm regards
Sandeep

[:) Sixthsense :)]

Hi Sandeepcb,

ragulduy given some hint about what type of issue is that and the code is not the one for your issue.

[john55]

you can try this piece of code to see if you are able to delete sheets

Please Login or Register  to view this content.

[Sandeepcb]

@ ragulduy,

Yes me too thought the same but OP refused it.

But the thing which you highlighted is TRUE and it needs to be re-enabled via code Or re installing will fix this issue

Hi Sixthsense,

Thanks for your kind advise. I will consider re installing the version once i finish my running work. coding to re-enable it is like a dream for me. With all your support we may able to code the right lines.

Warm regards
Sandeep

[ragulduy]

@Sandeep:

Try disabling macros (set your security setting to very high and restart excel) and see if you can delete/rename sheets then. If you still cannot do it with macros turned off then what I suggest will not be the problem.

[john55]

or ...type this into the VBE immediate window

Please Login or Register  to view this content.

and enter

[Izandol]

You may try to restart Excel with Ctrl key pressed and choose Yes to open in Safe Mode. If you may then delete/rename sheets it will be a startup file which is causing this problem. Most likely add-in or perhaps a corrupt .xlb file.

[jaslake]

If John55 suggestion does not work, try this in the Immediate Window

Please Login or Register  to view this content.