Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Protect multiple tabs (Excell 2000)

    A) Does anyone know if its possible to protect/unprotect multiple worksheets in a single operation instead of sheet by sheet(either without passwaord or all sheets with the same password).

    [img]/forums/images/smilies/cool.gif[/img] Is it possible to have Excel display 2 lines of worksheet tabs in order to see more of them ?

    Claude

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

    Re: Protect multiple tabs (Excell 2000)

    A) No, but you could create a macro to loop through selected worksheets and protect/unprotect them.
    [img]/forums/images/smilies/cool.gif[/img] No, Excel isn't designed that way.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protect multiple tabs (Excell 2000)

    To avoid problems with Upper case and Lower case in your VBA code , you could surround references to sheet names with, say UCase() to eliminate the problem.

    Instead of <font face="Georgia">Sheets(i).name</font face=georgia>
    you would have <font face="Georgia"><span style="background-color: #FFFF00; color: #000000; font-weight: bold">UCase(</span hi>Sheets(i).Name<span style="background-color: #FFFF00; color: #000000; font-weight: bold">)</span hi></font face=georgia>, etc.,

    and use the appropriate case in your <font face="Georgia">"SHEET NAME STRINGS"</font face=georgia>

    Errol

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

    Re: Protect multiple tabs (Excell 2000)

    Am I missing something? Variable q never gets assigned a value as far as I can see.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protect multiple tabs (Excell 2000)

    [img]/forums/images/smilies/cool.gif[/img] While Excel won't show multiple rows of tabs, you can see all of the tab names by right clicking on the arrowheads in the bottom left corner of the spreadsheet

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protect multiple tabs (Excell 2000)

    <P ID="edit" class=small>(Edited by unkamunka on 14-Dec-03 23:54. reflects feedback in [post#=322008] and [post#=321973])</P>To protect multiple worksheets, try code like this<pre>Sub EnterPassword() 'protects each sheet in the workbook
    Dim i As Long
    Dim strPassword As String
    strPassword = InputBox("Enter Password for Each Worksheet")
    For i = 1 To Sheets.Count
    Select Case LCase(Sheets(i).Name)
    Case [your special sheet names here]
    Worksheets(i).Protect Password:=strPassword
    Case Else
    End Select
    Next i
    End Sub

    Sub RemovePassword() 'unlocks each sheet in the workbook
    Dim i As Long
    Dim strPassword As String
    strPassword = InputBox("Enter Unlocking Password")
    For i = 1 To Sheets.Count
    Select Case LCase(Sheets(i).Name)
    Case [your special sheet names here]
    Worksheets(i).Unprotect Password:=strPassword
    Case Else
    End Select
    Next i
    End Sub</pre>

    The thing to watch for is, if you are only protecting worksheets, the name of the sheet can NOT be protected.

    Hope This Helps
    Gre

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protect multiple tabs (Excell 2000)

    You're right! It's slimmed down from another routine. q was meant to deal with InputBox values. Amendment in order, thanks.
    Gre

Posting Permissions

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