J
Jenny B.
Hello,
I have one last question in regards to creating an Error Handler for an
existing Application. I’m using a “Call†command for the below Macro
(“SaveGameâ€) inside a larger routine (noted below - Sub "Click"). Dave
Peterson was kind enough to help me with the SaveGame sub and now I’m hoping
I can request one further review.
The sub “Click†is writing data to the “Account Mgmt Checklist†and later
Calls “SaveGame†that sends the data to the Docking Station noted below. My
Problem is if the Docking Station is open – I get an error. I know how to
write a basic handler and have a message box come up in place of it, but I’m
more or less looking to have it cease the process and then provide an
alternative and message. Since the normal working order would be - write to
Checklist, save to Docking Station and then pop-up a message to alert the
user this has been sent to a certain workgroup – I would instead like it to
not only show a message box (such as Try again later), but stop the writing
processes and lastly NOT send the Msgbox noted below if an error is incurred.
Thanks once again in advance for all of your thoughts and advice – 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()
Dim ws As Worksheet
Set ws = Worksheets("T")
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
MsgBox "This has been sent to Roger’s Group for Review"
Application.ScreenUpdating = True
End Sub
I have one last question in regards to creating an Error Handler for an
existing Application. I’m using a “Call†command for the below Macro
(“SaveGameâ€) inside a larger routine (noted below - Sub "Click"). Dave
Peterson was kind enough to help me with the SaveGame sub and now I’m hoping
I can request one further review.
The sub “Click†is writing data to the “Account Mgmt Checklist†and later
Calls “SaveGame†that sends the data to the Docking Station noted below. My
Problem is if the Docking Station is open – I get an error. I know how to
write a basic handler and have a message box come up in place of it, but I’m
more or less looking to have it cease the process and then provide an
alternative and message. Since the normal working order would be - write to
Checklist, save to Docking Station and then pop-up a message to alert the
user this has been sent to a certain workgroup – I would instead like it to
not only show a message box (such as Try again later), but stop the writing
processes and lastly NOT send the Msgbox noted below if an error is incurred.
Thanks once again in advance for all of your thoughts and advice – 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()
Dim ws As Worksheet
Set ws = Worksheets("T")
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
MsgBox "This has been sent to Roger’s Group for Review"
Application.ScreenUpdating = True
End Sub