Run time error 1004 - unable to get the chartObjects property of the workshe

[hedgehog1]

I am having a problem with the code in my workbook. I have altered the
code to include some more areas that I have to chart. Will you look at
my code, and tell me what I have left out? I am fairly new to coding.
I get the error in the last section of the code.
(Worksheets("GBP").ChartObjects("theChart" & selArea).Visible = True)
is the error line.

Thanks for your help in advance!

Option Explicit

Private Sub Workbook_Open()
Application.WindowState = xlMaximized
setupToolbar
showChart "PO"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Msg As String
Dim Ans

If Not Me.Saved Then
Msg = "Do you want to save the changes you made to "
Msg = Msg & Me.Name & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Me.Save
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
On Error Resume Next
Application.CommandBars("SelectArea").Delete
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("SelectArea").Visible = False
End Sub

Private Sub Workbook_Activate()
On Error Resume Next
Application.CommandBars("SelectArea").Visible = True
End Sub

Public Function setupToolbar()
Dim cmdBarArea As CommandBar
Dim aBtn As CommandBarControl

'delete toolbar if already exists
On Error Resume Next
Application.CommandBars("SelectArea").Delete
On Error GoTo Err_setupToolbar

'create toolbar
Set cmdBarArea = Application.CommandBars.Add(Name:="Select Area",
Position:=msoBarFloating, MenuBar:=False, temporary:=True)

'populate the toolbar
With cmdBarArea.Controls
Set aBtn = .Add(Type:=msoControlDropdown, temporary:=True)
aBtn.BeginGroup = True
aBtn.Caption = "Select Area"
aBtn.Tag = aBtn.Caption
aBtn.TooltipText = "Select Area"
aBtn.OnAction = "ThisWorkbook.AreaUpdate"
aBtn.AddItem "Potomac"
aBtn.AddItem "BMET"
aBtn.AddItem "DC/MD West"
aBtn.AddItem "NORVA"
aBtn.AddItem "Patuxent"
aBtn.AddItem "SOVA"
aBtn.AddItem "WVA/WMD"
aBtn.ListIndex = 1
aBtn.Width = 100
End With

'final toolbar configuration
With cmdBarArea
'set the toolbar's protection
.Protection = msoBarNoCustomize + msoBarNoResize +
msoBarNoChangeVisible + msoBarNoChangeDock
.Left = 680
.Top = 120
.Position = msoBarFloating
.Visible = True
End With

Exit_setupToolbar:
Exit Function
Err_setupToolbar:
MsgBox Err.Description & "-" & Err.Number
Resume Exit_setupToolbar
End Function

Public Function AreaUpdate()
Dim AreaSelect As CommandBarControl

With Application.CommandBars("SelectArea")
Set AreaSelect = .Controls("AreaSelect")
End With

Select Case Trim(AreaSelect.Text)
Case "Potomac"
showChart "PO"
Case "BMET"
showChart "BE"
Case "DC/MD West"
showChart "DC"
Case "NORVA"
showChart "NO"
Case "Patuxent"
showChart "PA"
Case "SOVA"
showChart "SO"
Case "WVA/WMD"
showChart "WV"

Case Else
MsgBox "not working"
End Select
End Function

Public Function showChart(selArea As String)
Dim Area(1 To 7) As String
Dim countArea As Integer

Area(1) = "PO"
Area(2) = "BE"
Area(3) = "DC"
Area(4) = "NO"
Area(5) = "PA"
Area(6) = "SO"
Area(7) = "WV"

For countArea = 1 To 7
If Area(countArea) = selArea Then
Worksheets("GBP").ChartObjects("theChart" &
selArea).Visible = True
Worksheets("GBR").ChartObjects("theChart" &
selArea).Visible = True
Worksheets("CP").ChartObjects("theChart" & selArea).Visible
= True
Worksheets("CR").ChartObjects("theChart" & selArea).Visible
= True
Else
Worksheets("GBP").ChartObjects("theChart" &
Area(countArea)).Visible = False
Worksheets("GBR").ChartObjects("theChart" &
Area(countArea)).Visible = False
Worksheets("CP").ChartObjects("theChart" &
Area(countArea)).Visible = False
Worksheets("CR").ChartObjects("theChart" &
Area(countArea)).Visible = False
End If
Next countArea
End Function

[Chip Pearson]

