| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modified worksheet macro, where if Yes portion works but not else
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).ClearConten ts 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modified worksheet macro, where if Yes portion works but notelse
econ wrote:
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).ClearConten ts 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modified worksheet macro, where if Yes portion works but not else
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 "econ" wrote: 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).ClearConten ts 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modified worksheet macro, where if Yes portion works but not else
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 "econ" wrote: 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).ClearConten ts 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modified worksheet macro, where if Yes portion works but not e
It works now, thanks for the help!
"Mike H" wrote: 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 "econ" wrote: 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).ClearConten ts 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sorting a portion of a worksheet | Excel Worksheet Functions | |||
| HOW DO I SAVE A PORTION OF A WORKSHEET ON A FLOPPY DISK? | Excel Worksheet Functions | |||
| sort macro that works after leaving worksheet | Excel Discussion (Misc queries) | |||
| Buttons in lower portion of workbook appear in upper portion | Excel Programming | |||
| excel prints only a portion of my worksheet | Excel Discussion (Misc queries) | |||
Linear Mode
