PDA

View Full Version : Error: 1004



yurble_vn
03-06-2009, 11:58 AM
Dear All,

I have built this file for time ago, but now when I use back, it always error.
I have look thru it, but can not found the bug.

Could someone help to take a look thru. (Sub: Chartstandize)

Thanks in advance,

yurble

Bob Phillips
03-06-2009, 12:27 PM
Sub ChartStandardize() ' Chart Stanardize
Dim wks As Worksheet
Dim iCht As Integer
Dim Shp As Shape
Dim CharH As Double
Dim CharW As Double
Dim iResponce As Integer
Dim temp As Boolean
Dim errstr As String
On Error GoTo Error_Handler

iResponce = MsgBox("You are about to modify all charts" & Chr(10) & "Have you backed up your files?" & Chr(10) & "Please back up!!!", vbYesNo, "Bao Lan Dieu's Macro")
If iResponce = vbYes Then

For Each wks In ActiveWorkbook.Windows(1).SelectedSheets
For iCht = 1 To wks.ChartObjects.Count
'Set chart size
wks.ChartObjects(iCht).Activate
With wks.ChartObjects(iCht)
.Width = 383 ' Chart width
.Height = 235 'Chart Height
End With
'Get actual Chart Size
CharH = wks.ChartObjects(iCht).Height
CharW = wks.ChartObjects(iCht).Width
'MsgBox Str(CharH) & " " & CharW
'Change Plot Area
ActiveChart.PlotArea.Select
With Selection
.Top = 12
.Left = 5
'Change Size
.Width = 363
.Height = 203

End With
'Change Chart Title placement & Font
If ActiveChart.HasTitle Then

With ActiveChart.ChartTitle

With .Font

.Top = 0
.Left = 15
End With

With .Font

.Name = "Arial"
.Size = TitleFontSize 'font size
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15 'gray color, 0 represents for Rose
'at the bottom-left of the color pallet,
'so White will be represented by 7
.Background = xlAutomatic
End With
End With
End If

'Change Legend Style
ActiveChart.Legend.Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = LegendFontSize
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.Position = xlBottom
Next
Next
End If

GoTo EndCode
Error_Handler:
Select Case Err.Number
Case 13
errstr = "Input wrong data type, command cancled"
Case 1004
errstr = "Sheet protected.Unprotected first"
Case Else
errstr = "Uncertain error occured, contact author for instruction" & Chr(10) & "Error Number:" & Err.Number
End Select
temp = MsgBox(errstr, vbOKOnly, "Bao Lan Dieu's Macro")
EndCode:
End Sub

yurble_vn
03-07-2009, 08:40 AM
It always stopped at:

ActiveChart.PlotArea.Select

and the error code is: 1004 as this title

mdmackillop
03-07-2009, 09:09 AM
You have two unassigned variables
LegendFontSize and TitleFontSize
Dim these as Long and give them their values.

Always use Option Explicit to avoid these errors.

yurble_vn
03-07-2009, 10:11 AM
Thanks MDMackillop,

I have coded this 2 years ago. And now when comeback, all the basic rules' gone.