Excel crashes after "successfully" running macro..

[djradam]

Guys and Gals, I did a search and didn't really see anything that helped me (I hope I didn't miss anything...).

I wrote a macro a few weeks ago and everything worked fine. The overall macro was this:
call a form, initialize the form, run the code (the code looked to # of sheets and put variables into the header and footer on each sheet), exit and done.

now when I run it I have two problems. first, I can't update the page footer. I get an error with .LeftFooter. I'm not too concerned with this because it's a small problem.

The problem I'm having is that after all the sheets update, Excel will crash (sometimes) when I click on other tabs. I first found this out when trying to run another macro, all this macro did was Sheets("xx").select . So I think it's some kind of error when after running this macro without errors, then trying to switch to another tab/sheet.

Here is my code for the form (I used most of it from a progress bar example and tweaked it for my use... but it did work in my form for a while):

____________________________________________________________________________
Option Explicit




Private Sub UserForm_Activate()

Application.Cursor = xlWait
ProgressBar.MousePointer = fmMousePointerHourGlass
DoEvents
Call CalculateData
Application.Cursor = xlDefault
Unload Me

End Sub







Private Sub UserForm_Initialize()

TextBox2.Left = TextBox1.Left
TextBox2.Top = TextBox1.Top + 3
TextBox2.Width = 0

End Sub









Sub CalculateData()

Dim i, n As Integer
Dim sheetCount As Integer
Dim agencyName, agentName, DOF, FDL, territory, confidential As String

i = 1
n = 1
sheetCount = Sheets.Count

agencyName = Range("B2").Value
agentName = Range("B4").Value
DOF = Range("B6").Value
FDL = Range("B8").Value
territory = Range("B10").Value
confidential = Range("B24").Value


For i = 1 To sheetCount

ProgressBar.TextBox2.Width = (n / (sheetCount * 2)) * 200
ProgressBar.status.Caption = Int(n / (sheetCount * 2) * 100) & "% Complete"
DoEvents
n = n + 1
With Sheets(i).PageSetup

.LeftHeader = agencyName & Chr(10) & agentName & Chr(10) & DOF & Chr(10) & FDL & Chr(10) & territory
End With

ProgressBar.TextBox2.Width = (n / (sheetCount * 2)) * 200
ProgressBar.status.Caption = Int(n / (sheetCount * 2) * 100) & "% Complete"
DoEvents
n = n + 1
With Sheets(i).PageSetup

' .LeftFooter = confidential '& Chr(10) & "&D &T" & Chr(10) & "&Z&F"
End With


Next i


End Sub
____________________________________________________________________________


Again, this macro runs fine (when .LeftFooter is commented out). It's after this is said and done, I crash when I try to select another tab.

Additional information that might help is that I have some public variables defined, but they are not used in this macro.


I appreciate any help or suggestions.

 

[Blade Hunter]

Why is there a single quote after confidential?

Also, what is in cell B24?

 

[djradam]

Why is there a single quote after confidential?

Also, what is in cell B24?

oh, the ' is to comment out the remainder of the line, I was thinking MAYBE it was something after that variable. so it was just there for testing


B24 is a text cell, I currently have it as "confidential1" but will be longer as a confidentiality statement.

at work we use this thing called Interwoven. It's more of a network repository. the macro runs fine through Interwoven. When I download to my desktop, it does not run fine. This very exact macro DID run find as of last week.

I'll neeo to look at the macros I wrote before, it's just a hassle since they're all simple (select sheet, hide rows, that's about it)

 

[djradam]

An update .... it would still be appreciated if someone had some insight (knowledge, past experience). I went through my code and other macros and I narrowed the problem down to the code I posted earlier.

In the INITIALIZE routine, I commented out the mouse pointer related info

in the CalculateData routine I commented out anything progressbar related (so just defining variables and updating header /footer). NO PROBLEMS running this.

When I bring in the progressbar code:
"
ProgressBar.TextBox2.Width = (n / (sheetCount * 2)) * 200
ProgressBar.status.Caption = Int(n / (sheetCount * 2) * 100) & "% Complete"
DoEvents
n = n + 1
"

it RUNS, but then after I'm in Excel, it crashes about 15-20 seconds later.


If I remove DoEvents, the progress bar doesn't update.



For the time being, I'll just keep a progress bar out of this whole thing and all my code runs fine. I just wish I could have some kind of progress bar, or even figure out what is so wrong with these 4 lines to make it crash Excel after successfully running.

 

[Blade Hunter]

Are you setting screen updating to false anywhere? I am unsure why the status bar (Or text box as it is) resize is not showing without a DoEvents.

Try changing the textbox to a label and see if that works