Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to select multiple items from a dropdown with file password protected

    Hi Everyone,

    I got the code to select multiple items from a dropdown (At cell D4) & its absolutely working fine. Now if I am password protecting the whole file, its not working. I have unlocked the cell D4 through Protection tab in format cells. Could anybody please help me out.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Address = "$D$4" Then
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" Then
    If xValue2 <> "" Then
    If xValue1 = xValue2 Or _
    InStr(1, xValue1, ", " & xValue2) Or _
    InStr(1, xValue1, xValue2 & ",") Then
    Target.Value = xValue1
    Else
    Target.Value = xValue1 & ", " & xValue2
    End If
    End If
    End If
    End If
    End If
    Application.EnableEvents = True
    End Sub


    Best Regards,
    Abhishek

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    abhi,

    Add this code to the ThisWorkbook module. When the workbook opens, worksheet sheet1 will be password protected but will allow VBA code to interact with the sheet

    Code:
    Private Sub Workbook_Open()
    Worksheets("Sheet1").Protect "Password123", userinterfaceonly:=True
    End Sub
    Change the password to what you would like to have.

    HTH,
    Maud

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    abhi,

    Just realized that your dropdown is most likely data validation at not a combobox. In that case, enter the following code in the worksheet module along with your code

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("D4")) Is Nothing Then
        Target.Locked = False
    Else:
        Range("D4").Locked = True
    End If
    End Sub
    If cell D4 is selected, Cell D4 becomes unlocked. If any other cell is selected then D4 becomes locked again

    Maud

  4. #4
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    Many Thanks Maud. Its working perfectly fine.

Posting Permissions

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