protecting a Macro sub

Tej92

Board Regular
Joined
Sep 27, 2022
Messages
73
Office Version
  1. 365
Platform
  1. Windows
Hello everyone, please see below the code i'm using to create combo box where there is a drop down list to increase it's size and it works perfectly.
now i;m trying to protect all the macros so I've added the following two lines to the start of the subs and the end of them.
when i try to do the same for the following code it keeps giving me errors for that edit is enabled because the sheet is protected.
if i don'd add any protection it unprotects the whole worksheet.
any ideas?
ThisWorkbook.Worksheets("Cell 1").Unprotect ("1234")
ThisWorkbook.Worksheets("Cell 1").Protect ("1234")

these are the lines to protect and unprotect that i'm using

Private Sub TempCombo_Change()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
now i;m trying to protect all the macros
The code you are using doesn't protect the macro's it protects the sheet, you protect the macro's in the VB editor

If you want to allow the macro to have interaction with a protected sheet then you can use UserInterFaceOnly:=True

VBA Code:
ThisWorkbook.Worksheets("Cell 1").Protect ("1234"), UserInterFaceOnly:=True

You'll need to apply this each time the workbook is opened after a save if you ever use an older version of Excel
 
Upvote 0
I've tried that line too, I'm getting the same error, the code above is to use combobox instead of dropdown list from data validation so that i can increase the size of the list.
Now when i click on the cell with a dropdown list a combobox appears as it should but it doesn't let me select anything giving the following error:
"The cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password"

it seems that the code is running something else that isn't being unprotected from the first line so it doesn't allow me to modify the cell content
 
Upvote 0
Can you post the code you have with the protect/unprotect lines added please (in code tags as well please)
 
Upvote 0
Managed to make it work, not sure if it's the right way of doing it, by adding the protect line after the error line and unprotecting it when a tempbox is clicked

Private Sub TempCombo_Change()
ThisWorkbook.Worksheets("Cell 1").Unprotect ("1234")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'this sub is to increase the size of the drop-down lists.
ThisWorkbook.Worksheets("Cell 1").Protect ("1234")
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr

Set xWs = Application.ActiveSheet
On Error Resume Next
ThisWorkbook.Worksheets("Cell 1").Protect ("1234")
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
ThisWorkbook.Worksheets("Cell 1").Unprotect ("1234")
End Sub


this seems to work fine
 
Upvote 0
Solution
Whatever works for you is correct although I don't see the point of turning on protection at the start of the code and off at the end of the code so the sheet is only protected while the code is running (you can't alter a sheet manually while code is running anyway)
 
Upvote 0
that's exactly why i was stuck for so long. i was protecting it at the end. now for some reason that i can't explain with unprotecting at the end it works fine and the sheet stays protected ones the selection in the cell has been made 🤷‍♂️
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top