Macros on Shared & Protected worksheet (Excel 2003)

Topic

New Reply

Hi all, I have a protected and shared worksheet with Autofilter set to on. My problem is that various users save their work with their own filters set. I unshared the worksheet and unprotected it, added a button that would reset the filters to show all data, reprotected it and shared it again – once I did that I got a message about the macro not being available and could not click on the macro button. Is there a way I can have the filters reset on opening the file?

Reply | Quote

Replies

Welcome to Woody’s Lounge!

Unshare the workbook.
Activate the Visual Basic Editor.
Double-click the ThisWorkbook item in the Project Explorer tree on the left hand size.
Enter or copy/paste the following code into it:

Private Sub Workbook_Open()
With ActiveSheet
If .FilterMode = True Then
.ShowAllData
End If
End With
End Sub

If the workbook has multiple sheets and you want to reset the filter in a specific sheet, replace ActiveSheet with Worksheets(“NameOfSheet”).

If the workbook has several sheets that have their own filter, you can loop through the sheets:

Private Sub Workbook_Open()
Dim wsh As Worksheet
For Each wsh In Me.Worksheets
If wsh.FilterMode Then
wsh.ShowAllData
End If
Next wsh
End Sub

Remember, this code belongs in the ThisWorkbook module, not in a ‘standard’ module where normal macros are stored.

Reply | Quote

Thanks Hans, This is just what I wanted – I just wasn’t too sure about the syntax to use.

Reply | Quote

Hi Maria,

I’d like to take the opportunity to warn you against sharing workbooks.
Excel is not a multi-user application and sharing workbooks has a bad reputation of loss of data, corruption of files and loss of changes to the data.

I strongly advise you to start looking for a different solution, like a database.

Reply | Quote

Jan, There is no need to go to a database as the file in question is only used for one week, a new file is created in the following week with any comments carried forward into a new week for old data and new data being brought in for users to comment on. As the data is so short lived, and can be recreated very easily then loss of data or data corruption is less of a concern.

Reply | Quote

OK, I guess that makes it relatively safe.
Any chance on getting into a situation where there will be users with Excel 2007 AND excel 2003 editing the file?
I’ve heard quite some problem reports with that.

Reply | Quote

Jan,

Our Company standardizes its software, so there would never be a situation where there are different versions of Excel. Thanks for the info though, as there could be a situation where someone has a different version at home, and wanted to do some work on the file at home – shouldn’t happen though as we are supposed to use a Citrix interface to access our work files from home (using the same software as at the office).

Regards,
Maria

Reply | Quote