Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts

    Macros on Shared & Protected worksheet (Excel 2003)

    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?
    Maria
    Simmo7
    Victoria, Australia

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macros on Shared & Protected worksheet (Excel 2003)

    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:
    <code>
    Private Sub Workbook_Open()
    With ActiveSheet
    If .FilterMode = True Then
    .ShowAllData
    End If
    End With
    End Sub
    </code>
    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:
    <code>
    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
    </code>
    Remember, this code belongs in the ThisWorkbook module, not in a 'standard' module where normal macros are stored.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros on Shared & Protected worksheet (Excel 2003)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts

    Re: Macros on Shared & Protected worksheet (Excel 2003)

    Thanks Hans, This is just what I wanted - I just wasn't too sure about the syntax to use.
    Maria
    Simmo7
    Victoria, Australia

  5. #5
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts

    Re: Macros on Shared & Protected worksheet (Excel 2003)

    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.
    Maria
    Simmo7
    Victoria, Australia

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros on Shared & Protected worksheet (Excel 2003)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts

    Re: Macros on Shared & Protected worksheet (Excel 2003)

    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
    Maria
    Simmo7
    Victoria, Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •