Error 424 Object Required

[Fabian61]

Found this code on the net to essentially add a graph when a button is clicked but delete the graph if the button is deselected. I am quite new to vba but I am not understanding why there is an error 424 for the highlighted code and how to resolve it or if this solution can be completed in a better way. How would one add the object required for GoogleBtn?

VBA Code
Private Sub GoogleBtn_Click()

Dim mySheet As Worksheet
Dim shp As Shape
Dim myChart As Chart


Set mySheet = ActiveWorkbook.Worksheets("Sheet1")

On Error Resume Next
Set shp = mySheet.Shapes("GoogleChart")
On Error GoTo 0

If shp Is Nothing Then
Set shp = mySheet.Shapes.AddChart(XlChartType:=xlColumnClustered, _
Left:=GoogleBtn.Left + GoogleBtn.Width + 2, Top:=GoogleBtn.Top, Height:=100, Width:=150)
End If

Set myChart = shp.Chart

If GoogleBtn.Value = True Then

myChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B")
myChart.ChartType = xlLine
shp.Name = "GoogleChart"

Else

shp.Delete

End If

End Sub

 

[Skyybot]

Try this:

Private Sub GoogleBtn_Click()

Dim mySheet As Worksheet
Dim shp As Shape
Dim myChart As Chart


Set mySheet = ActiveWorkbook.Worksheets("Sheet1")

On Error Resume Next
Set shp = mySheet.Shapes("GoogleChart")
On Error GoTo 0

If shp Is Nothing Then
Set shp = mySheet.Shapes.AddChart(XlChartType:=xlColumnClustered, _
Left:=GoogleBtn.Left , Width:= GoogleBtn.Width + 2, Top:=GoogleBtn.Top, Height:=100, Width:=150)
End If

Set myChart = shp.Chart

If GoogleBtn.Value = True Then

myChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B")
myChart.ChartType = xlLine
shp.Name = "GoogleChart"

Else

shp.Delete

End If

End Sub

 

[Norie]

What type of button do you have and where is it located?

Also, where is the code located?

 

[Fabian61]

Try this:

Private Sub GoogleBtn_Click()

Dim mySheet As Worksheet
Dim shp As Shape
Dim myChart As Chart


Set mySheet = ActiveWorkbook.Worksheets("Sheet1")

On Error Resume Next
Set shp = mySheet.Shapes("GoogleChart")
On Error GoTo 0

If shp Is Nothing Then
Set shp = mySheet.Shapes.AddChart(XlChartType:=xlColumnClustered, _
Left:=GoogleBtn.Left , Width:= GoogleBtn.Width + 2, Top:=GoogleBtn.Top, Height:=100, Width:=150)
End If

Set myChart = shp.Chart

If GoogleBtn.Value = True Then

myChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B")
myChart.ChartType = xlLine
shp.Name = "GoogleChart"

Else

shp.Delete

End If

End Sub

Still getting the same error using this code

 

[BigBeachBananas]

You should ensure that you have a worksheet named "Sheet1" in the workbook, and a button named "GoogleBtn" on that worksheet for this code to work as expected.
The chart's source data range (Sheets("Sheet1").Range("A1:B")) might need to be modified to match your actual data range.

 

[MARK858]

Sheets("Sheet1").Range("A1:B")

The above isn't a valid range, you probably want something like

Sheets("Sheet1").Range("A1:B" & Sheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row)

 

[Fabian61]

What type of button do you have and where is it located?

Also, where is the code located?

Form control button located on the same sheet as the data. the code is located in the module

 

[Fabian61]

You should ensure that you have a worksheet named "Sheet1" in the workbook, and a button named "GoogleBtn" on that worksheet for this code to work as expected.
The chart's source data range (Sheets("Sheet1").Range("A1:B")) might need to be modified to match your actual data range.

Here is an image of how everything looks

 

[rlv01]

Maybe something like this.

Sub GoogleBtn_Click()
    Dim mySheet As Worksheet
    Dim shp As Shape
    Dim myChart As Chart
    Dim myChartDataRange As Range
    Dim GoogleBtn As Button

    Set mySheet = ActiveWorkbook.Worksheets("Sheet1")

    On Error Resume Next
    Set GoogleBtn = mySheet.Buttons("GoogleBtn")
    Set shp = mySheet.Shapes("GoogleChart")
    On Error GoTo 0

    If GoogleBtn Is Nothing Then
        MsgBox "Error -  Cannot find a Button named 'GoogleBtn'"
        Exit Sub
    End If

    If shp Is Nothing Then
        'Set Chart data range
        With ActiveSheet
            Set myChartDataRange = .Range("A1:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
        End With

        'Add chart
        Set shp = mySheet.Shapes.AddChart(XlChartType:=xlLine, Left:=GoogleBtn.Left + GoogleBtn.Width + 2, Top:=GoogleBtn.Top, Height:=100, Width:=150)
        shp.Chart.SetSourceData Source:=myChartDataRange
        shp.Name = "GoogleChart"
        Set myChart = shp.Chart
        GoogleBtn.Caption = "Delete Chart"
    Else
        'Delete Chart
        shp.Delete
        GoogleBtn.Caption = "Add Chart"
    End If
End Sub

 

[Fabian61]

Maybe something like this.

Sub GoogleBtn_Click()
    Dim mySheet As Worksheet
    Dim shp As Shape
    Dim myChart As Chart
    Dim myChartDataRange As Range
    Dim GoogleBtn As Button

    Set mySheet = ActiveWorkbook.Worksheets("Sheet1")

    On Error Resume Next
    Set GoogleBtn = mySheet.Buttons("GoogleBtn")
    Set shp = mySheet.Shapes("GoogleChart")
    On Error GoTo 0

    If GoogleBtn Is Nothing Then
        MsgBox "Error -  Cannot find a Button named 'GoogleBtn'"
        Exit Sub
    End If

    If shp Is Nothing Then
        'Set Chart data range
        With ActiveSheet
            Set myChartDataRange = .Range("A1:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
        End With

        'Add chart
        Set shp = mySheet.Shapes.AddChart(XlChartType:=xlLine, Left:=GoogleBtn.Left + GoogleBtn.Width + 2, Top:=GoogleBtn.Top, Height:=100, Width:=150)
        shp.Chart.SetSourceData Source:=myChartDataRange
        shp.Name = "GoogleChart"
        Set myChart = shp.Chart
        GoogleBtn.Caption = "Delete Chart"
    Else
        'Delete Chart
        shp.Delete
        GoogleBtn.Caption = "Add Chart"
    End If
End Sub

Thanks Bud much appreciated this works perfectly