Replies

HP Printer as default was causing my Excel errors and crashing.
I had a few lines that set up margins, headers.
By turning off communication with the printer during these commands it allowed my macro to run without losing its mind.

Code:

[B]    Application.PrintCommunication = False
[/B]    With ActiveSheet.PageSetup
        .PrintTitleRows = “$1:$8”
        .PrintTitleColumns = “”
        .LeftFooter = “&8Printed: &D”
        .RightFooter = “&8Page &P of &N”
    End With
[B]    Application.PrintCommunication = True
[/B]

Maybe this will also solve your crash problem?

Recreated all my files (addins,templates,Access databases) from “parts” in Office 2010 to eliminate any conversion issues from prior Office versions.
That got me to the point where my macro runs without crashing Excel during the macro.

But now I have the same problem reported over at this thread:
http://www.ozgrid.com/forum/showthread/?t=156653

Macro runs first time OK. Second time the custom form comboboxes do not contain the array of choices. Subsequently, closing the workbook causes Excel to crash.

I’m going to try uninstalling the last Windows updates from earlier this week. It must be an environmental problem with my laptop. Others are functioning just fine. One other user is having similar problems as I am.

Corporate IT installed AV…Sophos Protection not possible to disable.
I have just rolled out this Excel-based Add-in app to a new group of users and only one user so far is reporting this behavior.
I am very concerned that inexplicable crashing will become rampant and I can’t do any further development until its resolved.
Is it possible to report behavior like this to Microsoft Office tech support?

This is is my Windows update history for the week:

Security Update for Windows 7 (KB2536276)
Installation date: ‎8/‎15/‎2011 10:00 AM
Installation status: Successful
Update type: Important
A security issue has been identified that could allow an unauthenticated remote attacker to compromise your system and gain control over it. You can help protect your system by installing this update from Microsoft. After you install this update, you may have to restart your system.
More information: http://go.microsoft.com/fwlink/?LinkId=215841

Security Update for Microsoft Chart Controls for Microsoft .NET Framework 3.5 Service Pack 1 (KB2500170)
Installation date: ‎8/‎15/‎2011 10:00 AM
Installation status: Successful
Update type: Important
A security issue has been identified that could allow an attacker to compromise your Windows-based system that is running the Microsoft Chart Controls for Microsoft .NET Framework 3.5 Service Pack 1 and gain access to information. You can help protect your computer by installing this update from Microsoft. After you install this item, you may have to restart your computer.
More information: http://go.microsoft.com/fwlink/?LinkId=221536Another user in the company first reported this error on his laptop yesterday when I still was functioning just fine. So I know its not just my machine.
Other users are not crashing with the same Add-In macros. Which is why I’m suspicious of op system or Office or anti-virus software updates forced by IT to my laptop.

Setting the “Trusted Locations” for my Add-In folder is a great idea. I did that. And then it actually was able to run successfully once.
But it is now back to crashing. It crashes at different points…not always the same amount of progress through macro.
I have milestones displayed in the status message so I can tell how far it gets. I put in a MsgBox prompt just before the area that seemed to be the crashpoint, but it sometimes fails to get that far.

I’m scanning for viruses.

I also have the same problem starting last night. Another user within the company reported macro crashing Excel. I shared his screen and watched it happen. Then last night it began to happen to me too. Macros had been running fine previously for all. Macros run fine when stepping through. In fact, the crash occurs at different stages in the code if I run to a breakpoint and then F5 to continue unimpeded…Excel completely crashes. We do have corporate IT sending Windows 7 updates without warning and we did have a boot-required update this week. So I’m suspecting something in our Windows environment (Sophos, McAfee Endpoint Encryption) is the cause. Workarounds like scattered “DoEvents” in code, or tinkering with code itself may work, but this feels like a bug that needs to be reported to Microsoft. Our Help Desk is not able to handle this. How do we report something like this to Microsoft without a $250 charge for the privilege?

(Edited by Phil Rabichow on 02-Feb-04 13:35. if you have Option Explicit set in your VBE, you had to add the following two lines somewhere near the beginning:

Dim fraction As String, slashPosition As String
Dim preSlash As String, postSlash As String
)

The latest newsletter discussed formatting text with sub/superscript to make nicer looking fractions. Here’s a macro to automate that process for any combination of numbers needed:

Sub EnterFraction()
‘Add this macro to your Normal.dot
‘Assign a keystroke like Alt-/ to this macro (Tools…Customize…Keyboard)

