Code to delete sheets and rename sheets in a protected workbook?

**leaning** · 08-31-2011, 03:34 PM

Hello!

Protected workbook with button on each sheet to create copies of that sheet.

If user makes a mistake with copied sheet sheetname, user can't delete that copy without unprotecting the workbook.

I was thinking a Delete Sheet button on the Cover sheet. "Here is a list of sheets in the workbook. Which one(s) do you want to delete? Are you sure? OK. ImportantSheet cannot be deleted, or Sheet NotImportant successfully deleted."

And then another button for "You wish to change the name of a sheet. Please select the sheet. You cannot change the name of ImportantSheet, or Please input new name for NotImportant Sheet. NotImportant sheet name changed successfully."

Anybody have any code like that I can use?

Respectfully,

Lost

**leaning** · 08-31-2011, 11:25 PM

OK, I muddled together some code to delete sheets.

Please Login or Register  to view this content.

It needs a check-to-see-if-the-entered-sheetname-exists-in-the-workbook error checking and probably other stuff, but I don't know how to do that. It would also be better if the user could select a sheetname from a list rather than having to remember the sheetname and then type it into the inputbox.

I still don't have a good rename-a-sheet code, but I am hopeful.

Thanks!

Lost

**MaczaQ** · 09-01-2011, 07:38 AM

Please try provided by me example. I hope you will find some useful ideas for your project.

Best Regards
MaczaQ

**MaczaQ** · 09-01-2011, 07:39 AM

now correct file

**jaslake** · 09-01-2011, 11:00 AM

Hi leaning
Try the attached. The code allows for this:

user could select a sheetname from a list

and this

a check-to-see-if-the-entered-sheetname-exists-in-the-workbook

and this

rename-a-sheet code

Let me know of issues.

**leaning** · 09-02-2011, 08:48 AM

maczaq,

I like it!

Some things I found:

Create New Sheet: Cancel brings up the debugger. Naming the sheet "?" or any of the other reserved characters brings up the debugger.

Rename sheet: Cancel brings up the debugger.

Delete sheet: I would like a "Are you sure you want to delete: Sheet2" question. Also, say Sheet2 is super important. Can you add that to the code so that if they try to delete Sheet2, it says "Sheet2 is needed and will not be deleted."

That's all I got. I really wish I could whip something up like that without having to spend hours Googling looking for code examples that might work.

Thanks!

Lost

**jaslake** · 09-02-2011, 09:32 AM

Hi lost

Sorry 'bout that...file attached.

**leaning** · 09-02-2011, 09:51 AM

Jaslake,

The workbook came up asking for a password, so I just copied your code to a new workbook.

With your code, I can delete the sheet with the button (!) and then there is no code no more.

Also, it doesn't like sheets named "?" (debugger pops up).

It looks good!

Thanks!

Lost

**jaslake** · 09-02-2011, 10:42 AM

Hi Lost

You provided the password

Please Login or Register  to view this content.

Please notice the sheet naming convention in the Book I posted

Please Login or Register  to view this content.

This I'll look at

it doesn't like sheets named "?" (debugger pops up).

Use the book I provided...use the password you provided and the sheet naming convention you provided...let me know if you have issues.

**jaslake** · 09-02-2011, 05:18 PM

Hi Lost

The debugger pops up when the user enters an invalid character in the sheet name. The message is on point...what would you prefer to see?

**leaning** · 09-06-2011, 09:02 AM

jaslake,

It does work. I just don't like that if the user enters a ? for a sheetname, the error 1004 pops up. If the user doesn't enter anything (leaves it blank) and then clicks Cancel, the sheet name becomes "False". If it is the only sheet, and you click Rename, then Cancel, runtime 13 pops up.

Many ways for the user to wind up all up in Visual Basic with a blinking cursor.

(!)

Lost

**MaczaQ** · 09-06-2011, 09:35 AM

Hi,

please check updated code in my previous solution

'updated; catch wrong name and display worning

Create New Sheet: Cancel brings up the debugger. Naming the sheet "?" or any of the other reserved characters brings up the debugger.

'updated; catch error data and display worning

Rename sheet: Cancel brings up the debugger.

'udpated; check if current sheet could be deleted (for test try to delete Sheet1)

Delete sheet: I would like a "Are you sure you want to delete: Sheet2" question. Also, say Sheet2 is super important. Can you add that to the code so that if they try to delete Sheet2, it says "Sheet2 is needed and will not be deleted."

Best Regards
MaczaQ

**leaning** · 09-06-2011, 11:11 AM

maczaq,

Your code didn't bring up the debugger once! (I like that

)

For the workbook I am using, there are twenty sheets that can't be deleted or renamed. Since they can't be altered, I don't even want them to show up on the sheet list.

So, if the workbok had VitalSheet1, VitalSheet2, TrashSheet1, TrashSheet2, etc, only the TrashSheet's name should show up in the list and be able to be deleted/renamed.

I tried to modify your code, but it didn't work for me:

Please Login or Register  to view this content.

Lost

**MaczaQ** · 09-06-2011, 03:20 PM

your modification is quite good and should works ok in this way:

Please Login or Register  to view this content.

but you have to remember to edit below part of code for determinate what sheets you want to protect:

Please Login or Register  to view this content.

Best Regards
MaczaQ

**leaning** · 09-06-2011, 11:24 PM

All
Thanks almost entirely to your work, this is what I came up with. Hopefully, you will see the contributions of maczaq, jaslake, shg, and I even threw some stuff in there.

(I know that I was harping on error-handling, but as you can see, I want to catch the error after the user inputs the data, but before that data is actually used.)

Before I call this thread solved, I would appreciate any feedback about this code.

Thanks!

Lost

Please Login or Register  to view this content.

**MaczaQ** · 09-07-2011, 03:02 AM

for me your modification works good - is nothing more to say

Best Regards
MaczaQ

Code to delete sheets and rename sheets in a protected workbook?

LinkBack

Thread Tools

Rate This Thread

Display

Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Re: Code to delete sheets and rename sheets in a protected workbook?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions