Manipulating chartobject while workbook is shared.
 

I have a list of data on a sheet. The first column lists a date
representing the week. The next seven columns are the days of the week.


Weekly, a new row of data is added to the list.

I have a combobox where you select a day of the week and then the chart
is shown using the data from that particular weekday series. It works
fine either if the book is not shared, or if only one user is utilizing
the shared book. Once you have two or more in the book, you get a
run-time error such as Activate method of Chartobject class failed. My
code is below.

I know there are limitations when a book is shared. According to help
one of those limitations is changing charts - is this my problem. If
that's the case why does it work properly if only one person is using
the shared book. Is there a work around?

Any assistance would be appreciated.

Shelby


Private Sub ComboBox1_Change()

Select Case ComboBox1.Value
Case "Monday"
Col = 2
WDate = "Monday"
Case "Tuesday"
Col = 3
WDate = "Tuesday"
Case "Wednesday"
Col = 4
WDate = "Wednesday"
Case "Thursday"
Col = 5
WDate = "Thursday"
Case "Friday"
Col = 6
WDate = "Friday"
Case "Saturday"
Col = 7
WDate = "Saturday"
Case "Sunday"
Col = 8
WDate = "Sunday"

Case Else
Exit Sub
End Select


Range("BegWE").Select
Selection.End(xlDown).Select
endrange = ActiveCell.Row
dendrange = "='Day Summary'!R7C" & Col & ":R" & endrange & "C" &
Col
xendrange = "='Day Summary'!R7C1:R" & endrange & "C1"
ActiveSheet.ChartObjects(DailyChartName).Activate
ActiveChart.ShowWindow = True
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = dendrange
ActiveChart.SeriesCollection(1).XValues = xendrange
ActiveChart.SeriesCollection(1).Name = "='Day Summary'!R6C19"
With ActiveChart.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Text = WDate
End With
ComboBox1.ListIndex = 0

End Sub

Manipulating chartobject while workbook is shared.
 

You can set the Interactive property to false. This will prevent other users
from doing anything while your code is running.

"Shelby Haynes" wrote:

I have a list of data on a sheet. The first column lists a date
representing the week. The next seven columns are the days of the week.


Weekly, a new row of data is added to the list.

I have a combobox where you select a day of the week and then the chart
is shown using the data from that particular weekday series. It works
fine either if the book is not shared, or if only one user is utilizing
the shared book. Once you have two or more in the book, you get a
run-time error such as Activate method of Chartobject class failed. My
code is below.

I know there are limitations when a book is shared. According to help
one of those limitations is changing charts - is this my problem. If
that's the case why does it work properly if only one person is using
the shared book. Is there a work around?

Any assistance would be appreciated.

Shelby


Private Sub ComboBox1_Change()

Select Case ComboBox1.Value
Case "Monday"
Col = 2
WDate = "Monday"
Case "Tuesday"
Col = 3
WDate = "Tuesday"
Case "Wednesday"
Col = 4
WDate = "Wednesday"
Case "Thursday"
Col = 5
WDate = "Thursday"
Case "Friday"
Col = 6
WDate = "Friday"
Case "Saturday"
Col = 7
WDate = "Saturday"
Case "Sunday"
Col = 8
WDate = "Sunday"

Case Else
Exit Sub
End Select


Range("BegWE").Select
Selection.End(xlDown).Select
endrange = ActiveCell.Row
dendrange = "='Day Summary'!R7C" & Col & ":R" & endrange & "C" &
Col
xendrange = "='Day Summary'!R7C1:R" & endrange & "C1"
ActiveSheet.ChartObjects(DailyChartName).Activate
ActiveChart.ShowWindow = True
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = dendrange
ActiveChart.SeriesCollection(1).XValues = xendrange
ActiveChart.SeriesCollection(1).Name = "='Day Summary'!R6C19"
With ActiveChart.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Text = WDate
End With
ComboBox1.ListIndex = 0

End Sub