mminasian
03-17-2011, 03:09 PM
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:
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
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.
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:
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
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.