VBA: Need help protecting all but locked cells w/ macro

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi


    Anybody know the code to protect a sheet against locked cell changes but allowing user changes to unlocked cells?


    Also Code for saving a specified sheet as a seperate workbook?


    Thanks


    (trying to learn VBA is forcing me into alcohol abuse)

    Quote

    Originally posted by JJacob
    Anybody know the code to protect a sheet against locked cell changes but allowing user changes to unlocked cells?


    Sheets("Sheet1").Protect ("Password")


    Quote

    Originally posted by JJacob
    Also Code for saving a specified sheet as a seperate workbook?


    Sheets("Sheet1").Copy
    ActiveSheet.SaveAs "YourFileName.xls"



    Regards,

    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

    Hi Barrie:


    When I use the following code:


    Password = "Msorbino"
    Sheets(1).Protect Password, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
    Sheets(2).EnableSelection = xlNoSelection
    Password = "Msorbino"
    Sheets(2).Protect Password, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
    Password = "Msorbino"
    Sheets(3).Protect Password, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
    Password = "Msorbino"
    Sheets(4).Protect Password, DrawingObjects:=True, Contents:=False, Scenarios:=True, UserInterfaceOnly:=True
    Password = "Msorbino"
    Sheets(5).Protect Password, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True


    Either nothing happens or the unlocked cells in sheet one are also locked.


    Also


    When I use the save sheet as code, it saves the whole workbook. I have been working on this project for weeks and it is driving me up a wall.


    Thanks for your help!

    I don't know why unlocked cells are becoming locked. For the sheet saving, you may be omitting the first of Barrie's two lines. The initial copy is needed.

    Code
    Sub SaveCurrentSheet()
    ActiveSheet.Copy 'creates a new workbook with this sheet in it
    'the new workbook is now active
    ActiveSheet.SaveAs "YourFileName.xls" 'saves the new workbook
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login