Halt Rest of Macro after Error – Jim Cone review

J

Jenny B.

Hi Jim,

I was wondering if you might be able to help me with one last time. It’s 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 I’m 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 _
before:=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 Roger’s 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
 
J

Jim Cone

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. It’s 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 I’m 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 _
before:=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 Roger’s 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
 
J

Jenny B.

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error Handler Question 5
Questioin for Ron de Bruin 7
Not detecting Open Workbook - Why 7
Macro Help CONT 2003 1
Run time Error - Application Defined or Object Defined Error Excel Vba 0
Button for every sheet one new email 0
Select data to imput to macro 1
Runtime Error 1004:Method 'Range' of object'_Worksheet' Failed 4

Top