Solved How to save my sheet

June 19, 2017 at 12:08:20
Specs: Macintosh
I HAVE AN EXCEL SHEET I CREATED WITH FORMULAS IN CERTAIN CELLS AND ADDED A CLEAR BUTTON WITH A MACRO BUT I CANT PROTECT THE SHEET WITHOUT SHUTTING OFF MY CLEAR BUTTON AND CANT SAVE MY SHEET BECUSE IT WANTS TO DELETE MY MACROS
ANY WAY AROUND THIS PLEASE

thank you

EXCEL 16 , MAC AIR

message edited by BRENDAN


See More: How to save my sheet

Reply ↓  Report •


#1
June 19, 2017 at 12:23:23
Please don't post in all caps.
All caps is the internet equivalent of yelling and no one likes to be yelled at.

re: "Can't save my sheet because it wants to delete my macros"

Save the sheet as a .xlsm or .xlsb and the macros will be retained.

re: "Can't protect the sheet without shutting off my macros"

You can Unprotect the sheet, perform your "clear" operation and then Protect the sheet, all within the macro.

Assuming you are using a password, this example code might help.

Sub ClearMySheet()
 Worksheets("Sheet1").Unprotect Password:="YourPassword"
  'Your Clear Code Goes Here
 Worksheets("Sheet1").Protect Password:="YourPassword"
End Sub

If you aren't using a password, just leave off the Password:="YourPassword" arguments.

One more note: If you are using a password, the password will be visible to anyone who accesses the VBA editor and looks at your code. To hide the password from users, you must "Lock the project for viewing" and then password protect the VBA project itself. See the "Help protect the project" section of this page:

https://support.office.com/en-us/ar...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
June 19, 2017 at 15:21:54
thank you very much Derby dad03 ,saving it to the .xlms worked great

but the second part i entered the code and passwords and I'm sure it worked but i guess its not what i need as the sheet is unusable when its then reprotected .

i need something that stops people from deleting cells and my formulas with them

as they are using the sheet they might want to delete a mistake in a cell and delete it and with that it deletes my sum formula with it

any help would be appreciated thank you


Reply ↓  Report •

#3
June 19, 2017 at 17:26:04
✔ Best Answer
re: "as they are using the sheet they might want to delete a mistake in a cell and delete it and with that it deletes my sum formula with it"

I obviously don't know how your users use the sheets, but when I make a make a mistake in a cell, I don't delete the cell, I delete the contents of the cell. Why your users are deleting cells with formulas in them doesn't make sense to me, but if that the case, it's fairly easy to fix.

re: "the sheet is unusable when its then reprotected "

You do know that there are options that you can choose when you protect a sheet, right? When you protect the sheet, you chose the things that you want to allow the users to do, including which cells they can select, enter data in, etc. By locking/unlocking cells you can prevent them from deleting or even selecting the cells with your formulas in them.

You should read the info found here:

https://support.office.com/en-us/ar...

Basically, it works like this:

1 - All cells in a worksheet are Locked by default. You can see this by looking at the Protection tab under Format...Cells. The Locked box will be checked.
2 - The fact that the cells are Locked means nothing until you Protect the sheet. Once you Protect the sheet, the Locked cells cannot be altered, unless you chose some of the "Allow users...to" options when you Protect the sheet.
3 - If you Unlock certain cells before you Protect the sheet, users will be able alter those cells even when the sheet is protected.

In other words, Unlock the cells that you want your users to enter data into but leave your formula cells Locked and then Protect the sheet. The users will only be able to alter the Unlocked cells.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
June 20, 2017 at 08:19:26
Thank you that worked great. Much appreciated

Reply ↓  Report •

Ask Question