Hello,
I have one last question in regards to creating an Error Handler for an
existing Application. Im 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 Im 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 Im
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 _
befo=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 Rogers Group for Review"

Application.ScreenUpdating = True
End Sub

Might want to post this in the proper
group:microsoft.public.excel.programming.

"Jenny B." wrote in message
...
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 _
befo=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

Thank you so much for the code. It works terrifice except for one thing (my
fault of couse). I forgot to mention that I have yet another routine that
runs before "Save Game" called "Add Page". This runs so that the dettached
doc to the Docking Station is a duplicate of the oringal "T" page which is
hidden in the "Account Entry Database" workbook.

Since AddPage runs before SaveGame, I'm left with a copy sitting in the
workbook which I want to prevent. I have to run AddPage first otherwise
SaveGame would launch off my orginal hidden template sheet. I added a small
change to Save Game requesting the ActivePage be deleted if it meets the open
workbook critera. The only problem that presents is the user by nature
doesn't understand the message and would most likely choose not to delete the
sheet since they wouldn't understand the message.

Is there anyway to suppress the message that comes up with the Delete
Request?

Thank you again - Jenny B.

Sub AddPage()
Sheet6.Copy after:=Sheet6
ActiveSheet.Name = Sheet6.Range("h5").Value

ActiveSheet.Visible = True
End Sub


Sub SaveGame(ByRef strMsg As String) '<<<<
Dim Dockwkbk As Workbook
Dim Actwks As Worksheet

Set Actwks = ActiveSheet
Application.ScreenUpdating = False

On Error Resume Next
Set Dockwkbk = Workbooks("Docking Station.xls")
If Not Dockwkbk Is Nothing Then
On Error GoTo 0
strMsg = "Workbook in use. Try Again Later"
Set Dockwkbk = Nothing
Actwks.Delete `<<<<<<<<<<<<<<<< New Request on open workbook discovery`
Exit Sub
Else
On Error GoTo 0
Set Dockwkbk = Workbooks.Open(Filename:= _
"C:\Documents and Settings\Me\Desktop\Docking Station.xls")
End If

Actwks.Move _
befo=Dockwkbk.Sheets("account Mgmt Log")
Call WritetoMainPage
Dockwkbk.Save
Dockwkbk.Close savechanges:=False
Application.ScreenUpdating = True
End Sub


"Jim Cone" wrote:

Jenny B,
I can't test it, you can give it a try.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

'--
Sub SaveGame(ByRef strMsg As String) '<<<<
Dim Dockwkbk As Workbook
Dim Actwks As Worksheet

Set Actwks = ActiveSheet
Application.ScreenUpdating = False

On Error Resume Next '<<<< all new code below
Set Dockwkbk = Workbooks("Docking Station.xls")
If Not Dockwkbk Is Nothing Then
On Error GoTo 0
strMsg = "Workbook in use. Try Again Later"
Set Dockwkbk = Nothing
Exit Sub
Else
On Error GoTo 0
Set Dockwkbk = Workbooks.Open(Filename:= _
"C:\Documents and Settings\Me\Desktop\Docking Station.xls")
End If

Actwks.Move _
befo=Dockwkbk.Sheets("account Mgmt Log")
Call WritetoMainPage
Dockwkbk.Save
Dockwkbk.Close savechanges:=False
Application.ScreenUpdating = True
End Sub
'--

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 '<<<<

Application.ScreenUpdating = True
End Sub
'--




"Jenny B."
wrote in message
Hello,
I have one last question in regards to creating an Error Handler for an
existing Application. Im 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 Im 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 Im
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.
-snip-

"Is there anyway to suppress the message that comes up with the Delete Request?"

Application.DisplayAlerts = False
Actwks.Delete
Application.DisplayAlerts = True

Note: "Application.DisplayAlerts = True" should be included in your
error handling routine if you decide to create one.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Jenny B."
wrote in message
Thank you so much for the code. It works terrifice except for one thing (my
fault of couse). I forgot to mention that I have yet another routine that
runs before "Save Game" called "Add Page". This runs so that the dettached
doc to the Docking Station is a duplicate of the oringal "T" page which is
hidden in the "Account Entry Database" workbook.

Since AddPage runs before SaveGame, I'm left with a copy sitting in the
workbook which I want to prevent. I have to run AddPage first otherwise
SaveGame would launch off my orginal hidden template sheet. I added a small
change to Save Game requesting the ActivePage be deleted if it meets the open
workbook critera. The only problem that presents is the user by nature
doesn't understand the message and would most likely choose not to delete the
sheet since they wouldn't understand the message.

Is there anyway to suppress the message that comes up with the Delete
Request?

Thank you again - Jenny B.
-snip-

Thank you so much - that worked great. Every visit here is such a great
learning experience and I always take away such quality information.
Everything I've ever learned here is much more valuable than any other
programming related book I've ever purchased.

Thank you again for your continued advice and review - it's greatly
appreciated :~)


Jenny B.


"Jim Cone" wrote:


"Is there anyway to suppress the message that comes up with the Delete Request?"

Application.DisplayAlerts = False
Actwks.Delete
Application.DisplayAlerts = True

Note: "Application.DisplayAlerts = True" should be included in your
error handling routine if you decide to create one.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Jenny B."
wrote in message
Thank you so much for the code. It works terrifice except for one thing (my
fault of couse). I forgot to mention that I have yet another routine that
runs before "Save Game" called "Add Page". This runs so that the dettached
doc to the Docking Station is a duplicate of the oringal "T" page which is
hidden in the "Account Entry Database" workbook.

Since AddPage runs before SaveGame, I'm left with a copy sitting in the
workbook which I want to prevent. I have to run AddPage first otherwise
SaveGame would launch off my orginal hidden template sheet. I added a small
change to Save Game requesting the ActivePage be deleted if it meets the open
workbook critera. The only problem that presents is the user by nature
doesn't understand the message and would most likely choose not to delete the
sheet since they wouldn't understand the message.

Is there anyway to suppress the message that comes up with the Delete
Request?

Thank you again - Jenny B.
-snip-