Error 424 Object Required

Fabian61

New Member
Joined
Sep 19, 2023
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Try this:
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 , 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
 
Upvote 0
What type of button do you have and where is it located?

Also, where is the code located?
 
Upvote 0
Try this:
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 , 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
 
Upvote 0
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.
 
Upvote 0
VBA Code:
Sheets("Sheet1").Range("A1:B")
The above isn't a valid range, you probably want something like
VBA Code:
Sheets("Sheet1").Range("A1:B" & Sheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row)
 
Upvote 0
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
 
Upvote 0
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
 

Attachments

  • macro 2.PNG
    macro 2.PNG
    127.9 KB · Views: 24
Upvote 0
Maybe something like this.
VBA Code:
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
 
Upvote 0
Solution
Maybe something like this.
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,177
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top