MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Robb- Almost working


Posted by A.R. on August 24, 2001 9:19 AM

Regarding to message at http://www.mrexcel.com/wwwboard/messages/28447.html, I saved it as Microsoft Excel Workbook, close the file and re-opened and it works. But after I enter some data I would get a message saying “This program has performed an illegal operation and will be shut down.” When I delete the code I don’t get this message. I also saved the file as “Microsoft Excel 97 & 5.0/95 Workbook” and I get the same error message. I’m using Excel 97 on Windows 95. The Excel program is located on a network drive and not on my computer.


Posted by Robb on August 25, 2001 5:55 AM

A difficult one to answer as so many issues can cause the crash. When you gat the message, check the details to find out exactly what is the issue.

The code certainly does not crash my program, but mine is standalone. Does it crash immediately when you reopen it, or does it take some time? Also, you say "enter some data" - what sort of data?

Regards

Posted by Robb on August 25, 2001 9:52 PM

I played around with the code and was able to get it to crash Excel. It seems the problem is in
the OLEAUT32.DLL - presumably a DLL to do with OLE automation.

I don't know why it does it, but I amended the code and have not been able to get it to crash.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count > 1 = True Then Exit Sub
With Worksheets("Sheet1")
If Not Application.Intersect(Target, Columns(1)) Is Nothing Then
Target = Format(Target, ">")
ElseIf Not Application.Intersect(Target, Columns(3)) Is Nothing Then
Target = Format(Target, ">")
ElseIf Not Application.Intersect(Target, Columns(5)) Is Nothing Then
Target = Format(Target, ">")
Else
End If
End With
End Sub

You will need to amend the Worksheet name and columns numbers to suit.

This code will not do anything if a user copies to a range of cells - input
must be by single cell.

Any help?

Regards