T
T_o_n_y
My "ClearWorksheets" subroutine shown below gives the following error
error is 1004: Method 'Columns' of object '_Global'failed
on the following line of code:
Columns.ColumnWidth = 6.5
But, the error only occurs
a) on one particular sheet (the 3rd one shown called "IncludedAngleChange")
and
b) even then, the error only occurs the first time through the routine
The routine that calls 'ClearWorksheets' is also shown below, and it has an
error trap that allows the user to try again. When you try again the error
doesn't occur. I tried deleting that sheet and creating an identical new
one. The same thing keeps happening.
I'm perplexed at intermittant errors like this. Can someone shed light on
it for me?
-Tony
Sub ClearWorksheets()
Dim ws As Worksheet, ch As Chart
'check to see if ANG.XLS is open and close it if it is
CloseANGXLS
'clear the sheets of any previous data or charts
For Each ws In Worksheets
Select Case ws.Name
Case "DataAngles", "IncludedAngles", "IncludedAngleChange"
ws.Activate
Range("B3:GS52").Clear
Columns.ColumnWidth = 6.5
ActiveSheet.ChartObjects.Delete
Case "ANG.PRN"
ws.Activate
Range("B3:AY202").Clear
End Select
Next
chartHeight = Range("Height_of_Charts")
chartWidth = Range("Width_of_Charts")
End Sub
Here's the routine that calls the above routine:
Private Sub importANGXLSButton_Click()
Dim i As Integer, msg As String
Dim UserWantsToOpen As String
Dim OpenDialog As fOpenANGXLS
Dim response As Integer
UserWantsToOpen = "Yes"
OriginalEventStatus = Application.EnableEvents
Application.EnableEvents = False
On Error GoTo ErrHandler
While UserWantsToOpen = "Yes"
Worksheets("Preferences").Activate
If Range("File_Path") = "" Then
Application.DefaultFilePath = "G:\"
Else
Application.DefaultFilePath = Range("File_Path")
End If
Set OpenDialog = New fOpenANGXLS
With OpenDialog
.ANGXLSpath = Range("File_Path") & ANGXLS
.Show
If .ANGXLSpath = "Finished" Then Exit Sub 'user exited early
End With
DisplayWaitMessage _
"The new angle data is being imported"
GetDataIntoTablesAndCharts
Unload waitDialog
Worksheets("IncludedAngles").Activate
Range("A1").Select
Application.EnableEvents = OriginalEventStatus
Exit Sub
ErrHandler:
MsgBox "error is " & Err.Number & ": " & Err.Description
Err.Clear
response = MsgBox("There was an error. Do you want to try again?",
vbQuestion + vbYesNo)
UserWantsToOpen = IIf(response = vbYes, "Yes", "No")
Wend
End Sub
error is 1004: Method 'Columns' of object '_Global'failed
on the following line of code:
Columns.ColumnWidth = 6.5
But, the error only occurs
a) on one particular sheet (the 3rd one shown called "IncludedAngleChange")
and
b) even then, the error only occurs the first time through the routine
The routine that calls 'ClearWorksheets' is also shown below, and it has an
error trap that allows the user to try again. When you try again the error
doesn't occur. I tried deleting that sheet and creating an identical new
one. The same thing keeps happening.
I'm perplexed at intermittant errors like this. Can someone shed light on
it for me?
-Tony
Sub ClearWorksheets()
Dim ws As Worksheet, ch As Chart
'check to see if ANG.XLS is open and close it if it is
CloseANGXLS
'clear the sheets of any previous data or charts
For Each ws In Worksheets
Select Case ws.Name
Case "DataAngles", "IncludedAngles", "IncludedAngleChange"
ws.Activate
Range("B3:GS52").Clear
Columns.ColumnWidth = 6.5
ActiveSheet.ChartObjects.Delete
Case "ANG.PRN"
ws.Activate
Range("B3:AY202").Clear
End Select
Next
chartHeight = Range("Height_of_Charts")
chartWidth = Range("Width_of_Charts")
End Sub
Here's the routine that calls the above routine:
Private Sub importANGXLSButton_Click()
Dim i As Integer, msg As String
Dim UserWantsToOpen As String
Dim OpenDialog As fOpenANGXLS
Dim response As Integer
UserWantsToOpen = "Yes"
OriginalEventStatus = Application.EnableEvents
Application.EnableEvents = False
On Error GoTo ErrHandler
While UserWantsToOpen = "Yes"
Worksheets("Preferences").Activate
If Range("File_Path") = "" Then
Application.DefaultFilePath = "G:\"
Else
Application.DefaultFilePath = Range("File_Path")
End If
Set OpenDialog = New fOpenANGXLS
With OpenDialog
.ANGXLSpath = Range("File_Path") & ANGXLS
.Show
If .ANGXLSpath = "Finished" Then Exit Sub 'user exited early
End With
DisplayWaitMessage _
"The new angle data is being imported"
GetDataIntoTablesAndCharts
Unload waitDialog
Worksheets("IncludedAngles").Activate
Range("A1").Select
Application.EnableEvents = OriginalEventStatus
Exit Sub
ErrHandler:
MsgBox "error is " & Err.Number & ": " & Err.Description
Err.Clear
response = MsgBox("There was an error. Do you want to try again?",
vbQuestion + vbYesNo)
UserWantsToOpen = IIf(response = vbYes, "Yes", "No")
Wend
End Sub