‘Prompt user for the text to format as a fraction
fraction = InputBox(“Enter fraction text (ex: 1/2, 5/32)”)
‘Find the position of the slash in the text
slashPosition = InStr(fraction, “/”)
‘Quit if no slash in the text
If slashPosition = 0 Then Exit Sub

preSlash = Left(fraction, slashPosition – 1)
postSlash = Right(fraction, Len(fraction) – slashPosition)
With Selection
‘Type text before the slash as superscript
.Font.Superscript = True
.TypeText preSlash

‘Type the slash as normal text
.Font.Superscript = False
.TypeText “/”

‘Format text after the slash
.Font.Subscript = True
.TypeText postSlash

‘Continue with font back to normal
.Font.Subscript = False
End With

End Sub

If Selection.ShapeRange.Fill.GradientVariant > 0 Then MsgBox “Gradient!”

{IF {SECTIONPAGES} = {PAGE} {FILENAME p}}

… works only if you reset the page numbering to 1 at the beginning of each section.

Another solution is to just put the {FILENAME} field wherever the user wants to see it and quit trying to automate it in the footer. A text box that is positioned at the bottom margin of the page can simulate a footer. Add one to the end of each section. Store the textbox as an AutoText to make it easier to pop in wherever you want.

Many years ago we had a high school student as a summer intern. He thought he’d teach himself to make macros. So he recorded a macro. He just did a little stream-of-consciousness typing, ended the recording and assigned it the “Ctrl-J” keystroke. Unfortunately, the macro was stored to the server shared area.

So one day I get an irate call from the President’s secretary. “Someone is out to get me! And it’s not funny! Who is doing this?”

Apparently, she accidentally typed Ctrl-J instead of Shift-J while typing and suddenly, the following text appeared in her document:

“You are a dipsh*t. Stupid stupid stupid. La la la.”

That took a little investigating to figure out and a lot of apologizing to the poor woman who thought someone was sending text directly to her PC.

Anyhoo, your example seems like it might be the same sort of thing. Especially since it’s on multiple lines (not just a hijacked date function). Someone at some time may have created a little test macro and it accidentally got executed. If you have a irate president’s secretary yelling at you it might warrant doing a Windows Explorer search of files containing a phrase of that text to find the source. Maybe someone really loves adding it to their spreadsheets with a quick shortcut keystroke.

Hey Don,

I am in awe of the elegant and mathematical formulas that complex minds concoct. But I’m a simple country girl. Some would say simple-minded. So this is how I’d solve your problem: a couple columns like the ones suggested before to get daily counts and then a Pivot Table to do all the work of adding it up by teacher for me. Simple AND lazy!

See attachment

Leanne:

This macro will clear out the ranges for ANY grid in the workbook. Since the user cuts & pastes them, I’m assuming all 55 look the same in layout.
The user must have a “Station” cell selected to make sure you’re in the right position to use Offset. I formatted cell A7 with yellow and bold to make it an obvious “hotspot” and then Format…Style…[type the name of the new style]…OK. I called the style “Station” in this case. Then you can apply that style to all the othr 54 places you need it.

Add a toolbar button that runs the macro below. If the user is on one of the hotspots, the macro will clear all the data from that section. Only ONE MACRO!

Sub ClearTheDeck()
'Use a Style called "Station" to identify the top left cell of the range to clear
'I used yellow and bold to make it an obvious hotspot.

If Selection.Style = "Station" Then

'OPTIONAL: Make sure the user really wants to whack their data
bSure = MsgBox("Are you sure you want to clear this section of data?", vbYesNo, "Clear Data")
If bSure = vbNo Then Exit Sub
    
    'C7:K14 or it's equivalent on other rows
    Range(Selection.Offset(0, 2), Selection.Offset(7, 10)).ClearContents
    'N7:N14
    Range(Selection.Offset(0, 13), Selection.Offset(7, 13)).ClearContents
    'O8
    Selection.Offset(1, 14).ClearContents
End If

End Sub

Uh…Holstein Falls — the Cow Tipping Capital of the World! — is more of a state of mind, really. Not too far from Madison, Wisconsin you could say.

A shortcut on the user’s desktop or Office Shortcut Bar usually helps folks get a fresh copy of the template without tears, too.

Now *THAT* is sexy!

[indent]

---

You just did thank us.

---

[/indent]

Nice going, Steve. I was going to suggest a batch of homemade brownies. Oh well…