Unlink button from worksheet copied from another file

Hey guys.

I was trying to create a Gantt chart when I came across a fairly nice Gantt chart here in the forum. So I downloaded the file from Excel Forum's server and used it as a template. After a good amount of editing I have the chart working beautifully with the data from my own workbook. However, I was left with two problems:

1. Whenever I hit the "update chart" button in my file's Gant Chart worksheet, although it does update my chart, it opens the original file I downloaded to use as a template. If I delete the file, the button will not work, hence it will not update my chart.

2. In my workbook, I changed the worksheet name from "Gantt Chart" to "Active Projects" and the button stopped working. In my attempts to correct this problem, I went to VBA and replaced whatever "Gantt Chart" text I found with "Active Projects" but obviously it didn't work. I'm not that experienced in Excel. So I undid all changes in order t make it work as it does now, but I can't change the name of the worksheet.

What should I do?

I appreciate any help.

Attachment 403444

Hi protonLeah. Thanks for the reply.

I followed your advice but when I got to the "select the desired macro" stage I realized there wasn't such thing as a macro for that button. So I went back to the original file which I used as a template and then compared its VBA code with the one in the file I am editing. Guess what? I copied their code that was typed in something called "Module", created a "Module" back in my file's VBA code, pasted the copied code into it, followed your steps and assigned the macro to the button. Now it works beautifully. Not only that: I managed to change the worksheet's name too!

The code was:

Sub set_axis_options()

Dim cht As Chart
'change chart name here
Set cht = Sheets("Gantt Chart").ChartObjects("gantt_chart").Chart
    
    With cht.Axes(xlValue, xlPrimary)
        .MaximumScale = Range("ed_date").Value
        .MinimumScale = Range("st_date").Value
        .MajorUnit = 31
    End With
  
Sheets("Gantt Chart").Select
End Sub

And now it looks like this:

Sub set_axis_options()

Dim cht As Chart
'change chart name here
Set cht = Sheets("Active Projects").ChartObjects("gantt_chart").Chart
    
    With cht.Axes(xlValue, xlPrimary)
        .MaximumScale = Range("ed_date").Value
        .MinimumScale = Range("st_date").Value
        .MajorUnit = 10
    End With
  
Sheets("Active Projects").Select
End Sub

Thanks for the help mate!