Delboy
asked on
Corrupt Excel Workbook
I have a workbook containing 2 userforms and 2 modules of VBA code (Excel97). It now crashes Excel when I go into the Visual Basic Editor and I can't get to my code.. but it does run the forms and the code via an AutoOpen macro.. I have tried Excel Recovery version 1 but it only recovers the AutoOpen macro....
Help... Delboy
Help... Delboy
Try holding down the shift key when you open the workbook. This should prevent the macro from running. That way you ahould be able to get into the workbook and fix the problem.
ASKER
This only stops the AutoOpen macro, but still cannot get into the visual basic editor without crashing once in the workbook...
I see. I misunderstood your problem. I will give it some more thought.
ASKER
I look forward to it.. D.
Delboy,
If you will send the file to me, I'll take a look at it and see if I can get to your code. You can send the file to shalbe@san.rr.com
Steve
If you will send the file to me, I'll take a look at it and see if I can get to your code. You can send the file to shalbe@san.rr.com
Steve
Delboy,
What happens when it crashes?
What happens when it crashes?
ASKER
It crashes Excel altogether...
Hi,
Could you send the file to me as well please. I think I can get at the code only using a handle.
frazer@flying-worm.demon.c o.uk
regards
Frazer
Could you send the file to me as well please. I think I can get at the code only using a handle.
frazer@flying-worm.demon.c
regards
Frazer
Have you tried copying the workbook to a new file to see if the problem persists? What if you try getting at the code through the Tools-Macro-Macros menu option? Do you get the same behavior? Can you delete macros from this spot? Can you access the code editor in other workbooks?
ASKER
Everything you have just said, I have done to no avail...nice try though awd... copying to a new workbook brings the problem with it, going through the Tools etc option has the same results, cannot delete macros .. and can go into the editor in other workbooks..
D
D
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
alternativly,
here is your code....
'Module1
Sub Auto_Open()
ScreenUpdating = False
UserForm2.Show
End Sub
'UserForm1
Private Sub CommandButton1_Click()
If UserForm1.TextBox2.Value = """" Then
MsgBox ""Please Fill In The Name Field"", vbExclamation, ""Name""
UserForm1.TextBox2.SetFocu s
Exit Sub
End If
If UserForm1.TextBox4.Value = """" Then
MsgBox ""Please Fill In The Project Field"", vbExclamation, ""Project""
UserForm1.TextBox4.SetFocu s
Exit Sub
End If
If UserForm1.TextBox1.Value = """" Then
MsgBox ""Please Fill In The Date Field"", vbExclamation, ""Date""
UserForm1.TextBox1.SetFocu s
Exit Sub
End If
If UserForm1.TextBox8.Value = """" Then
MsgBox ""Please Fill In The Start Date Field"", vbExclamation, ""Start Date""
UserForm1.TextBox8.SetFocu s
Exit Sub
End If
If Label25.Visible = True Then
MsgBox ""Please Enter A Valid Start Date In The Start Date Field"", vbExclamation, ""Start Date""
UserForm1.TextBox8.SetFocu s
Exit Sub
End If
If UserForm1.TextBox5.Value = """" Then
MsgBox ""Please Fill In The Team Leader Field"", vbExclamation, ""Team Leader""
UserForm1.TextBox5.SetFocu s
Exit Sub
End If
If UserForm1.TextBox6.Value = """" Then
MsgBox ""Please Fill In The Supervisor Field"", vbExclamation, ""Supervisor""
UserForm1.TextBox6.SetFocu s
Exit Sub
End If
If UserForm1.TextBox7.Value = """" Then
MsgBox ""Please Fill In The Manager Field"", vbExclamation, ""Manager""
UserForm1.TextBox7.SetFocu s
Exit Sub
End If
Dim Y As Integer
Sheets(""Datasheet"").Rang e(""A2""). Value = UserForm1.TextBox2.Text
Sheets(""Datasheet"").Rang e(""B2""). Value = UserForm1.TextBox4.Text
Sheets(""Datasheet"").Rang e(""C2""). Value = UserForm1.TextBox1.Text
Sheets(""Datasheet"").Rang e(""D2""). Value = CDate(UserForm1.TextBox8.T ext)
If UserForm1.OptionButton1.Va lue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""E2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton2.Va lue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""E2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton3.Va lue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""E2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton4.Va lue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""E2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton5.Va lue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""F2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton6.Va lue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""F2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton7.Va lue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""F2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton8.Va lue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""F2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton9.Va lue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""G2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton10.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""G2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton11.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""G2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton12.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""G2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton13.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""H2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton14.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""H2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton15.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""H2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton16.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""H2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton17.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""I2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton18.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""I2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton19.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""I2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton20.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""I2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton21.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""J2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton22.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""J2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton23.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""J2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton24.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""J2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton25.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""K2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton26.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""K2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton27.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""K2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton28.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""K2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton29.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""L2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton30.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""L2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton31.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""L2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton32.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""L2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton33.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""M2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton34.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""M2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton35.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""M2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton36.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""M2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton37.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""N2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton38.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""N2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton39.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""N2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton40.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""N2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton41.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""O2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton42.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""O2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton43.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""O2""). Value = UserForm1.OptionButton1.Ca ption
End If
If UserForm1.OptionButton44.V alue = True Then
Y = Y + 1
Sheets(""Datasheet"").Rang e(""O2""). Value = UserForm1.OptionButton1.Ca ption
End If
If Y <> 11 Then
MsgBox ""You haven't answered all questions, please try again"", vbExclamation, ""Answer All Questions""
End If
Sheets(""Datasheet"").Rang e(""P2""). Value = UserForm1.TextBox5.Text
Sheets(""Datasheet"").Rang e(""Q2""). Value = UserForm1.TextBox6.Text
Sheets(""Datasheet"").Rang e(""R2""). Value = UserForm1.TextBox7.Text
OpenFeedData
Workbooks(""feed.xls"").Ac tivate
Rows(""2:2"").Select
Selection.Copy
Windows(""FEEDDATA.XLS""). Activate
Range(""A1"").Select
If IsEmpty(Range(""A2"")) Then
Selection.Offset(1, 0).Select
Else
Selection.End(xlDown).Sele ct
Selection.Offset(1, 0).Select
End If
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range(""A1"").Select
Workbooks(""Feeddata.xls"" ).Close savechanges = True
End Sub
Private Sub CommandButton2_Click()
Workbooks(""Feed.xls"").Cl ose savechanges = False
End Sub
Private Sub TextBox1_Change()
End Sub
Private Sub TextBox8_Change()
If IsDate(TextBox8.Value) Then
Label25.Visible = False
Else
Label25.Visible = True
TextBox8.SetFocus
End If
End Sub
Private Sub UserForm_Activate()
TextBox1.Value = Format(Date, ""short date"")
TextBox8.Value = Format(Date + 14, ""short date"")
End Sub
Function OpenFeedData()
On Error GoTo ErrorHandler
For X = 1 To 10000
Workbooks.Open (""i:\users\jeed\data\xls\ FeedData.x ls"")
X = 10000
Next
Exit Function
ErrorHandler:
Select Case Err.Number
Case 55
Resume
Case Else
Debug.Print Error(ErrorNumber)
End Select
End Function
'UserForm2
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
Hope this helps
Frazer
alternativly,
here is your code....
'Module1
Sub Auto_Open()
ScreenUpdating = False
UserForm2.Show
End Sub
'UserForm1
Private Sub CommandButton1_Click()
If UserForm1.TextBox2.Value = """" Then
MsgBox ""Please Fill In The Name Field"", vbExclamation, ""Name""
UserForm1.TextBox2.SetFocu
Exit Sub
End If
If UserForm1.TextBox4.Value = """" Then
MsgBox ""Please Fill In The Project Field"", vbExclamation, ""Project""
UserForm1.TextBox4.SetFocu
Exit Sub
End If
If UserForm1.TextBox1.Value = """" Then
MsgBox ""Please Fill In The Date Field"", vbExclamation, ""Date""
UserForm1.TextBox1.SetFocu
Exit Sub
End If
If UserForm1.TextBox8.Value = """" Then
MsgBox ""Please Fill In The Start Date Field"", vbExclamation, ""Start Date""
UserForm1.TextBox8.SetFocu
Exit Sub
End If
If Label25.Visible = True Then
MsgBox ""Please Enter A Valid Start Date In The Start Date Field"", vbExclamation, ""Start Date""
UserForm1.TextBox8.SetFocu
Exit Sub
End If
If UserForm1.TextBox5.Value = """" Then
MsgBox ""Please Fill In The Team Leader Field"", vbExclamation, ""Team Leader""
UserForm1.TextBox5.SetFocu
Exit Sub
End If
If UserForm1.TextBox6.Value = """" Then
MsgBox ""Please Fill In The Supervisor Field"", vbExclamation, ""Supervisor""
UserForm1.TextBox6.SetFocu
Exit Sub
End If
If UserForm1.TextBox7.Value = """" Then
MsgBox ""Please Fill In The Manager Field"", vbExclamation, ""Manager""
UserForm1.TextBox7.SetFocu
Exit Sub
End If
Dim Y As Integer
Sheets(""Datasheet"").Rang
Sheets(""Datasheet"").Rang
Sheets(""Datasheet"").Rang
Sheets(""Datasheet"").Rang
If UserForm1.OptionButton1.Va
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton2.Va
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton3.Va
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton4.Va
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton5.Va
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton6.Va
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton7.Va
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton8.Va
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton9.Va
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton10.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton11.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton12.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton13.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton14.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton15.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton16.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton17.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton18.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton19.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton20.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton21.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton22.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton23.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton24.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton25.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton26.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton27.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton28.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton29.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton30.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton31.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton32.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton33.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton34.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton35.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton36.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton37.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton38.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton39.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton40.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton41.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton42.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton43.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If UserForm1.OptionButton44.V
Y = Y + 1
Sheets(""Datasheet"").Rang
End If
If Y <> 11 Then
MsgBox ""You haven't answered all questions, please try again"", vbExclamation, ""Answer All Questions""
End If
Sheets(""Datasheet"").Rang
Sheets(""Datasheet"").Rang
Sheets(""Datasheet"").Rang
OpenFeedData
Workbooks(""feed.xls"").Ac
Rows(""2:2"").Select
Selection.Copy
Windows(""FEEDDATA.XLS"").
Range(""A1"").Select
If IsEmpty(Range(""A2"")) Then
Selection.Offset(1, 0).Select
Else
Selection.End(xlDown).Sele
Selection.Offset(1, 0).Select
End If
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range(""A1"").Select
Workbooks(""Feeddata.xls""
End Sub
Private Sub CommandButton2_Click()
Workbooks(""Feed.xls"").Cl
End Sub
Private Sub TextBox1_Change()
End Sub
Private Sub TextBox8_Change()
If IsDate(TextBox8.Value) Then
Label25.Visible = False
Else
Label25.Visible = True
TextBox8.SetFocus
End If
End Sub
Private Sub UserForm_Activate()
TextBox1.Value = Format(Date, ""short date"")
TextBox8.Value = Format(Date + 14, ""short date"")
End Sub
Function OpenFeedData()
On Error GoTo ErrorHandler
For X = 1 To 10000
Workbooks.Open (""i:\users\jeed\data\xls\
X = 10000
Next
Exit Function
ErrorHandler:
Select Case Err.Number
Case 55
Resume
Case Else
Debug.Print Error(ErrorNumber)
End Select
End Function
'UserForm2
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
Hope this helps
Frazer
ASKER
Thanks Steve, it works fine now.. I see the error of my ways..
Derek.
Derek.