Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Columnwidth | Excel Discussion (Misc queries) | |||
ColumnWidth | Excel Programming | |||
ColumnWidth | Excel Programming | |||
Columnwidth changes-Event | Excel Programming | |||
ColumnWidth not working... | Excel Programming |