protecting a Macro sub

[Tej92]

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

 

[Tej92]

up

 

[MARK858]

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

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

 

[Tej92]

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

 

[MARK858]

Can you post the code you have with the protect/unprotect lines added please (in code tags as well please)

 

[Tej92]

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

 

[MARK858]

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)

 

[Tej92]

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

 

[MARK858]

Happy you have it working for you.

 

[Tej92]

thank you for trying to help!! really appreciate it