Results 1 to 4 of 4

Thread: Protecting button

  1. #1

    Protecting button



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,
    Need to protect only the buttons on my sheet, I do not want to protect/lock the sheet I only want to keep users from accidentally changing or moving the button. When I tried protecting the sheet even with all the cells unlock the macro would not work. Thanks
    I am using both Form control and ActiveX control in my sheet.
    Any idea guys?

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,304
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    If you want to protect your worksheet, but still let your code run, then you can use code like the following to do so:

    Code:
    ActiveSheet.Protect userinterfaceonly:=True
    The only issue is that you need to re-run this code every time the workbook launches, as the setting is not persistent between workbook sessions. When I need to do this I'll usually just put it in a Workbook_Open routine.

    Hope that helps,
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hi ,

    thanks for your kind help.

    Do i need to put ur code in the General Workbook ?

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,304
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    It depends if you're trying to run it on demand for a specific worksheet, or if you want to do all worksheets in the workbook.

    If you want to re-protect all worksheets at open, and make sure userinterfaceonly is set to true as well, then you'd drop this in the ThisWorkbook module:
    Code:
    Private Sub Workbook_Open()
        Dim pWord As String
        Dim ws As Worksheet
        
        'Set your password here
        pWord = "myPassword"
        
        'Re-protect all sheets
        For Each ws In ThisWorkbook.Worksheets
            ws.Protect Password:=pWord, userinterfaceonly:=True
        Next ws
        
    End Sub
    If you want to do a single worksheet, then you'd use code like this in whatever routine you decide needs it:
    Code:
    Worksheets("Sheet_x").Protect Password:="myPassword", UserInterfaceOnly:=True
    Of course you'd need to update the password and the name of the worksheet.

    Does that make more sense?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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