OWC11 Spreadsheet component Undo Error

[mminasian]

Hey guys,

I'm working on a school project creating a fully self contained ERP system within Excel VBA.

I'm using OWC11 with the spreadsheet component. The code causing a problem is below:

[VBA]Private Sub Spreadsheet1_SheetCalculate(ByVal Sh As OWC11.Worksheet)
If exitflag <> 0 Then
exitflag = 0
Else
Dim e
exitflag = 0
If Spreadsheet1.Worksheets("IMF").Name = Sh.Name Then
For Each e In Spreadsheet1.Worksheets("IMF").Range("U4:U56")
If e.value < 0 Then
exitflag = exitflag + 1
MsgBox "On Hand must be greater than Allocated."
Spreadsheet1.EnableEvents = False
Spreadsheet1.Undo
Spreadsheet1.EnableEvents = True

Exit Sub
End If
Next e
End If

Dim f
If Spreadsheet1.Worksheets("IMF").Name = Sh.Name Then
For Each f In Spreadsheet1.Worksheets("IMF").Range("V4:V56")
If f.value < 0 Then
exitflag = exitflag + 1
MsgBox "On Hand must be greater than Safety Stock."
Spreadsheet1.EnableEvents = False
Spreadsheet1.Undo
Spreadsheet1.EnableEvents = True
Call IMF_Click
Exit Sub
End If
Next f
End If


If exitflag = 0 Then


If Spreadsheet1.Worksheets("IMF").Name = Sh.Name Or Spreadsheet1.Worksheets("MPS").Name = Sh.Name Then

'Past Due Handling
Dim eMsg As String
Dim Text As String
Dim Title As String
Dim response As String
Dim response2 As String
Dim NPV As String
Dim IRR As Double
Dim ROI As String

Dim response3 As String
Dim c As Object
Dim d As Object
Frame1.Visible = False
ListBox1.Clear

Set d = Spreadsheet1.Worksheets("Error").Range("O1:O58")

Title = "Past Due Report"
response2 = "Are you sure you want to undo your last change?" & Chr(13) & Chr(13) & "This action cannot be undone. This will UNDO your last action ONLY." & Chr(13) & Chr(13) & "If you would like to keep everything the same and make the changes yourself, Press No."
For Each c In d

If c.value <> "" Then
If eMsg = "" Then
eMsg = c.value
ListBox1.AddItem (c.value)
Else
eMsg = eMsg & ", " & c.value
ListBox1.AddItem (c.value)
End If
End If

Next c
Text = "The result of your most recent activity created a Past Due conflict in the following parts:" & Chr(13) & Chr(13) & eMsg & Chr(13) & Chr(13) & Chr(13) & Chr(13) & "Do you want to undo your most recent action?" & Chr(13) & Chr(13) & "Failure to fix these errors restricts access to the Analysis and Financials tabs!"

If Flag = True Then
If eMsg = "" Then
MultiPage1.Pages(3).Enabled = True
MultiPage1.Pages(4).Enabled = True
ListBox1.Clear
Frame1.Visible = False
Flag = False
Exit Sub
End If
End If

If eMsg = "" Then
MultiPage1.Pages(3).Enabled = True
MultiPage1.Pages(4).Enabled = True
ListBox1.Clear
Frame1.Visible = False


Else
MultiPage1.Pages(3).Enabled = False
MultiPage1.Pages(4).Enabled = False
response = MsgBox(Text, vbExclamation + vbYesNo, Title)

If response = vbYes Then
response3 = MsgBox(response2, vbYesNo + vbCritical, "Warning")
Else
Frame1.Visible = True
Flag = False
Exit Sub
End If

If response3 = vbYes Then
On Error GoTo Errorline
Flag = True
Spreadsheet1.EnableEvents = False
Spreadsheet1.Undo
Spreadsheet1.EnableEvents = True
If (Sh.Name = Spreadsheet1.Worksheets("IMF").Name) Then
Call IMF_Click
End If
If Sh.Name = Spreadsheet1.Worksheets("MPS").Name Then
Call MPS_Click
End If
Else
Frame1.Visible = True
Flag = False
End If

End If
Exit Sub
Errorline: MsgBox "There is nothing to undo."
End If
End If
End If
End Sub[/VBA]

Basically I have a couple of functions occurring. The first two big blocks check a couple of columns to do Data Validation (which custom validation isn't available to save in XML). The Last section checks a page for Past Due reports and spits them out.


My issue is this:
The first time I initiate a spreadsheet1.undo event everything works great. Subsequent times, ALSO work, as in they undo the last action. However, they create a horrible black screen over the spreadsheet component, or it won't display properly. It seems to be having a problem Redrawing or something. Again this does NOT happen the first time it undoes something.

Win 7 x64
Excel 2007
OWC11 Spreadsheet component

Screenshots of problem attached.

Thanks in advance. Unfortunately I have to demo this for my teacher in a matter of hours! Any help is very much appreciated. I'd hate to see 60 hours go down the drain because of a stupid bug.

[mdmackillop]

Looking at this bit
[VBA] If Flag = True Then
If eMsg = "" Then
MultiPage1.Pages(3).Enabled = True
MultiPage1.Pages(4).Enabled = True
ListBox1.Clear
Frame1.Visible = False
Flag = False
Exit Sub
End If
End If

If eMsg = "" Then[/VBA]

Where does Flag get its value? Would this be clearer in a If..Else statement. If eMsg = "" and Flag = True, the second test will not occur.

[mminasian]

Flag is a global variable that gets set when undo has occurred. The point of that logic is to prevent spreadsheet calculate from refiring after an undo command. However, that may not be an issue anymore given the enableevents lines.

That is definitely better logic

I tried separating those three functions out to see if that would help or isolate the problem but it is still occuring. Is there anyone with specific OWC knowledge that might have seen a screen like this before?

[mminasian]

Alright, so I removed all of the code from the sheet calculate event and ran it off of a button and the evil black screen goes away.

Is there any other way I can automate a button press through a different element?

[mdmackillop]

Check your other IFs
The first bit reduces to
[VBA] If exitflag <> 0 Then
exitflag = 0
Else
Dim e
exitflag = 0
If Spreadsheet1.Worksheets("IMF").Name = Sh.Name Then
'do stuff
End If

Dim f
If Spreadsheet1.Worksheets("IMF").Name = Sh.Name Then
'do stuff
End If

If exitflag = 0 Then
[/VBA]

There seem to be redundant/repeating statements which overcomplicate the code.

[mdmackillop]

Just seen your previous post.
I'm not familiar with your program, sop can't really help with events. Did you try ScreenUpdate = False while your code is running?

[mminasian]

Incidentally the screenupdating = false was part of the problem! Due to lack of time, I just connected everything to a button, and it works well enough I suppose. Thanks for the ideas!