Modified worksheet macro, where if Yes portion works but not else

[Guest]

Hi,

This macro has been developed using suggestions and macros by others, and it
does most of what I want, except...

The else portion should simply exit the macro, but what it actaully does is
carry on as if the user clicked yes. Does anyone have a solution for me? Many
thanks...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'D.McRitchie, 2007-05-11, not posted
ActiveSheet.Unprotect
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you want to insert a new row ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Inserting a New Row" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes"
'
Cancel = True
Target.Offset(0).EntireRow.Insert
Target.Offset(-2).EntireRow.Copy Target.Offset(-2).EntireRow
On Error Resume Next
Target.Offset(-1).EntireRow.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
Else ' User chose No.
MyString = "No" 'Don't do anything.
End If
ActiveCell.Offset(-1).Select
ActiveCell.EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Activate
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[CoRrRan]

Hi,

This macro has been developed using suggestions and macros by others, and it
does most of what I want, except...

The else portion should simply exit the macro, but what it actaully does is
carry on as if the user clicked yes. Does anyone have a solution for me? Many
thanks...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'D.McRitchie, 2007-05-11, not posted
ActiveSheet.Unprotect
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you want to insert a new row ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Inserting a New Row" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes"
'
Cancel = True
Target.Offset(0).EntireRow.Insert
Target.Offset(-2).EntireRow.Copy Target.Offset(-2).EntireRow
On Error Resume Next
Target.Offset(-1).EntireRow.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
Else ' User chose No.
MyString = "No" 'Don't do anything.
End If
ActiveCell.Offset(-1).Select
ActiveCell.EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Activate
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Add in:

Exit Sub

Right after:

MyString = "No"

(Although I wonder why you would want to fill the "MyString" with "No"
and not use it at all anymore, so I probably would have put "Exit Sub"
right after "Else".)

CoRrRan

 

[Guest]

Hi,

It isn't doing that. If the user presses YES it executes the first part of
the if statement and the code after end if.

If no is pressed it is executing the code after the Enf If

To make it terminate if the user presses no try:-

Else ' User chose No.
MyString = "No" 'Don't do anything.
Exit Sub
End If


Mike

 

[Guest]

Hi

I assume the Else portion you were referring to ios this:
Else ' User chose No.
MyString = "No" 'Don't do anything.
End If

If you want the sub to finish there if the user click no
simply insert the following line:

Exit Sub

in place of the --> MyString = "No" 'Don't do anything.

HTH

 

[Guest]

It works now, thanks for the help!

Hi,

It isn't doing that. If the user presses YES it executes the first part of
the if statement and the code after end if.

If no is pressed it is executing the code after the Enf If

To make it terminate if the user presses no try:-

Else ' User chose No.
MyString = "No" 'Don't do anything.
Exit Sub
End If


Mike