Protect format

amna77

Active Member
Joined
May 9, 2002
Messages
251
Hi,
Is there a way that I can protect the format of sheet.

For example, I don't want any body to change format, I mean re-arrage the column width and set the print area things, etc.
Thats what I want to protect,or put a password for that.
Anybody know how to do it?
Thanks in advance
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
You could hide the format toolbar and the format pop up toolbar (in an AUTO_OPEN Macro) - they shouldn't be able to amend the formats then...

To hide Main Worksheet Bar

Application.CommandBars("Worksheet Menu Bar").Enabled = False

not too sure the code for the pop up - I use this...

Sub DisableAllShortcutMenus()
Dim cb As CommandBar
For Each cb In CommandBars
If cb.Type = msoBarTypePopup Then cb.Enabled = False
Next cb
End Sub

But it gets rid of all right-button click menus...

Obviously you restore the menus etc in an Auto_Close Macro.

You can amend the code to simply remove certain buttons so leave the main bar but without Format...have a look on google.

_________________
LASW10
This message was edited by lasw10 on 2002-10-25 08:23
 

Iridium

Well-known Member
Joined
Jul 15, 2002
Messages
2,831
If you protect the sheet with a password (Tools|Protection|Protect Sheet) then the formats should bu unchangeable. If you want to do it with a macro the include these lines -
'ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=False 'for a workbook
ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True 'for a sheet
This message was edited by Chris Chattin on 2002-10-25 09:07
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
You can amend Chris' code to protect the sheet whilst still allowing users to amend the data etc...

ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=FALSE, Scenarios:=True
 

B_2

Board Regular
Joined
Aug 24, 2002
Messages
210
Hi Lasw10

What do i need to amend in this line of code which will allow me to protect the sheet and for it to work?

Selection.Sort KEY1:=Sheets(DATABASE_SHEET).[A3], ORDER1:=xlAscending, HEADER:=xlYes, ORDERCUSTOM:=1, MatchCase:=False, Orientation:=xlTopToBottom
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
On 2002-10-25 09:16, B_2 wrote:
Hi Lasw10

What do i need to amend in this line of code which will allow me to protect the sheet and for it to work?

B_2 - you need to unprotect the sheet before running the sort and then reprotect it when you're done...

Sheets("sheet1").Unprotect Password:="Blah"

Selection.Sort KEY1:=Sheets(DATABASE_SHEET).[A3], ORDER1:=xlAscending, HEADER:=xlYes, ORDERCUSTOM:=1, MatchCase:=False, Orientation:=xlTopToBottom

Sheets("sheet1").Protect Password:="Blah"


This is when it becomes good to protect your code in VBEditor, else people simply go into the code to get the password. (Though I admit Excel security is still pretty poor).

Is this what you wanted B_2?
 

Forum statistics

Threads
1,144,112
Messages
5,722,544
Members
422,446
Latest member
jsasiadek35

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
Top