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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
|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
> > ------------------------
>
>
>