Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 43
Default Columns.ColumnWidth error...sometimes

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Columnwidth Jean-Paul Excel Discussion (Misc queries) 1 November 24th 09 02:21 PM
ColumnWidth abhimanyu Excel Programming 1 November 10th 06 01:14 PM
ColumnWidth Dwight[_4_] Excel Programming 2 July 14th 04 08:22 PM
Columnwidth changes-Event Tom Excel Programming 3 February 16th 04 03:36 PM
ColumnWidth not working... BEE Excel Programming 2 December 19th 03 06:29 PM


All times are GMT +1. The time now is 08:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"