Rather than post 150 lines of code that someone will have to copy
and then configure a workbook and charts appropriately, you
should post the smallest possible example of code that exhibits
the problem.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"hedgehog1" <[email protected]> wrote in message
news:[email protected]...
>I am having a problem with the code in my workbook. I have
>altered the
> code to include some more areas that I have to chart. Will you
> look at
> my code, and tell me what I have left out? I am fairly new to
> coding.
> I get the error in the last section of the code.
> (Worksheets("GBP").ChartObjects("theChart" & selArea).Visible =
> True)
> is the error line.
>
> Thanks for your help in advance!
>
> Option Explicit
>
> Private Sub Workbook_Open()
> Application.WindowState = xlMaximized
> setupToolbar
> showChart "PO"
> End Sub
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Dim Msg As String
> Dim Ans
>
> If Not Me.Saved Then
> Msg = "Do you want to save the changes you made to "
> Msg = Msg & Me.Name & "?"
> Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
> Select Case Ans
> Case vbYes
> Me.Save
> Case vbNo
> Me.Saved = True
> Case vbCancel
> Cancel = True
> Exit Sub
> End Select
> End If
> On Error Resume Next
> Application.CommandBars("SelectArea").Delete
> End Sub
>
> Private Sub Workbook_Deactivate()
> On Error Resume Next
> Application.CommandBars("SelectArea").Visible = False
> End Sub
>
> Private Sub Workbook_Activate()
> On Error Resume Next
> Application.CommandBars("SelectArea").Visible = True
> End Sub
>
> Public Function setupToolbar()
> Dim cmdBarArea As CommandBar
> Dim aBtn As CommandBarControl
>
> 'delete toolbar if already exists
> On Error Resume Next
> Application.CommandBars("SelectArea").Delete
> On Error GoTo Err_setupToolbar
>
> 'create toolbar
> Set cmdBarArea = Application.CommandBars.Add(Name:="Select
> Area",
> Position:=msoBarFloating, MenuBar:=False, temporary:=True)
>
> 'populate the toolbar
> With cmdBarArea.Controls
> Set aBtn = .Add(Type:=msoControlDropdown,
> temporary:=True)
> aBtn.BeginGroup = True
> aBtn.Caption = "Select Area"
> aBtn.Tag = aBtn.Caption
> aBtn.TooltipText = "Select Area"
> aBtn.OnAction = "ThisWorkbook.AreaUpdate"
> aBtn.AddItem "Potomac"
> aBtn.AddItem "BMET"
> aBtn.AddItem "DC/MD West"
> aBtn.AddItem "NORVA"
> aBtn.AddItem "Patuxent"
> aBtn.AddItem "SOVA"
> aBtn.AddItem "WVA/WMD"
> aBtn.ListIndex = 1
> aBtn.Width = 100
> End With
>
> 'final toolbar configuration
> With cmdBarArea
> 'set the toolbar's protection
> .Protection = msoBarNoCustomize + msoBarNoResize +
> msoBarNoChangeVisible + msoBarNoChangeDock
> .Left = 680
> .Top = 120
> .Position = msoBarFloating
> .Visible = True
> End With
>
> Exit_setupToolbar:
> Exit Function
> Err_setupToolbar:
> MsgBox Err.Description & "-" & Err.Number
> Resume Exit_setupToolbar
> End Function
>
> Public Function AreaUpdate()
> Dim AreaSelect As CommandBarControl
>
> With Application.CommandBars("SelectArea")
> Set AreaSelect = .Controls("AreaSelect")
> End With
>
> Select Case Trim(AreaSelect.Text)
> Case "Potomac"
> showChart "PO"
> Case "BMET"
> showChart "BE"
> Case "DC/MD West"
> showChart "DC"
> Case "NORVA"
> showChart "NO"
> Case "Patuxent"
> showChart "PA"
> Case "SOVA"
> showChart "SO"
> Case "WVA/WMD"
> showChart "WV"
>
> Case Else
> MsgBox "not working"
> End Select
> End Function
>
> Public Function showChart(selArea As String)
> Dim Area(1 To 7) As String
> Dim countArea As Integer
>
> Area(1) = "PO"
> Area(2) = "BE"
> Area(3) = "DC"
> Area(4) = "NO"
> Area(5) = "PA"
> Area(6) = "SO"
> Area(7) = "WV"
>
> For countArea = 1 To 7
> If Area(countArea) = selArea Then
> Worksheets("GBP").ChartObjects("theChart" &
> selArea).Visible = True
> Worksheets("GBR").ChartObjects("theChart" &
> selArea).Visible = True
> Worksheets("CP").ChartObjects("theChart" &
> selArea).Visible
> = True
> Worksheets("CR").ChartObjects("theChart" &
> selArea).Visible
> = True
> Else
> Worksheets("GBP").ChartObjects("theChart" &
> Area(countArea)).Visible = False
> Worksheets("GBR").ChartObjects("theChart" &
> Area(countArea)).Visible = False
> Worksheets("CP").ChartObjects("theChart" &
> Area(countArea)).Visible = False
> Worksheets("CR").ChartObjects("theChart" &
> Area(countArea)).Visible = False
> End If
> Next countArea
> End Function
>