PDA

View Full Version : [SOLVED] One sub Button Two Macros



BENSON
01-10-2014, 08:43 PM
I have a sub button that when clicked adds a trend line to my graph. The code below adds the trend line and edits the text on the button to say "Remove trend line".I need help adding the 2nd code below to the button so when clicked again it removes the trend line and edits the text on the button back to "Add text Line"


Sub Button116_Click()
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLogarithmic, Forward _
:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Select
With Selection.Border
.ColorIndex = 5
.Weight = xlThick
.LineStyle = xlContinuous
ActiveSheet.Shapes("Button 116").TextFrame _
.Characters.Text = "REMOVE TREND LINE"
End With
End Sub

2nd piece of code

ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.SeriesCollection(1).Trendlines(1).Select
Selection.Delete
ActiveSheet.Shapes("Button 116").TextFrame _
.Characters.Text = "ADD TREND LINE"

westconn1
01-10-2014, 09:39 PM
try like
Sub Button116_Click()
If button116.caption = "ADD TREND LINE" then
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLogarithmic, Forward _
:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Select

With Selection.Border
.ColorIndex = 5
.Weight = xlThick
.LineStyle = xlContinuous
ActiveSheet.Shapes("Button 116").TextFrame _
.Characters.Text = "REMOVE TREND LINE"
End With

Else
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select
Selection.Delete
ActiveSheet.Shapes("Button 116").TextFrame _
.Characters.Text = "ADD TREND LINE"
End If
End Sub

BENSON
01-10-2014, 10:24 PM
THKS tried the code but getdebug and see the first line of your code highlighted yellow


Sub Button116_Click()
If button116.caption = "ADD TREND LINE" Then
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLogarithmic, Forward _
:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(1).Trendlines(1).Select

With Selection.Border
.ColorIndex = 5
.Weight = xlThick
.LineStyle = xlContinuous
ActiveSheet.Shapes("Button 116").TextFrame _
.Characters.Text = "REMOVE TREND LINE"
End With

Else
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select
Selection.Delete
ActiveSheet.Shapes("Button 116").TextFrame _
.Characters.Text = "ADD TREND LINE"
End If
End Sub

BENSON
01-11-2014, 12:28 AM
Run Time Error "424"
Object Required

westconn1
01-11-2014, 12:46 AM
i assumed (from the procedure name) that button116 is the name of your button, if not change to suit
the procecure should be located in the code pane for the worksheet containing the button, not a standard module
right click on the button and Veiw Code

if all else fails try

if activesheet.shapes("button116").oleformat.object.object.caption = "ADD TREND LINE" then

BENSON
01-11-2014, 01:16 AM
It is button 116,tried your suggested other code gave error message "item with specified name wasn't found " Thanks for trying

westconn1
01-11-2014, 01:36 AM
should be a space between button and 116