[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" wrote in message
oups.com...
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