I've been trying to set up a workbook where each sheet represents one day of the month and will display the number of staff unavailable to work that day as a percentage displayed on a pie chart. I've no problem with doing that but I would like to display the chart by clicking a control command button. The total number of staff are entered in A1 and the total number sick is entered into B1.
I created the chart on one sheet and recorded my actions using the macro recorder with the intention of assigning this macro to the button.
Sub pie6()
Range("A1:B1").Select
Charts.Add
ActiveChart.ChartType = xl3DPieExploded
ActiveChart.SetSourceData Source:=Sheets("Sheet5").Range("A1:B1"), PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet5"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = _
"% of productive staff not available due to sickness"
End With
End Sub
Two problems have arisen.
1/ I set the data labels to percentage but the macro recorder does not record this.
2/I need this to refer to the active sheet not Sheet5 and have been unable to adjust the code accordingly.
thanks for looking
Nick
I created the chart on one sheet and recorded my actions using the macro recorder with the intention of assigning this macro to the button.
Sub pie6()
Range("A1:B1").Select
Charts.Add
ActiveChart.ChartType = xl3DPieExploded
ActiveChart.SetSourceData Source:=Sheets("Sheet5").Range("A1:B1"), PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet5"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = _
"% of productive staff not available due to sickness"
End With
End Sub
Two problems have arisen.
1/ I set the data labels to percentage but the macro recorder does not record this.
2/I need this to refer to the active sheet not Sheet5 and have been unable to adjust the code accordingly.
thanks for looking
Nick
