Consulting

Results 1 to 4 of 4

Thread: Solved: array not working

  1. #1
    VBAX Regular
    Joined
    Oct 2004
    Posts
    65
    Location

    Solved: array not working

    Sorry this may be simple but I'm not shure what I am doing wrong Can any one tell me why this is not working

    [VBA] Sheets(Array("MO-TS", "MO-DS", "MO-CG")).Unprotect password:="RTA"[/VBA]

    We are living in a world today
    where lemonade is made from
    artificial flavoring and furniture polish
    is made from real lemons...
    Alfred E Newman

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    You can't use the Protect method with an array, it must be by singular value, e.g. looping through each of those sheets and (un)protecting on an iteration. Some functions cannot be performed like this. My biggest pet peeve with this is not being able to do Data Validation to the same cell in each worksheet (manually) at the same time.

    To test out what I'm talking about, select a sheet, press and hold the Shift key, then select another sheet - group any number of sheets. Now go to the Tools menu, select Protection, not that Protect Sheet is greyed out. Try this with other menus and menu options to see what you can/can't use with sheets in an array.

  3. #3
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi there,

    If you only have the three to do, how about this as an alternative?

    [vba]Sub Unprotect()
    dim pwrd as string
    pwrd = "RTA"
    Sheets("MO-TS").unprotect password:=pwrd
    Sheets("MO-DS").unprotect password:=pwrd
    Sheets("MO-cg").unprotect password:=pwrd
    End Sub[/vba]
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  4. #4
    VBAX Regular
    Joined
    Oct 2004
    Posts
    65
    Location
    thanks that was what i needed to know
    We are living in a world today
    where lemonade is made from
    artificial flavoring and furniture polish
    is made from real lemons...
    Alfred E Newman

Posting Permissions

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