Message box to confirm action

I have the following code so that when a cell is selected, it copies all the
cells/formulas from the template worksheet to the active worksheet and
“overwrite” the target cells.

Now, how can I insert a “Pop Up Message” to ask “Are you sure?” and proceed
if Yes, exit if No?

(I just want to make sure if the cell is clicked by accident then the active
worksheet data is not lost.)

------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$K$1" Then
ActiveSheet.Unprotect
ActiveSheet.Range("N3:Q242").Formula =
Sheets("ProgramSummaryTemplate").Range("N3:Q242").Formula
ActiveSheet.Protect
Range("N3").Select
End If

Cancel = True
End Sub
------------------------

Hi CRayF,

Something like this would do it:

If MsgBox("Are you sure?", vbYesNo Or vbQuestion _
Or vbDefaultButton2) = vbYes Then
'/ Your Code Here
End If

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]


CRayF wrote:
> I have the following code so that when a cell is selected, it copies
> all the cells/formulas from the template worksheet to the active
> worksheet and "overwrite" the target cells.
>
> Now, how can I insert a "Pop Up Message" to ask "Are you sure?" and
> proceed if Yes, exit if No?
>
> (I just want to make sure if the cell is clicked by accident then the
> active worksheet data is not lost.)
>
> ------------------------
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> If Target.Address = "$K$1" Then
> ActiveSheet.Unprotect
> ActiveSheet.Range("N3:Q242").Formula =
> Sheets("ProgramSummaryTemplate").Range("N3:Q242").Formula
> ActiveSheet.Protect
> Range("N3").Select
> End If
>
> Cancel = True
> End Sub
> ------------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ans
If Target.Address = "$K$1" Then
Ans = Msgbox("Are tyou sure", vbYesNo")
If Ans = vbYes Then
ActiveSheet.Unprotect
ActiveSheet.Range("N3:Q242").Formula =
Sheets("ProgramSummaryTemplate").Range("N3:Q242").Formula
ActiveSheet.Protect
Range("N3").Select
End If
End If

Cancel = True
End Sub


--
HTH

Bob Phillips

"CRayF" <CRayF@discussions.microsoft.com> wrote in message
news:4F2F4F9C-A58C-4052-8807-9D3409A0F7E6@microsoft.com...
> I have the following code so that when a cell is selected, it copies all
the
> cells/formulas from the template worksheet to the active worksheet and
> "overwrite" the target cells.
>
> Now, how can I insert a "Pop Up Message" to ask "Are you sure?" and
proceed
> if Yes, exit if No?
>
> (I just want to make sure if the cell is clicked by accident then the
active
> worksheet data is not lost.)
>
> ------------------------
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> If Target.Address = "$K$1" Then
> ActiveSheet.Unprotect
> ActiveSheet.Range("N3:Q242").Formula =
> Sheets("ProgramSummaryTemplate").Range("N3:Q242").Formula
> ActiveSheet.Protect
> Range("N3").Select
> End If
>
> Cancel = True
> End Sub
> ------------------------

"CRayF" <CRayF@discussions.microsoft.com> wrote in message
news:4F2F4F9C-A58C-4052-8807-9D3409A0F7E6@microsoft.com...
|I have the following code so that when a cell is selected, it copies all
the
| cells/formulas from the template worksheet to the active worksheet and
| â?ooverwriteâ? the target cells.
|
| Now, how can I insert a â?oPop Up Messageâ? to ask â?oAre you sure?â?
and proceed
| if Yes, exit if No?
|
| (I just want to make sure if the cell is clicked by accident then the
active
| worksheet data is not lost.)
|
Try this..!

| Private Sub Worksheet_SelectionChange(ByVal Target As Range)

| If Target.Address = "$K$1" Then

If MsgBox("Are you sure?", vbYesNo) = vbYes Then
| ActiveSheet.Unprotect
| ActiveSheet.Range("N3:Q242").Formula =
| Sheets("ProgramSummaryTemplate").Range("N3:Q242").Formula
| ActiveSheet.Protect
| Range("N3").Select
| End If
End If
| End Sub

HTH
Andrew

Perfect! Thanks

"Jake Marx" wrote:

> Hi CRayF,
>
> Something like this would do it:
>
> If MsgBox("Are you sure?", vbYesNo Or vbQuestion _
> Or vbDefaultButton2) = vbYes Then
> '/ Your Code Here
> End If
>
> --
> Regards,
>
> Jake Marx
> MS MVP - Excel
> www.longhead.com
>
>
> [please keep replies in the newsgroup - email address unmonitored]
>
>
> CRayF wrote:
> > I have the following code so that when a cell is selected, it copies
> > all the cells/formulas from the template worksheet to the active
> > worksheet and "overwrite" the target cells.
> >
> > Now, how can I insert a "Pop Up Message" to ask "Are you sure?" and
> > proceed if Yes, exit if No?
> >
> > (I just want to make sure if the cell is clicked by accident then the
> > active worksheet data is not lost.)
> >
> > ------------------------
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >
> > If Target.Address = "$K$1" Then
> > ActiveSheet.Unprotect
> > ActiveSheet.Range("N3:Q242").Formula =
> > Sheets("ProgramSummaryTemplate").Range("N3:Q242").Formula
> > ActiveSheet.Protect
> > Range("N3").Select
> > End If
> >
> > Cancel = True
> > End Sub
> > ------------------------
>
>
>