Oh no! Run Time error 1004 Method 'Range' of object '_Global' failed

[surfinsunshinekat]

Hi everyone - I'm very new with VBA and am having a problem trying to debug a piece of code that is regularly used in my office and was previously written by someone else. I am new at this job and they've turned to me to fix this macro! I was able to fix a few previous problems within the macro (and felt very successful!) but now I am stuck! Please forgive me if I am not clear enough or do not use the right vocabulary. I am also unsure what constitutes a large or small file so hopefully this piece I will paste here isn't too big. I have also tried looking at many other threads and threads within other website but didn't find anything that could help me.

I attempt to run my code and I keep getting the "1004: Method of 'Range' of object '_Global' failed" error message. The Debug function will immediately go to the line "ActiveChart.SetSourceData Source:=Range("Charts!$E$1:$F$4")" I have tried several things to correct this error but I am without success. Does anyone have any ideas? Please let me know if you need to see all the code, in which case I will upload the file to a file sharing website.

Again, since I am very new with VBA, I apologize in advance if I haven't quite posted this piece of code correctly or if I am not using the correct terminology. Thank you!

Select Case ChartComboBox.Value
Case "Yes"
    Cells.Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("E1:F4").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Range("Charts!$E$1:$F$4")
    ActiveChart.ApplyLayout (10)
    ActiveChart.ChartTitle.Select
    ActiveChart.ClearToMatchStyle
    ActiveChart.ChartStyle = 25
    ActiveChart.ClearToMatchStyle
    ActiveChart.ChartTitle.Text = "CHIHUAHUA SUSPENSION"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "CHIHUAHUA SUSPENSION"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 20).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 20).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(0, 0, 0)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 18
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With

 

[SirJB7]

Hi, surfinsunshinekat!

As a new user you may want (I'd say should and must) go thru this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

Regarding your question, give a look at this file:
https://dl.dropboxusercontent.com/u...d (for surfinsunshinekat at chandoo.org).xlsm

Your posted snippet works if:
a) There's a worksheet named Charts in your workbook.
b) The code is within that worksheet class module (i.e., not in a standard module)
c) There's a combo box named ChartComboBox in that worksheet

Just advise if any issue.

Regards!

 

[surfinsunshinekat]

Hi, surfinsunshinekat!

As a new user you may want (I'd say should and must) go thru this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

Regarding your question, give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Oh no! Run Time error 1004 Method 'Range' of object '_Global' failed (for surfinsunshinekat at chandoo.org).xlsm

Your posted snippet works if:
a) There's a worksheet named Charts in your workbook.
b) The code is within that worksheet class module (i.e., not in a standard module)
c) There's a combo box named ChartComboBox in that worksheet

Just advise if any issue.

Regards!

Hi! Thank you, thank you. I did try to read through all the new folks stuff before posting. I'll try what you have recommended.

:D

 

[SirJB7]

Hi, surfinsunshinekat!
Please do it, and if your issue refuses to vanish in the air, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!