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

The short answer is to put
Cancel = True
into which ever part of the If..Then...Else statement you want to execute
and ignore the double-click. If you just want to ignore the double-click
regardless, then just put that statement pretty much anywhere outside of the
If...Then...Else block.


"Matt" wrote:

> 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
>
>
>

Thanks, works a treat

"JLatham" <(E-Mail Removed)> wrote in message
news:E0AA30DE-002C-4F92-89DA-(E-Mail Removed)...
> The short answer is to put
> Cancel = True
> into which ever part of the If..Then...Else statement you want to execute
> and ignore the double-click. If you just want to ignore the double-click
> regardless, then just put that statement pretty much anywhere outside of
> the
> If...Then...Else block.
>
>
> "Matt" wrote:
>
>> 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
>>
>>
>>