Saving Cause Runtime Error

[mchow2469]

Hi,

I have a fairly large xl file with plenty of macros (2mb). What I can not figure out is when I save my workbook to a different name using the 'save as' option, I get runtime errors.

First one is it complains about the scroll column proprety I use:

With ActiveWindow 
    .ScrollColumn = incrcol 
    .ScrollRow = 1 
End With

I am assuming when I save my workbook in a different name, the activewindow changes? Is there another way around this?

Second error I get is 'unable to get chart object' from below:

Sheets(currentsheetname).ChartObjects(1).Activate

I get this error for every chart object in my worksheet. Why can't I access it if I save my workbook to another name?

Any help will be greatly appreciated thanks!!

[JustinLabenne]

Sounds like you have some event codes that run when your saving the file. When you look in the THisWorkbook module, do you have any BeforeSave events?

[mchow2469]

How do I prevent these events from running when I save? I do have some events such as worksheet_activate, workbook_open, button_click events, etc.

[mchow2469]

btw,

I have two worksheets that do similar tasks of activating charts and scrolling. I noticed when I save my workbook as another name, the error occurs in the other sheet, and vice versa if my activesheet was the other sheet.

Is there some sort of linkage when I save my workbook using the 'save as' option with other sheets? So confused...

[VBAhack]

You have a number of choices:

1. Delete all of the event handling routines - not recommended
2. Turn off events using Application.EnableEvents = False
3. Comment out selected event handlers w/ ':

'Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _
        ByVal SaveAsUI As Boolean, Cancel as Boolean)
    'a = MsgBox("Do you really want to save the workbook?", vbYesNo)
    'If a = vbNo Then Cancel = True
'End Sub

VBAhack

[mchow2469]

Thanks for the helpful tips.
Well I shouldn't do the first one because then my whole sheet would not work.
I tried using the enableevents property, setting that to false.

I get different errors when I do that:

' runtime error: unable to set Characters Property  of Title
with activechart
.title.characters.text = "chart title"
end with

Also, I do not have an event handling beforesave, so option 3 probably wouldn't work. Unless you feel adding a beforesave event would work better then can you elaborate on it more? Thanks!

[VBAhack]

No WorkbookBeforeSave, eh? Quite a mystery you have, my friend. It was a logical choice (like JustinLabenne suggested in an earlier post) since the errors only occur when you perform a 'Save As'. Hmmmmm, well one other possibility is to print out a msgbox in each event code to see which one fires last before you get the error. Kind of tedious, but no other suggestion comes to mind. It means putting something like the following at the beginning of each event handler:

Msgbox "This is xxx"

Where xxx is the name of the event handler. The last one to post before the error message occurs would be the first place to look. This assumes an event handler is the cause. Sorry, can't think of anything else. Maybe others can think of something.

VBAhack

[mchow2469]

Ok, I figured out what event is being called when I use the save as option.

Currently I have two sheets which consists of 10 embedded charts on each page. They both contain the same vba logic, but plot different data.

On each sheet, each chart has 10 associated comboboxes where the user can click which data to plot ( which are located on the same sheet).

If I am in one sheet, the combobox's events located in the OTHER sheet is called, i.e. select_change event. In each event, I activate the chart, which is specifically where the runtime error occurs. Why would the select change event for comboboxes be ran after I save my workbook in another name?

[JustinLabenne]

Can you attach a sample of your workbook here? It may be a question of minor corruption in the workbook (possible, lots of charts, event handlers galore). That is worst case, it sounds like the charts or something within the code may be hard coded and referencing the old file name for some reason.

Best bet, attach a sample, or post oodles of your code on the board. A sample would be loads easier for people to help with though...........

[mchow2469]

Hi,

because of the size quota, I was wondering if I can just email you my workbook. Posting parts of my code might be a little confusing since you will not get the general idea of what is goign on. It will be greatly appreciated if you can see what is going on in my code. I want to get your permission first before I email it to you.

[mchow2469]

Hi,

I actually fixed problem. I just added a flag so some of the events will not run at certain times. Thanks anyways!