Allow to widen/narrow columns while excel sheet is protected

[franksirvent]

Hi there
I have a sheet which is protected with the following code which run at startup:
Worksheets("ExporttoExcel2").Activate
ActiveSheet.Protect UserInterfaceOnly:=True
ActiveSheet.EnableAutoFilter = True

this works great as I don'r want to let people change the data, while at the same time I need to be able to filter.

The problem is that I also want to allow people to widen or narrow columns as they like (for printing, viewing etc) BUT because sheet is protected, they cannot.

How can I allow that while mantaining the protection ?

thanks in advance

 

[vladk]

Something like this?

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFiltering:=True

 

[franksirvent]

thanks

I get error 1004 ...

 

[vladk]

I don't.
Show your "startup

 

[franksirvent]

here it is...

Private Sub Workbook_Open()
Allow_filter
End Sub


Sub Allow_filter()
Worksheets("ExporttoExcel2").Activate
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, AllowFiltering:=True


End Sub

 

[combo]

What excel version? Extended protection was introduced in excel xp.

combo

 

[vladk]

franksirvent,

It runs fine on my PC. There is another issue though: it seems that AllowFiltering option does not actually allows filtering!

What I did before I sent you an example, I recorded the macro while protecting the sheet and checking the appropriate options, including AllowFiltering.

If you receive error 1004, and I am not, it likely means that something sits inside your workbook/worksheet that prevents the automation.

Try this code on the blank workbook with some data inside just to make filtering possible, and then we try to decide what to do.

vladk

 

[franksirvent]

thanks for your time in trying to help me

I am using excel 2000

i have created a dummy sheet, with only a few rows.

added the code above to Worksheet_Open like this but same error...

Private Sub Workbook_Open()
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFiltering:=True
End Sub

 

[vladk]

franksirvent,

Looks like combo was right: in Excel 2000 you can't do such a sensitive protection. Upgrade your EXCEL, if you can...

I use Excel 2002 SP3, this code does not causes errors but seems does not do what is supposed to do.

I am interested how it works in Excel 2003!

vladk

 

[franksirvent]

ok, thanks for info

I cannot upgrade as the whole company (over 300 people( use the same version so I cannot upgrade...

thanks anyway and thanks so much for taking your time to help me !

 

[combo]

Do not resign so easy. Having UserInterface protection only, you can code formatting. You can design simple dialog (row/column resize/autofit etc.), create custom menu, and/or use built-in dialog. Example in protected worksheet's module:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Application.Dialogs(xlDialogColumnWidth).Show
End Sub

combo

 

[firefytr]

It works the same in 2003 as it does in 2002 (XP), there were no changes to those object properties.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[vladk]

Hi,
I figured it out. It does work, I mean the filter. The filter must be prebuilt (prepared before the protection).
vladk