I have a set of code to save a certain workbook changes, when i run it in
step mode (F8). The code works without a single problem, not even needing to
resort to my error handling. When I run it from the Embedded control on the
worksheet that activates, it will not run in the background and simply
crashes Excel every single time. HELP!!! I have included the sub below:


On Error GoTo oops
Application.ScreenUpdating = False
msg = "Are you sure you want to save these changes to your Resale SOF?"
Style = vbYesNo + vbDefaultButton2
title = "Save?"
Response = MsgBox(msg, Style, title)
ini = Range("e3").Value
If taxx = True Then taxx1 = True
If taxx = False Then taxx1 = False
nob = ThisWorkbook.Name
If Response = vbYes Then
With ActiveWorkbook.Sheets(1).Range("AA15:AC22")
.Font.Size = 10
.Font.ColorIndex = 0
.ClearContents
End With
ActiveWorkbook.Sheets(1).Cells(Sheets(1).Range("ao 1").Value + 61,
13).Value = ""
ActiveWorkbook.Sheets(1).Range("x1").Value = ini
ActiveWorkbook.Sheets(1).Activate
If taxx1 = True Then ActiveWorkbook.Sheets(1).TAXEX = True
If taxx1 = False Then ActiveWorkbook.Sheets(1).TAXEX = False
Sheets(1).Range("ac9").Value = ""
ActiveSheet.Range("d2").Select
On Error Resume Next
Kill "C:\lists\Resale SOF.xlt"
Kill "c:\lists\123.xls"
On Error GoTo oops
Workbooks(nob).SaveAs Filename:="c:\lists\Resale SOF.xlt"
Workbooks("Resale SOF.xlt").SaveAs Filename:="c:\lists\123.xls"
ActiveWorkbook.Sheets(1).Activate
ThisWorkbook.Workbook_Open
Else
Application.ScreenUpdating = True
Exit Sub
End If
Application.ScreenUpdating = True
Exit Sub
oops:
MsgBox "I'm sorry this is a problem saving your changes. " +
Str(Err.Number) + "-" + Err.Description
Application.ScreenUpdating = True


P.S. Yeah I know the code is simple but i learned computers with Old Basic :p)

Without looking at your code (only your description)...

There's a bug in xl97 that concerns macros called from controls placed on a
worksheet (from the control toolbox toolbar).

If that's the case with you (like a commandbutton), you can change the control's
takefocusonclick property to false.

Or add:

activecell.activate

to the top of your procedure.

If that's not it, you may want to post the portion of the code that's blowing up
and how it's initiated.



ben wrote:

I have a set of code to save a certain workbook changes, when i run it in
step mode (F8). The code works without a single problem, not even needing to
resort to my error handling. When I run it from the Embedded control on the
worksheet that activates, it will not run in the background and simply
crashes Excel every single time. HELP!!! I have included the sub below:

On Error GoTo oops
Application.ScreenUpdating = False
msg = "Are you sure you want to save these changes to your Resale SOF?"
Style = vbYesNo + vbDefaultButton2
title = "Save?"
Response = MsgBox(msg, Style, title)
ini = Range("e3").Value
If taxx = True Then taxx1 = True
If taxx = False Then taxx1 = False
nob = ThisWorkbook.Name
If Response = vbYes Then
With ActiveWorkbook.Sheets(1).Range("AA15:AC22")
.Font.Size = 10
.Font.ColorIndex = 0
.ClearContents
End With
ActiveWorkbook.Sheets(1).Cells(Sheets(1).Range("ao 1").Value + 61,
13).Value = ""
ActiveWorkbook.Sheets(1).Range("x1").Value = ini
ActiveWorkbook.Sheets(1).Activate
If taxx1 = True Then ActiveWorkbook.Sheets(1).TAXEX = True
If taxx1 = False Then ActiveWorkbook.Sheets(1).TAXEX = False
Sheets(1).Range("ac9").Value = ""
ActiveSheet.Range("d2").Select
On Error Resume Next
Kill "C:\lists\Resale SOF.xlt"
Kill "c:\lists\123.xls"
On Error GoTo oops
Workbooks(nob).SaveAs Filename:="c:\lists\Resale SOF.xlt"
Workbooks("Resale SOF.xlt").SaveAs Filename:="c:\lists\123.xls"
ActiveWorkbook.Sheets(1).Activate
ThisWorkbook.Workbook_Open
Else
Application.ScreenUpdating = True
Exit Sub
End If
Application.ScreenUpdating = True
Exit Sub
oops:
MsgBox "I'm sorry this is a problem saving your changes. " +
Str(Err.Number) + "-" + Err.Description
Application.ScreenUpdating = True

P.S. Yeah I know the code is simple but i learned computers with Old Basic :p)

--

Dave Peterson