multiple selection dropdown list not working on protected sheet

Question

Have a question for Excel VBA profs.

I have an excel sheet with multiple selection dropdown list /list box . the code works brilliant.

But..

Now my  problem is that it doesn't work if the worksheet is protected, even if the actual cell containing the dropdown list is unlocked. It just behaves like a normal data validation list, i.e. the code is ignored.

Is there any way to allow it to work even if the sheet is protected

Help.....

Answer 1

Hi Carrie,

This is brilliant!! Thank you!! it solved my problem at work!!

Thank you for the kind feedback. I am pleased that the code was of assistance.

===

Regards,

Norman

Answer 2

Hi Jalal,

Replace the code in the standard module modSettings with the following version:

'=========>>

Option Explicit

Global strDVList As String

Public Const sPassword As String = "ABC"                   '<<==== Change to suit

'--------->>

Public Sub ProtectAll()

    Dim WB As Workbook

    Dim SH As Worksheet

    Set WB = ThisWorkbook

    For Each SH In WB.Worksheets

        SH.Protect Password:=sPassword, _

                   UserinterfaceOnly:=True

    Next SH

End Sub

'--------->>

Public Sub UnprotectIAll()

    Dim WB As Workbook

    Dim SH As Worksheet

    Set WB = ThisWorkbook

    For Each SH In WB.Worksheets

        SH.Unprotect Password:=sPassword

    Next SH

End Sub

'<<=========

===

Regards,

Norman

Answer 3

Hi Jalal,

In the ThisWorkbook module, add the following code:

'=========>>

Option Explicit

'--------->>

Private Sub Workbook_Open()

    Call ProtectIt

End Sub

'<<=========

In the code module of the Calc sheet, restore the original code:

'=========>>

Option Explicit

'--------->>

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim rngDV As Range

    Dim oldVal As String

    Dim newVal As String

    Dim strList As String

    On Error Resume Next

    Application.EnableEvents = False

    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)

    On Error GoTo exitHandler

    If rngDV Is Nothing Then GoTo exitHandler

    If Not Intersect(Target, rngDV) Is Nothing Then

        If Target.Validation.Type = 3 Then

            strList = Target.Validation.Formula1

            strList = Right(strList, Len(strList) - 1)

            strDVList = strList

            frmDVList.Show

        End If

    End If

exitHandler:

    Application.EnableEvents = True

End Sub

'--------->>

' Developed by Contextures Inc.

' www.contextures.com

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngDV As Range

    Dim oldVal As String

    Dim newVal As String

    Dim strSep As String

    strSep = ", "

    Application.EnableEvents = False

    On Error Resume Next

    If Target.Count > 1 Then GoTo exitHandler

    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)

    On Error GoTo exitHandler

    If rngDV Is Nothing Then GoTo exitHandler

    If Intersect(Target, rngDV) Is Nothing Then

        'do nothing

    Else

        newVal = Target.Value

        Application.Undo

        oldVal = Target.Value

        Target.Value = newVal

        If newVal = "" Then

            'do nothing

        Else

            If oldVal = "" Then

                Target.Value = newVal

            Else

                Target.Value = oldVal & strSep & newVal

            End If

        End If

    End If

exitHandler:

    Application.EnableEvents = True

End Sub

'<<=========

In your standard module, replace the existing declaration with:

'=========>>

Option Explicit

Global strDVList As String

Public Const sPassword As String = "ABC"                          '<<==== Change to suit

'--------->>

Public Sub ProtectIt()

    Dim WB As Workbook

    Dim SH As Worksheet

    Set WB = ThisWorkbook

    Set SH = WB.Sheets("Calc")

    SH.Protect Password:=sPassword, _

               UserinterfaceOnly:=True

End Sub

'--------->>

Public Sub UnprotectIt()

    Dim WB As Workbook

    Dim SH As Worksheet

    Set WB = ThisWorkbook

    Set SH = WB.Sheets("Calc")

    SH.Unprotect Password:=sPassword

End Sub

'<<=========

In order to unprotect and re-protect the sheet for manual maintenance, execute the UnprotectIt and ProtectIt macros.

Because of the use of the UserInterfaceOnly argument of the protect method, you can operate on the protected sheet via code as if the sheet were unprotected.  Because this setting is not persistent between Excel sessions, the initial protection is called in the Workbook_Open event.

I have uploaded the updated file  Jalal20150324,xlsm at: http://1drv.ms/1dbCieY

===

Regards,

Norman

Answer 4

Appreciated.

I would like to share file link here

 https://onedrive.live.com/redir?resid=ebfac460fed086a3!215&authkey=!AJbUmIuVUpgA3zs&ithint=file%2cxlsm

try to protect/unprotect the sheet. Then you can see what I explained above.

also you can view the code and help me with solution.

Thanks

Answer 5

Hi Jalal,

Have a question for Excel VBA profs.

I have an excel sheet with multiple selection dropdown list /list box . the code works brilliant.

But..

Now my  problem is that it doesn't work if the worksheet is protected, even if the actual cell containing the dropdown list is unlocked. It just behaves like a normal data validation list, i.e. the code is ignored.

Is there any way to allow it to work even if the sheet is protected

Help.....

One approach would be to unprotect the sheet at the start of your code and to re-protect it at the end of the code.  Schematically, something like:

'=========>>

Option Explicit

'--------->>

Public Sub Tester()

    Dim WB As Workbook

    Dim SH As Worksheet

    Set WB = ThisWorkbook

    Set SH = WB.Sheets("Sheet1")             '<<==== Change as required

    SH.Unprotect Password:=sPassword

    '\ Your code

    '.....

    '.....

    SH.Protect Password:=sPassword

End Sub

'<<=========

===

Regards,

Norman