Excel keeps crashing when spreadsheet is used

[t16turbo]

I have some VBA that refreshes several queries on several worksheets.
The VBA then saves the spreadsheet as a given name on the C:\ drive of my PC.

When it runs it just replaces the source spreadsheet with the saved one.

There's a couple of problems now.
1) there is a combo box on the first worksheet. If I select a different worksheet, I can still see the combo box on the screen in the same space - obscuring all of the worksheet data.
2) If I select a sheet sometimes, Excel just crashes.

If I then close down Excel, reopen the saved file and disable the macros, its all fine.

how can I tell VBA to NOT enable the macros when it saves a file?
is it just a case of copying all data, and pasting it into a new spreadsheet before saving it??

 

[notuserfriendly]

The information you give doesn't say that much. Some
code or adding more precise information would help a lot.

For example do you open all worksheets when using the macro or does it open and refresh the queries automatically?

1) The combobox issue I really don't know, never had that problem. Otherwise it would be nice to know how you created the combobox?

2) If Excel crashes when you select a sheet then you should check (with the debugger) what happens when you select it. Worksheet.Activate or something like that.

Normally you enable macros when you open a file. It
all depends on your macro security settings. If they are high then you always have to allow unsigned macros.

 

[WalterContrata]

t16turbo,

I'm just guessing here, but I've had various trouble with Excel after workbooks became to large: too much data, too many charts, too many formulae or user defined functions. Recently some discussion of this occured in thread68-947957

If you like, you can have the macro delete itself from the workbook before saving, using a subroutine like the following. Of course, you'll want to have all of the code in the active workbook backed up somewhere before trying it. And you should add a reference to Microsoft Visual Basic 6.0 Extensibility to the project.

Option Explicit

Sub DeleteCode()

    Dim oWB As Workbook
    Dim oVBProject As VBProject
    Dim oVBComponent As VBComponent
    Dim oCodeModule As CodeModule
        
    Set oWB = ActiveWorkbook
    
    Set oVBProject = oWB.VBProject
    
    For Each oVBComponent In oVBProject.VBComponents
        Set oCodeModule = oVBComponent.CodeModule
        oCodeModule.DeleteLines _
            startline:=1, Count:=oCodeModule.CountOfLines
    Next oVBComponent

    Set oVBComponent = Nothing
    Set oVBProject = Nothing
        
    Set oWB = Nothing

End Sub

Good Luck,
Walter