ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Avoid protected cell warning on BeforeDoubleClick WS event (https://www.excelbanter.com/excel-programming/441675-avoid-protected-cell-warning-beforedoubleclick-ws-event.html)

Matt[_52_]

Avoid protected cell warning on BeforeDoubleClick WS event
 
I am creating an event scheduling worksheet. A grid is generated with days
of the month x-axis and users y-axis. Where an event occurs this is logged
by a hidden event ID in the appropriate day cell. In order to prevent this
ID from being overwritten I protect the worksheet. What I am trying to do is
have a worksheet DoubleClick event which either.

1) Captures the event ID in the underlying cell, and opens a custom form for
editing the event, or
2) Recognises that no event exists and opens a custom form for logging a new
event.

What I have tried is to unprotect the worksheet at the start of my procedure
and then protect it following the code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim lActiveColumn As Long, lActiveDay As Long
Dim dActiveMonthDate As Date
ActiveSheet.Unprotect
lActiveColumn = Target.Column
lActiveDay = Cells(4, lActiveColumn).Value
dActiveMonthDate = Cells(1, (lActiveColumn - (lActiveDay - 1))).Value
If Target.Value = 0 Then
MsgBox ("DATE:" & lActiveDay & "/" & Month(dActiveMonthDate) & "/" &
Year(dActiveMonthDate))
Else
MsgBox ("EVENT ID: " & Target.Value)
End If
ActiveSheet.Protect
End Sub

But the warning message still appears.

Is there a method of disabling this warning message temporarily, or is there
another method which may be more suitable?, I considered BeforeRightClick
but this results in the context menu appearing after the code has executed.

Any advice and/or alternative solutions appreciated

Thanks in advance
Matt





All times are GMT +1. The time now is 07:27 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com