Need to unprotect then reprotect sheet when running a reate meeting macro from excel VBA

Anonymous
2023-05-11T12:21:59+00:00

I have the below code on one of my sheets.

The first sub creates a meeting event in outlook based on what initals are in a rannge of cells, links their email address and takes data from the sheet to generate the event.

The second creates a temporaary note whenever a cell is clicked in a range that has a letter in the cell, to display the full name associated with that letter.

Ive protected my sheet however when i run create meeting macro by double clicking the cell i specified as the initialiser, the macro wont work if the sheet is protected, yet works fine when it isnt. i need to add the sheets.Unprotect syntax to my code but in the places ive tried it hasnt been sucessful.

I do not want to set a password when i protect the sheet - im using it simply as a mistake handler so people dont delete code with formulas included.

Any help?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Sheets("Sheet2").Unprotect Password:="whatever" 

Dim OutApp As Object 

Dim ObjOutlook As Object 

Dim ObjMeeting As Object 

Dim OutMail As Object 

Dim r As Long 

Dim c As Long 

Dim strTo As String 

Dim strAddress As String 

If Target.CountLarge > 1 Then Exit Sub 

If Target.Column <> 2 Then Exit Sub 

Cancel = True 

'Set MailList = Worksheets("Sheet1").Range("MailList") 

r = Target.Row 

For c = 11 To 21 

    If Cells(r, c).Value <> "" Then 

        strAddress = Evaluate("IFERROR(VLOOKUP(""" & Cells(r, c).Value & """, AllStaff,2,FALSE),"""")") 

        If strAddress <> "" Then 

            strTo = strTo & "; " & strAddress 

        End If 

    End If 

Next c 

Set ObjOutlook = CreateObject("Outlook.Application") 

Set ObjMeeting = ObjOutlook.CreateItem(1) 

ObjOutlook.Session.Logon 

ObjMeeting.Display 

On Error Resume Next 

With ObjMeeting 

    .Meeting = 1 

    If strTo <> "" Then 

        .RequiredAttendees = Mid(strTo, 3) 

    End If 

    .Subject = Range("C" & r) & " - " & Range("E" & r) & " " & Range("F" & r) & " - " & Range("J" & r) 

    .Start = Range("F" & r).Value & " " & Format(Range("G" & r).Value, "h:mm") 

    .Duration = 480 

    .ReminderSet = False 

    .BusyStatus = 0 

    .Body = "This is the actual event start time.  Please arrive in advance at the office. Please accept this meeting but select 'Do not send response' if possible. Unless you need to decline, then let me know with a response. ----- " & Range("C" & r) & " ----- " & Range("E" & r) & " ----- " & Range("F" & r) & " ----- " & "Please, check this schedule for the weekend, if there are any problems or conflicts, contact me ASAP. Thank you!" 

    .Display 

End With 

Sheets("Sheet1").Protect Password:="whatever" 

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next 

Dim c As Range 

Dim r As Range 

Set r = Range("Allocations") 'Change to actual range where initials are entered 

If Not Intersect(Target, r) Is Nothing Then 

    r.ClearComments 

    If Target.Cells.Count > 1 Then Exit Sub 

    If Target.Value = "" Then Exit Sub 

    Target.AddComment 

    Target.Comment.Visible = True 

    Target.Comment.Text Text:=Application.VLookup(Target.Value, Worksheets("Dropdowns").Range("AllStaff"), 3, False) 

    Target.Comment.Shape.TextFrame.Characters.Font.Size = 10 

    Target.Comment.Shape.TextFrame.AutoSize = True 

End If 

End Sub

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

4 answers

Sort by: Most helpful
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more

  2. Anonymous
    2023-05-12T08:41:00+00:00

    I have realised that my code was fine, the issue was my target column - it wont work on a protected sheet if the target column has data validation. Ive changed it to a normal cell column with no validation and it works regardless of whether the sheet is or is not protected. Thank you!

    Was this answer helpful?

    0 comments No comments
  3. HansV 462.7K Reputation points MVP Volunteer Moderator
    2023-05-11T13:47:10+00:00

    I'm afraid I cannot explain that. BeforeDoubleClick code works for me on a protected sheet.

    Was this answer helpful?

    0 comments No comments
  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more