+ Reply to Thread
Results 1 to 7 of 7

Macro for filter on protected workbook that works for all sheets, no matter what sheets are named?

  1. #1
    StargateFanFromWork
    Guest

    Macro for filter on protected workbook that works for all sheets, no matter what sheets are named?

    I found a great piece of coding in the archives for taking care of filters
    in protected workbooks. My difficulty lies in that the sheets all have
    different names and I don't know how to code for all sheets in a workbook.

    Here's the code to put in the workbook module:

    Private Sub Workbook_Open()
    Worksheets("Sheet1").Activate
    ActiveSheet.EnableAutoFilter = True
    ActiveSheet.Protect UserInterfaceOnly:=True
    End Sub

    I'm guessing that it's the "Sheet1" that is stopping this from working.
    I've tried removing the "Sheet1", etc., but all I get are errors. Is there
    a way to modify the above so that it works on any sheet?: Users will be
    adding new ones in the future and they'll call them all sorts of things that
    would be impossible to determine in advance so a generic bit of code would
    work best.

    Thank you! :oD



  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello StargateFanFromWork,

    Private Sub Workbook_Open()
    Dim I As Integer
    For I = 1 To ThisWorkbook.Worksheets.Count
    Worksheets(I).Activate
    ActiveSheet.EnableAutoFilter = True
    ActiveSheet.Protect UserInterfaceOnly:=True
    Next I
    End Sub

    Sincerely,
    Leith Ross

  3. #3
    Norman Jones
    Guest

    Re: Macro for filter on protected workbook that works for all sheets, no matter what sheets are named?

    Hi StargateFanFromWork,

    Try:

    '=============>>
    Private Sub Workbook_Open()
    Dim SH As Worksheet
    Const PWORD As String = "ABC"

    For Each SH In Me.Worksheets
    With SH
    .Protect Password:=PWORD, UserInterfaceOnly:=True
    .EnableAutoFilter = True
    End With
    Next SH
    End Sub
    '<<=============

    ---
    Regards,
    Norman


    "StargateFanFromWork" <noSpam@NoJunkMail.com> wrote in message
    news:ORrhBffIGHA.1876@TK2MSFTNGP11.phx.gbl...
    >I found a great piece of coding in the archives for taking care of filters
    > in protected workbooks. My difficulty lies in that the sheets all have
    > different names and I don't know how to code for all sheets in a workbook.
    >
    > Here's the code to put in the workbook module:
    >
    > Private Sub Workbook_Open()
    > Worksheets("Sheet1").Activate
    > ActiveSheet.EnableAutoFilter = True
    > ActiveSheet.Protect UserInterfaceOnly:=True
    > End Sub
    >
    > I'm guessing that it's the "Sheet1" that is stopping this from working.
    > I've tried removing the "Sheet1", etc., but all I get are errors. Is
    > there
    > a way to modify the above so that it works on any sheet?: Users will be
    > adding new ones in the future and they'll call them all sorts of things
    > that
    > would be impossible to determine in advance so a generic bit of code would
    > work best.
    >
    > Thank you! :oD
    >
    >




  4. #4
    StargateFanFromWork
    Guest

    Re: Macro for filter on protected workbook that works for all sheets, no matter what sheets are named?

    How do I leave out the password, pls? I just do a generic, or whatever it's
    called, protect on the worksheets without any name or anything. Would like
    to leave it without a pw.

    btw, is there no way to just modify the "Worksheets("Sheet1").Activate" so
    that it doesn't have to take a worksheet name? Just curious. It seems much
    simpler than this code below. (But then, what do I know? <g>)

    Thanks!

    "Norman Jones" <normanjones@whereforartthou.com> wrote in message
    news:O6EZnqfIGHA.376@TK2MSFTNGP12.phx.gbl...
    > Hi StargateFanFromWork,
    >
    > Try:
    >
    > '=============>>
    > Private Sub Workbook_Open()
    > Dim SH As Worksheet
    > Const PWORD As String = "ABC"
    >
    > For Each SH In Me.Worksheets
    > With SH
    > .Protect Password:=PWORD, UserInterfaceOnly:=True
    > .EnableAutoFilter = True
    > End With
    > Next SH
    > End Sub
    > '<<=============
    >
    > ---
    > Regards,
    > Norman
    >
    >
    > "StargateFanFromWork" <noSpam@NoJunkMail.com> wrote in message
    > news:ORrhBffIGHA.1876@TK2MSFTNGP11.phx.gbl...
    > >I found a great piece of coding in the archives for taking care of

    filters
    > > in protected workbooks. My difficulty lies in that the sheets all have
    > > different names and I don't know how to code for all sheets in a

    workbook.
    > >
    > > Here's the code to put in the workbook module:
    > >
    > > Private Sub Workbook_Open()
    > > Worksheets("Sheet1").Activate
    > > ActiveSheet.EnableAutoFilter = True
    > > ActiveSheet.Protect UserInterfaceOnly:=True
    > > End Sub
    > >
    > > I'm guessing that it's the "Sheet1" that is stopping this from working.
    > > I've tried removing the "Sheet1", etc., but all I get are errors. Is
    > > there
    > > a way to modify the above so that it works on any sheet?: Users will be
    > > adding new ones in the future and they'll call them all sorts of things
    > > that
    > > would be impossible to determine in advance so a generic bit of code

    would
    > > work best.
    > >
    > > Thank you! :oD




  5. #5
    Norman Jones
    Guest

    Re: Macro for filter on protected workbook that works for all sheets, no matter what sheets are named?

    Hi StargateFanFromWork,

    > How do I leave out the password, pls? I just do a generic, or whatever
    > it's
    > called, protect on the worksheets without any name or anything. Would
    > like
    > to leave it without a pw.


    '=============>>
    Private Sub Workbook_Open()
    Dim SH As Worksheet

    For Each SH In Me.Worksheets
    With SH
    .Protect UserInterfaceOnly:=True
    .EnableAutoFilter = True
    End With
    Next SH
    End Sub
    '<<=============

    > btw, is there no way to just modify the "Worksheets("Sheet1").Activate" so
    > that it doesn't have to take a worksheet name? Just curious. It seems
    > much
    > simpler than this code below. (But then, what do I know? <g>)


    The above code does not activate any sheet (which should be unnecessary) and
    no sheet names are used.

    ---
    Regards,
    Norman


    "StargateFanFromWork" <noSpam@NoJunkMail.com> wrote in message
    news:O9lGpwfIGHA.3120@TK2MSFTNGP10.phx.gbl...
    > How do I leave out the password, pls? I just do a generic, or whatever
    > it's
    > called, protect on the worksheets without any name or anything. Would
    > like
    > to leave it without a pw.
    >
    > btw, is there no way to just modify the "Worksheets("Sheet1").Activate" so
    > that it doesn't have to take a worksheet name? Just curious. It seems
    > much
    > simpler than this code below. (But then, what do I know? <g>)
    >
    > Thanks!
    >
    > "Norman Jones" <normanjones@whereforartthou.com> wrote in message
    > news:O6EZnqfIGHA.376@TK2MSFTNGP12.phx.gbl...
    >> Hi StargateFanFromWork,
    >>
    >> Try:
    >>
    >> '=============>>
    >> Private Sub Workbook_Open()
    >> Dim SH As Worksheet
    >> Const PWORD As String = "ABC"
    >>
    >> For Each SH In Me.Worksheets
    >> With SH
    >> .Protect Password:=PWORD, UserInterfaceOnly:=True
    >> .EnableAutoFilter = True
    >> End With
    >> Next SH
    >> End Sub
    >> '<<=============
    >>
    >> ---
    >> Regards,
    >> Norman
    >>
    >>
    >> "StargateFanFromWork" <noSpam@NoJunkMail.com> wrote in message
    >> news:ORrhBffIGHA.1876@TK2MSFTNGP11.phx.gbl...
    >> >I found a great piece of coding in the archives for taking care of

    > filters
    >> > in protected workbooks. My difficulty lies in that the sheets all have
    >> > different names and I don't know how to code for all sheets in a

    > workbook.
    >> >
    >> > Here's the code to put in the workbook module:
    >> >
    >> > Private Sub Workbook_Open()
    >> > Worksheets("Sheet1").Activate
    >> > ActiveSheet.EnableAutoFilter = True
    >> > ActiveSheet.Protect UserInterfaceOnly:=True
    >> > End Sub
    >> >
    >> > I'm guessing that it's the "Sheet1" that is stopping this from working.
    >> > I've tried removing the "Sheet1", etc., but all I get are errors. Is
    >> > there
    >> > a way to modify the above so that it works on any sheet?: Users will
    >> > be
    >> > adding new ones in the future and they'll call them all sorts of things
    >> > that
    >> > would be impossible to determine in advance so a generic bit of code

    > would
    >> > work best.
    >> >
    >> > Thank you! :oD

    >
    >




  6. #6
    StargateFan
    Guest

    Re: Macro for filter on protected workbook that works for all sheets, no matter what sheets are named?

    On Wed, 25 Jan 2006 16:09:27 -0600, Leith Ross
    <Leith.Ross.227nyn_1138227009.2328@excelforum-nospam.com> wrote:

    >
    >Hello StargateFanFromWork,
    >
    >Private Sub Workbook_Open()
    >Dim I As Integer
    >For I = 1 To ThisWorkbook.Worksheets.Count
    >Worksheets(I).Activate
    >ActiveSheet.EnableAutoFilter = True
    >ActiveSheet.Protect UserInterfaceOnly:=True
    >Next I
    >End Sub
    >
    >Sincerely,
    >Leith Ross


    Thank you so much to everyone who responded! I'll try these out
    tomorrow at work. :oD


  7. #7
    StargateFanFromWork
    Guest

    Re: Macro for filter on protected workbook that works for all sheets, no matter what sheets are named?

    You're right. Sorry. This works fantastically well, I'm so pleased. The
    only thing that happens is that there is a bit of a flicker as each sheet is
    dealt with. You see each sheet pop up for a millisecond. But that's
    perfectly fine.

    All the sheets are protected but the ones that have filters now allow the
    filters to work.

    Thank you so much everyone. Appreciate it! :oD

    "Norman Jones" <normanjones@whereforartthou.com> wrote in message
    news:OB1IT3fIGHA.3120@TK2MSFTNGP10.phx.gbl...
    > Hi StargateFanFromWork,
    >
    > > How do I leave out the password, pls? I just do a generic, or whatever
    > > it's
    > > called, protect on the worksheets without any name or anything. Would
    > > like
    > > to leave it without a pw.

    >
    > '=============>>
    > Private Sub Workbook_Open()
    > Dim SH As Worksheet
    >
    > For Each SH In Me.Worksheets
    > With SH
    > .Protect UserInterfaceOnly:=True
    > .EnableAutoFilter = True
    > End With
    > Next SH
    > End Sub
    > '<<=============
    >
    > > btw, is there no way to just modify the "Worksheets("Sheet1").Activate"

    so
    > > that it doesn't have to take a worksheet name? Just curious. It seems
    > > much
    > > simpler than this code below. (But then, what do I know? <g>)

    >
    > The above code does not activate any sheet (which should be unnecessary)

    and
    > no sheet names are used.
    >
    > ---
    > Regards,
    > Norman
    >
    >
    > "StargateFanFromWork" <noSpam@NoJunkMail.com> wrote in message
    > news:O9lGpwfIGHA.3120@TK2MSFTNGP10.phx.gbl...
    > > How do I leave out the password, pls? I just do a generic, or whatever
    > > it's
    > > called, protect on the worksheets without any name or anything. Would
    > > like
    > > to leave it without a pw.
    > >
    > > btw, is there no way to just modify the "Worksheets("Sheet1").Activate"

    so
    > > that it doesn't have to take a worksheet name? Just curious. It seems
    > > much
    > > simpler than this code below. (But then, what do I know? <g>)
    > >
    > > Thanks!
    > >
    > > "Norman Jones" <normanjones@whereforartthou.com> wrote in message
    > > news:O6EZnqfIGHA.376@TK2MSFTNGP12.phx.gbl...
    > >> Hi StargateFanFromWork,
    > >>
    > >> Try:
    > >>
    > >> '=============>>
    > >> Private Sub Workbook_Open()
    > >> Dim SH As Worksheet
    > >> Const PWORD As String = "ABC"
    > >>
    > >> For Each SH In Me.Worksheets
    > >> With SH
    > >> .Protect Password:=PWORD, UserInterfaceOnly:=True
    > >> .EnableAutoFilter = True
    > >> End With
    > >> Next SH
    > >> End Sub
    > >> '<<=============
    > >>
    > >> ---
    > >> Regards,
    > >> Norman
    > >>
    > >>
    > >> "StargateFanFromWork" <noSpam@NoJunkMail.com> wrote in message
    > >> news:ORrhBffIGHA.1876@TK2MSFTNGP11.phx.gbl...
    > >> >I found a great piece of coding in the archives for taking care of

    > > filters
    > >> > in protected workbooks. My difficulty lies in that the sheets all

    have
    > >> > different names and I don't know how to code for all sheets in a

    > > workbook.
    > >> >
    > >> > Here's the code to put in the workbook module:
    > >> >
    > >> > Private Sub Workbook_Open()
    > >> > Worksheets("Sheet1").Activate
    > >> > ActiveSheet.EnableAutoFilter = True
    > >> > ActiveSheet.Protect UserInterfaceOnly:=True
    > >> > End Sub
    > >> >
    > >> > I'm guessing that it's the "Sheet1" that is stopping this from

    working.
    > >> > I've tried removing the "Sheet1", etc., but all I get are errors. Is
    > >> > there
    > >> > a way to modify the above so that it works on any sheet?: Users will
    > >> > be
    > >> > adding new ones in the future and they'll call them all sorts of

    things
    > >> > that
    > >> > would be impossible to determine in advance so a generic bit of code

    > > would
    > >> > work best.
    > >> >
    > >> > Thank you! :oD

    > >
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1