| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Halt Rest of Macro after Error €“ Jim Cone review
Hi Jim,
I was wondering if you might be able to help me with one last time. Its in regards to the code you helped me with a few days ago. Once €śClick€ť runs and later calls €śSaveGame€ť, I run into a slight problem. If the Docking Station workbook is open, the error messages works just great. However, I have one more application that is included in the €śClick€ť Macro (see below) and runs directly after €śSaveGame€ť. The code after SaveGame is used to clear the Templates and have them ready for the next entry. Along with the error message that comes up when the Docking Station Workbook is open, I would like it to halt the rest of the code so that my data is not lost until Im able to send to the Docking Station. Thanks again so much and I appreciate all your advice to help accomplish this request €“ Jenny B. Option Explicit Sub SaveGame() Dim Dockwkbk As Workbook Dim Actwks As Worksheet Set Actwks = ActiveSheet Application.ScreenUpdating = False Set Dockwkbk = Workbooks.Open(Filename:="C:\Documents and Settings\Me \Desktop\Docking Station.xls") Actwks.Move _ befo=Dockwkbk.Sheets("account Mgmt Log") Call WritetoMainPage Dockwkbk.Save Dockwkbk.Close savechanges:=False Application.ScreenUpdating = True End Sub __________________________________________________ __ Sub click() Sub click() Dim strNotice As String '<<<< Dim ws As Worksheet Set ws = Worksheets("T") strNotice = "This has been sent to Rogers Group for Review" If Range("a3") = "" And Range("a4") = "" Then MsgBox "Please Select NEW or Update" Exit Sub End If If Range("f5") = "" Then MsgBox "Enter Issue Number" Exit Sub End If Application.ScreenUpdating = False If Sheets("Account Mgmt Checklist").Range("a4") Then ws.Cells(4, 4) = "true" If Sheets("Account Mgmt Checklist").Range("a3") Then ws.Cells(4, 5) = "true" If Sheets("Account Mgmt Checklist").Range("a10") Then ws.Cells(8, 5) = "true" If Sheets("Account Mgmt Checklist").Range("b10") Then ws.Cells(8, 1) = "true" If Sheets("Account Mgmt Checklist").Range("t9") Then ws.Cells(8, 7) = "true" If Sheets("Account Mgmt Checklist").Range("u9") Then ws.Cells(8, 9) = "true" If Sheets("Account Mgmt Checklist").Range("a8") Then ws.Cells(7, 4) = "Yes" If Sheets("Account Mgmt Checklist").Range("A7") Then ws.Cells(7, 7) = "Yes" If Sheets("Account Mgmt Checklist").Range("B7") Then ws.Cells(7, 7) = "No" If Sheets("Account Mgmt Checklist").Range("e7") = 1 Then ws.Cells(19, 6) = "DRS" If Sheets("Account Mgmt Checklist").Range("e7") = 2 Then ws.Cells(19, 6) = "Book Entry" If Sheets("Account Mgmt Checklist").Range("e7") = 3 Then ws.Cells(19, 6) = "Restricted" If Sheets("Account Mgmt Checklist").Range("e7") = 4 Then ws.Cells(19, 6) = "ESPP" If Sheets("Account Mgmt Checklist").Range("e7") = 5 Then ws.Cells(19, 6) = "See other Comments" ws.Range("f21").Value = Sheets("Account Mgmt Checklist").Range("P5") If Sheets("Account Mgmt Checklist").Range("t5") = 1 Then ws.Cells(21, 6) = "Common Stock" If Sheets("Account Mgmt Checklist").Range("t5") = 2 Then ws.Cells(21, 6) = "Preferred Stock" ws.Range("d5").Value = Sheets("Account Mgmt Checklist").Range("l4") ws.Range("h5").Value = Sheets("Account Mgmt Checklist").Range("f5") Call AddPage Call SaveGame(strNotice) MsgBox strNotice ' Sheets("Account Mgmt Checklist").Range("A7") = "" Sheets("Account Mgmt Checklist").Range("A9") = "" Sheets("Account Mgmt Checklist").Range("B7") = "" Sheets("Account Mgmt Checklist").Range("a3") = "" Sheets("Account Mgmt Checklist").Range("a8") = "" Sheets("Account Mgmt Checklist").Range("h6") = "" Sheets("Account Mgmt Checklist").Range("L9") = "" <<<<< - Rest of Code Sheets("Account Mgmt Checklist").Range("l4") = "" Sheets("Account Mgmt Checklist").Range("J8") = "" Sheets("Account Mgmt Checklist").Range("L6") = "" Sheets("Account Mgmt Checklist").Range("P5") = "" Sheets("Account Mgmt Checklist").Range("P6") = "" Sheets("Account Mgmt Checklist").Range("j6") = "" Sheets("Account Mgmt Checklist").Range("o6") = "" Sheets("Account Mgmt Checklist").Range("t9") = "" Sheets("Account Mgmt Checklist").Range("u9") = "" Sheets("Account Mgmt Checklist").Range("Q7") = "" Sheets("Account Mgmt Checklist").Range("a4") = "" Sheets("Account Mgmt Checklist").Range("b4") = "" <<<<< - Rest of Code Sheets("Account Mgmt Checklist").Range("f5") = "" Sheets("Account Mgmt Checklist").Range("h5") = "" Sheets("Account Mgmt Checklist").Range("t5") = "" Sheets("Account Mgmt Checklist").Range("b9") = "" Sheets("Account Mgmt Checklist").Range("a21") = "" Sheets("Account Mgmt Checklist").Range("b21") = "" Sheets("Account Mgmt Checklist").Range("s21") = "" Sheets("Account Mgmt Checklist").Range("a22") = "" Sheets("Account Mgmt Checklist").Range("d9") = "" Sheets("Account Mgmt Checklist").Range("e6") = "" Sheets("Account Mgmt Checklist").Range("e8") = "" ws.Cells(4, 4) = "" ws.Cells(4, 5) = "" ws.Range("h5") = "" ws.Range("d5") = "" ws.Range("p5") = "" ws.Range("p7") = "" ws.Cells(7, 4) = "" ws.Cells(19, 6) = "" ws.Cells(21, 7) = "" <<<<<<<< Rest of Code ws.Range("f21") = "" ws.Cells(31, 6) = "" ws.Cells(31, 7) = "" ws.Cells(53, 6) = "" ws.Range("e6") = "" ws.Cells(7, 4) = "" ws.Range("g7") = "" ws.Range("h7") = "" ws.Cells(30, 6) = "" ws.Cells(29, 6) = "" ws.Range("k7") = "" ws.Cells(28, 6) = "" ws.Cells(28, 6) = "" ws.Cells(28, 7) = "" ws.Cells(30, 6) = "" ws.Cells(22, 6) = "" Application.ScreenUpdating = True End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Halt Rest of Macro after Error €“ Jim Cone review
Jenny B.,
Must be one of those days as the code you posted is not what I supplied. Plus you show the line "Sub click()" twice. However, this may answer your question... Call AddPage Call SaveGame(strNotice) MsgBox strNotice If InStr(1, strNotice, "Try Again Later", vbTextCompare) 0 Then Exit Sub '<<<<New line -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jenny B." wrote in message Hi Jim, I was wondering if you might be able to help me with one last time. Its in regards to the code you helped me with a few days ago. Once €śClick€ť runs and later calls €śSaveGame€ť, I run into a slight problem. If the Docking Station workbook is open, the error messages works just great. However, I have one more application that is included in the €śClick€ť Macro (see below) and runs directly after €śSaveGame€ť. The code after SaveGame is used to clear the Templates and have them ready for the next entry. Along with the error message that comes up when the Docking Station Workbook is open, I would like it to halt the rest of the code so that my data is not lost until Im able to send to the Docking Station. Thanks again so much and I appreciate all your advice to help accomplish this request €“ Jenny B. Option Explicit Sub SaveGame() Dim Dockwkbk As Workbook Dim Actwks As Worksheet Set Actwks = ActiveSheet Application.ScreenUpdating = False Set Dockwkbk = Workbooks.Open(Filename:="C:\Documents and Settings\Me \Desktop\Docking Station.xls") Actwks.Move _ befo=Dockwkbk.Sheets("account Mgmt Log") Call WritetoMainPage Dockwkbk.Save Dockwkbk.Close savechanges:=False Application.ScreenUpdating = True End Sub __________________________________________________ __ Sub click() Sub click() Dim strNotice As String '<<<< Dim ws As Worksheet Set ws = Worksheets("T") strNotice = "This has been sent to Rogers Group for Review" If Range("a3") = "" And Range("a4") = "" Then MsgBox "Please Select NEW or Update" Exit Sub End If If Range("f5") = "" Then MsgBox "Enter Issue Number" Exit Sub End If Application.ScreenUpdating = False If Sheets("Account Mgmt Checklist").Range("a4") Then ws.Cells(4, 4) = "true" If Sheets("Account Mgmt Checklist").Range("a3") Then ws.Cells(4, 5) = "true" If Sheets("Account Mgmt Checklist").Range("a10") Then ws.Cells(8, 5) = "true" If Sheets("Account Mgmt Checklist").Range("b10") Then ws.Cells(8, 1) = "true" If Sheets("Account Mgmt Checklist").Range("t9") Then ws.Cells(8, 7) = "true" If Sheets("Account Mgmt Checklist").Range("u9") Then ws.Cells(8, 9) = "true" If Sheets("Account Mgmt Checklist").Range("a8") Then ws.Cells(7, 4) = "Yes" If Sheets("Account Mgmt Checklist").Range("A7") Then ws.Cells(7, 7) = "Yes" If Sheets("Account Mgmt Checklist").Range("B7") Then ws.Cells(7, 7) = "No" If Sheets("Account Mgmt Checklist").Range("e7") = 1 Then ws.Cells(19, 6) = "DRS" If Sheets("Account Mgmt Checklist").Range("e7") = 2 Then ws.Cells(19, 6) = "Book Entry" If Sheets("Account Mgmt Checklist").Range("e7") = 3 Then ws.Cells(19, 6) = "Restricted" If Sheets("Account Mgmt Checklist").Range("e7") = 4 Then ws.Cells(19, 6) = "ESPP" If Sheets("Account Mgmt Checklist").Range("e7") = 5 Then ws.Cells(19, 6) = "See other Comments" ws.Range("f21").Value = Sheets("Account Mgmt Checklist").Range("P5") If Sheets("Account Mgmt Checklist").Range("t5") = 1 Then ws.Cells(21, 6) = "Common Stock" If Sheets("Account Mgmt Checklist").Range("t5") = 2 Then ws.Cells(21, 6) = "Preferred Stock" ws.Range("d5").Value = Sheets("Account Mgmt Checklist").Range("l4") ws.Range("h5").Value = Sheets("Account Mgmt Checklist").Range("f5") Call AddPage Call SaveGame(strNotice) MsgBox strNotice ' Sheets("Account Mgmt Checklist").Range("A7") = "" Sheets("Account Mgmt Checklist").Range("A9") = "" Sheets("Account Mgmt Checklist").Range("B7") = "" Sheets("Account Mgmt Checklist").Range("a3") = "" Sheets("Account Mgmt Checklist").Range("a8") = "" Sheets("Account Mgmt Checklist").Range("h6") = "" Sheets("Account Mgmt Checklist").Range("L9") = "" <<<<< - Rest of Code Sheets("Account Mgmt Checklist").Range("l4") = "" Sheets("Account Mgmt Checklist").Range("J8") = "" Sheets("Account Mgmt Checklist").Range("L6") = "" Sheets("Account Mgmt Checklist").Range("P5") = "" Sheets("Account Mgmt Checklist").Range("P6") = "" Sheets("Account Mgmt Checklist").Range("j6") = "" Sheets("Account Mgmt Checklist").Range("o6") = "" Sheets("Account Mgmt Checklist").Range("t9") = "" Sheets("Account Mgmt Checklist").Range("u9") = "" Sheets("Account Mgmt Checklist").Range("Q7") = "" Sheets("Account Mgmt Checklist").Range("a4") = "" Sheets("Account Mgmt Checklist").Range("b4") = "" <<<<< - Rest of Code Sheets("Account Mgmt Checklist").Range("f5") = "" Sheets("Account Mgmt Checklist").Range("h5") = "" Sheets("Account Mgmt Checklist").Range("t5") = "" Sheets("Account Mgmt Checklist").Range("b9") = "" Sheets("Account Mgmt Checklist").Range("a21") = "" Sheets("Account Mgmt Checklist").Range("b21") = "" Sheets("Account Mgmt Checklist").Range("s21") = "" Sheets("Account Mgmt Checklist").Range("a22") = "" Sheets("Account Mgmt Checklist").Range("d9") = "" Sheets("Account Mgmt Checklist").Range("e6") = "" Sheets("Account Mgmt Checklist").Range("e8") = "" ws.Cells(4, 4) = "" ws.Cells(4, 5) = "" ws.Range("h5") = "" ws.Range("d5") = "" ws.Range("p5") = "" ws.Range("p7") = "" ws.Cells(7, 4) = "" ws.Cells(19, 6) = "" ws.Cells(21, 7) = "" <<<<<<<< Rest of Code ws.Range("f21") = "" ws.Cells(31, 6) = "" ws.Cells(31, 7) = "" ws.Cells(53, 6) = "" ws.Range("e6") = "" ws.Cells(7, 4) = "" ws.Range("g7") = "" ws.Range("h7") = "" ws.Cells(30, 6) = "" ws.Cells(29, 6) = "" ws.Range("k7") = "" ws.Cells(28, 6) = "" ws.Cells(28, 6) = "" ws.Cells(28, 7) = "" ws.Cells(30, 6) = "" ws.Cells(22, 6) = "" Application.ScreenUpdating = True End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Halt Rest of Macro after Error €“ Jim Cone review
Hi Jim,
Thank you so much and sorry for any confusion I might have added to the post. You are correct that the original you worked on was different. You are also right in noting that it truly has been one those days. Thanks again for your review - Jenny B. "Jim Cone" wrote: Jenny B., Must be one of those days as the code you posted is not what I supplied. Plus you show the line "Sub click()" twice. However, this may answer your question... Call AddPage Call SaveGame(strNotice) MsgBox strNotice If InStr(1, strNotice, "Try Again Later", vbTextCompare) 0 Then Exit Sub '<<<<New line -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jenny B." wrote in message Hi Jim, I was wondering if you might be able to help me with one last time. Its in regards to the code you helped me with a few days ago. Once €śClick€ť runs and later calls €śSaveGame€ť, I run into a slight problem. If the Docking Station workbook is open, the error messages works just great. However, I have one more application that is included in the €śClick€ť Macro (see below) and runs directly after €śSaveGame€ť. The code after SaveGame is used to clear the Templates and have them ready for the next entry. Along with the error message that comes up when the Docking Station Workbook is open, I would like it to halt the rest of the code so that my data is not lost until Im able to send to the Docking Station. Thanks again so much and I appreciate all your advice to help accomplish this request €“ Jenny B. Option Explicit Sub SaveGame() Dim Dockwkbk As Workbook Dim Actwks As Worksheet Set Actwks = ActiveSheet Application.ScreenUpdating = False Set Dockwkbk = Workbooks.Open(Filename:="C:\Documents and Settings\Me \Desktop\Docking Station.xls") Actwks.Move _ befo=Dockwkbk.Sheets("account Mgmt Log") Call WritetoMainPage Dockwkbk.Save Dockwkbk.Close savechanges:=False Application.ScreenUpdating = True End Sub __________________________________________________ __ Sub click() Sub click() Dim strNotice As String '<<<< Dim ws As Worksheet Set ws = Worksheets("T") strNotice = "This has been sent to Rogers Group for Review" If Range("a3") = "" And Range("a4") = "" Then MsgBox "Please Select NEW or Update" Exit Sub End If If Range("f5") = "" Then MsgBox "Enter Issue Number" Exit Sub End If Application.ScreenUpdating = False If Sheets("Account Mgmt Checklist").Range("a4") Then ws.Cells(4, 4) = "true" If Sheets("Account Mgmt Checklist").Range("a3") Then ws.Cells(4, 5) = "true" If Sheets("Account Mgmt Checklist").Range("a10") Then ws.Cells(8, 5) = "true" If Sheets("Account Mgmt Checklist").Range("b10") Then ws.Cells(8, 1) = "true" If Sheets("Account Mgmt Checklist").Range("t9") Then ws.Cells(8, 7) = "true" If Sheets("Account Mgmt Checklist").Range("u9") Then ws.Cells(8, 9) = "true" If Sheets("Account Mgmt Checklist").Range("a8") Then ws.Cells(7, 4) = "Yes" If Sheets("Account Mgmt Checklist").Range("A7") Then ws.Cells(7, 7) = "Yes" If Sheets("Account Mgmt Checklist").Range("B7") Then ws.Cells(7, 7) = "No" If Sheets("Account Mgmt Checklist").Range("e7") = 1 Then ws.Cells(19, 6) = "DRS" If Sheets("Account Mgmt Checklist").Range("e7") = 2 Then ws.Cells(19, 6) = "Book Entry" If Sheets("Account Mgmt Checklist").Range("e7") = 3 Then ws.Cells(19, 6) = "Restricted" If Sheets("Account Mgmt Checklist").Range("e7") = 4 Then ws.Cells(19, 6) = "ESPP" If Sheets("Account Mgmt Checklist").Range("e7") = 5 Then ws.Cells(19, 6) = "See other Comments" ws.Range("f21").Value = Sheets("Account Mgmt Checklist").Range("P5") If Sheets("Account Mgmt Checklist").Range("t5") = 1 Then ws.Cells(21, 6) = "Common Stock" If Sheets("Account Mgmt Checklist").Range("t5") = 2 Then ws.Cells(21, 6) = "Preferred Stock" ws.Range("d5").Value = Sheets("Account Mgmt Checklist").Range("l4") ws.Range("h5").Value = Sheets("Account Mgmt Checklist").Range("f5") Call AddPage Call SaveGame(strNotice) MsgBox strNotice ' Sheets("Account Mgmt Checklist").Range("A7") = "" Sheets("Account Mgmt Checklist").Range("A9") = "" Sheets("Account Mgmt Checklist").Range("B7") = "" Sheets("Account Mgmt Checklist").Range("a3") = "" Sheets("Account Mgmt Checklist").Range("a8") = "" Sheets("Account Mgmt Checklist").Range("h6") = "" Sheets("Account Mgmt Checklist").Range("L9") = "" <<<<< - Rest of Code Sheets("Account Mgmt Checklist").Range("l4") = "" Sheets("Account Mgmt Checklist").Range("J8") = "" Sheets("Account Mgmt Checklist").Range("L6") = "" Sheets("Account Mgmt Checklist").Range("P5") = "" Sheets("Account Mgmt Checklist").Range("P6") = "" Sheets("Account Mgmt Checklist").Range("j6") = "" Sheets("Account Mgmt Checklist").Range("o6") = "" Sheets("Account Mgmt Checklist").Range("t9") = "" Sheets("Account Mgmt Checklist").Range("u9") = "" Sheets("Account Mgmt Checklist").Range("Q7") = "" Sheets("Account Mgmt Checklist").Range("a4") = "" Sheets("Account Mgmt Checklist").Range("b4") = "" <<<<< - Rest of Code Sheets("Account Mgmt Checklist").Range("f5") = "" Sheets("Account Mgmt Checklist").Range("h5") = "" Sheets("Account Mgmt Checklist").Range("t5") = "" Sheets("Account Mgmt Checklist").Range("b9") = "" Sheets("Account Mgmt Checklist").Range("a21") = "" Sheets("Account Mgmt Checklist").Range("b21") = "" Sheets("Account Mgmt Checklist").Range("s21") = "" Sheets("Account Mgmt Checklist").Range("a22") = "" Sheets("Account Mgmt Checklist").Range("d9") = "" Sheets("Account Mgmt Checklist").Range("e6") = "" Sheets("Account Mgmt Checklist").Range("e8") = "" ws.Cells(4, 4) = "" ws.Cells(4, 5) = "" ws.Range("h5") = "" ws.Range("d5") = "" ws.Range("p5") = "" ws.Range("p7") = "" ws.Cells(7, 4) = "" ws.Cells(19, 6) = "" ws.Cells(21, 7) = "" <<<<<<<< Rest of Code ws.Range("f21") = "" ws.Cells(31, 6) = "" ws.Cells(31, 7) = "" ws.Cells(53, 6) = "" ws.Range("e6") = "" ws.Cells(7, 4) = "" ws.Range("g7") = "" ws.Range("h7") = "" ws.Cells(30, 6) = "" ws.Cells(29, 6) = "" ws.Range("k7") = "" ws.Cells(28, 6) = "" ws.Cells(28, 6) = "" ws.Cells(28, 7) = "" ws.Cells(30, 6) = "" ws.Cells(22, 6) = "" Application.ScreenUpdating = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP returns #N/A and causes VBA routine to halt | Excel Worksheet Functions | |||
| How do I set up a sales chart in cone format? | Charts and Charting in Excel | |||
| "No RETURN() or HALT() function found on macro sheet." | Excel Worksheet Functions | |||
| No RETURN() or HALT() function found on macro sheet | Excel Discussion (Misc queries) | |||
| VBA: syntax to review macro names in a file? | Excel Discussion (Misc queries) | |||
Linear Mode
