VBA unprotect button

Question

I have an excel sheet with multiple buttons. The sheet needs to be unclocked in order for the macros to run. Although I have found short VBA codes that protect/ unprotect the sheet when run independently, when these are integrated into a button's code, unprotecting the sheet does not work. The code for a particular button is:

Sub OptionButton56_Click()
     ActiveSheet.Unprotect Password:="Password"
            If Range("D33").Value = 2 Then
               Sheets("Input").Rows("34:35").Hidden = msoTrue
            ElseIf Range("D33").Value = 1 Then
               Sheets("Input").Rows("34:35").Hidden = msoFalse
            End If
    ActiveSheet.Protect Password:="Password"
End Sub

Is the Input sheet the same sheet that has this button on it? — Hasib_Ibradzic, Mar 19 '19 at 16:12
@costas Please note that *"unprotecting the sheet does not work"* is no useful error description. Instead tell more details. Which error do you get? Or what happens instead of what you expected? • Note that you unprotect the `ActiveSheet` but you hide some rows in `Sheets("Input")`. Please clarify. — Pᴇʜ, Mar 19 '19 at 16:23

score 0 · Answer 1 · answered Mar 19 '19 at 16:08

0

That code should work. When you say it doesn't work are you getting some sort of error or is everything executing but you just aren't seeing the change you expected? Also, a much easier way of doing this is to use the userinterfaceonly:=True when you are protecting the sheet. If you use this then the sheets are only locked for the users and not for vba code. So then you don't need to unlock the sheets every time you need your code to execute. I always use this macro for all my models:

'UserInterfaceOnly locks edits from user but allows VBA to edit (no need to unlock/lock before/after macros)
'This setting is kept in memory, so it must be applied on Open Workbook Event
Sub Lock_Model(wb As Workbook, Optional strFunction As String = "Lock")
    Dim varSheets As Variant

    varSheets = Array("sheet1", "sheet2") ' list all the sheets you want to lock

    'Lock/Unlock specified sheets in model
    If strFunction = "Lock" Then
        For i = LBound(varSheets) To UBound(varSheets)
            wb.Sheets(varSheets(i)).Protect Password:="Password", AllowFormattingColumns:=True, AllowFormattingRows:=True, UserInterfaceOnly:=True
        Next i
    Else
        For i = LBound(varSheets) To UBound(varSheets)
            wb.Sheets(varSheets(i)).Unprotect Password:="Password"
        Next i
    End If
End Sub

If you don't like this approach please specifiy what exactly is happening when you run your code.

answered Mar 19 '19 at 16:08

Hasib_Ibradzic

666
5
23

Note that you have to use `UserInterfaceOnly:=True` after every reopening of the workbook. It is not saved and therefore does not persist after closing the workbook. Also see [VBA Excel: Sheet protection: UserInterFaceOnly gone](https://stackoverflow.com/questions/38353751/vba-excel-sheet-protection-userinterfaceonly-gone/38375298#38375298) – Pᴇʜ Mar 19 '19 at 16:12
Which is exactly why I said to call this macro on the workbook open event. Every time the workbook is open the code appies – Hasib_Ibradzic Mar 19 '19 at 16:13
haha that is a lesson that every great developer learns the hard way. I have had to learn it many, many times... – Hasib_Ibradzic Mar 19 '19 at 16:17
Well I'm one step further and I already had my lessons. I almost never use comments. That's probably why I didn't read them. Good code doesn't need comments. Just an example: If you rename `varSheets` into `SheetsToLock` the comment behind gets redundant and can be removed. – Pᴇʜ Mar 19 '19 at 16:40
I think that is highly debatable. Especially when working with new programmers. Often times the shortest and most efficient code is hardest for new programmers to understand. As an example, a new programmer most likely wouldn't have understood that this macro would need to be called by the workbook open event. Someone who is experienced and understands how the code is working can come to that conclusion, but adding a comment will help ensure that isn't missed. – Hasib_Ibradzic Mar 19 '19 at 16:46
1

@Hasib_Ibradzic: Peh's point is that well named variables make many comments redundant. Too many comments can make the code hard to read, especially if the comments are not always updated when the code changes! – AJD Mar 19 '19 at 19:10

VBA unprotect button

1 Answers1