PDA

View Full Version : Solved: Macro doesnt work with buttons



CloudenL
12-20-2011, 11:04 AM
I have a file that i inherited at work and i'm trying to understand what all the code does. there is a tab that will create charts, you are supposed to click on a chart area and hit a button for either line, pie or bar. the macro runs if im in the VB screen and hit run, but not if i am on the actual excel page. it automatically jumps to the "error" statement, even though i have clicked on the chart area. Im not sure what is wrong with this code and was hoping someone could help with what is missing.

I tried doing my own macro to change chart type to see what it looks like, and it gives me:

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.ChartType = xlPie

which is fine, but "chart 2" is the chart i selected, similar to below where it says ActiveSheet.ChartObjects("Chart 5").Activate. but i have 5 different charts, so im thinking having it say "Chart 5" is part of the problem, but i am not totally sure since i am still trying to learn.


Sub ChangeChartType()

ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlBarClustered
ActiveChart.ChartType = xlLineMarkers
ActiveChart.ChartType = xlColumnClustered
ActiveChart.ChartType = xlPie
End Sub


Sub LineChart()
On Error GoTo LineChartERR
ActiveChart.ChartType = xlLineMarkers
Exit Sub
LineChartERR:
vERR = MsgBox("SELECT A CHART BEFORE CLICKING HERE")

End Sub

Bob Phillips
12-20-2011, 11:41 AM
Remove that line and select the chart before running it.

CloudenL
12-20-2011, 11:56 AM
Remove what line?

i removed this: ActiveSheet.ChartObjects("Chart 5").Activate

but it still doesnt work

Jan Karel Pieterse
12-20-2011, 11:15 PM
If you remove the on error statement, what error do you get when you run the code from the button?

CloudenL
12-21-2011, 06:56 AM
the error i get is "SELECT A CHART BEFORE CLICKING HERE" even though i am clicking in a chart. i can click in a chart and manually run the macro and it works fine.

Aflatoon
12-21-2011, 08:11 AM
What kind of button is it that you are using? Forms or ActiveX? If the latter, have you set its TakeFocusOnClick property to False?

CloudenL
12-21-2011, 09:03 AM
I wish i knew exactly. here is a screen shot of what they look like. I think it is a a picture type. when i right click one of the options is change picture.

Jan Karel Pieterse
12-21-2011, 09:27 AM
Could you please re-read my last post and follow the instruction to remove the On error statement? It will force VBA to show you the real error.

CloudenL
12-21-2011, 09:38 AM
Run-time error "91": Object variable or with block variable not set

Bob Phillips
12-21-2011, 10:46 AM
Did you select the chart first?

Bob Phillips
12-21-2011, 10:51 AM
Assuming not, try


Sub LineChart()

If TypeName(Selection) = "ChartArea" Then

ActiveChart.ChartType = xlLineMarkers
Else

MsgBox "No chart selected"
End If
End Sub

CloudenL
12-21-2011, 10:53 AM
yes, in every instance. when i deleted out the On Error statement i click in the chart and hit to change and i got the run-time message.

Bob Phillips
12-21-2011, 11:15 AM
I knocked up a quick test and it worked fine here.

CloudenL
12-21-2011, 11:27 AM
using this code you put in earlier?


Sub LineChart()

If TypeName(Selection) = "ChartArea" Then

ActiveChart.ChartType = xlLineMarkers
Else

MsgBox "No chart selected"
End If
End Sub

CloudenL
12-21-2011, 12:03 PM
can i ask, what is the "xl" for in front of linemarkers? does any of this matter that this workbook was created in 2003 excel and i am working in 2007?

CloudenL
12-21-2011, 01:40 PM
Since this was done by someone outside my organization that i have no contact information for, would it be easier if i deleted the code he has for these charts and ask for your help to write new code that will create charts instead of changing the type?

I cant get it to work even with the help/suggestions. and to be honest, i'm not even sure if this has ever worked.

Bob Phillips
12-21-2011, 05:12 PM
First question: yes, that code:

Second question: xl is because it is an Excel coonstant

Third question: probably yes. Will you post the workbook?

CloudenL
12-22-2011, 06:50 AM
i can. it is for work and contains our product info. dont think anything in it is propriatary. so would i just post the passwords in the message with the attachment?