Hide/show scrollbar with combobox

[Bandito1]

Hi all,

I'm using the following code to hide/show charts and scrollbars.

Private Sub cmbDepartment_Change()Dim SelectedChart As String
Dim SelectedScrollbar As String
Dim blnVisible As Boolean



Me.cmbFilter.ListFillRange = Me.cmbDepartment
Range("B16") = cmbDepartment.ListIndex + 1


SelectedChart = "Chart" & Range("B16")
SelectedScrollbar = "Scrollbar" & Range("B16")  


    ActiveSheet.ChartObjects("Chart1").Visible = blnVisible
    ActiveSheet.Shapes("Scrollbar1").Visible = blnVisible
    ActiveSheet.ChartObjects("Chart2").Visible = blnVisible
    ActiveSheet.Shapes("Scrollbar2").Visible = blnVisible
    ActiveSheet.ChartObjects("Chart3").Visible = blnVisible
    ActiveSheet.Shapes("Scrollbar3").Visible = blnVisible
    ActiveSheet.ChartObjects("Chart4").Visible = blnVisible
    ActiveSheet.Shapes("Scrollbar4").Visible = blnVisible
    


    If Not blnVisible Then ActiveSheet.ChartObjects(SelectedChart).Visible = True
    If Not blnVisible Then ActiveSheet.Shapes(SelectedScrollbar).Visible = True
    
End Sub

Is there a way to reduce to code in size? Especially this part:

    ActiveSheet.ChartObjects("Chart1").Visible = blnVisible    
    ActiveSheet.Shapes("Scrollbar1").Visible = blnVisible
    ActiveSheet.ChartObjects("Chart2").Visible = blnVisible
    ActiveSheet.Shapes("Scrollbar2").Visible = blnVisible
    ActiveSheet.ChartObjects("Chart3").Visible = blnVisible
    ActiveSheet.Shapes("Scrollbar3").Visible = blnVisible
    ActiveSheet.ChartObjects("Chart4").Visible = blnVisible
    ActiveSheet.Shapes("Scrollbar4").Visible = blnVisible

You can imagine if I have 50 charts the length of the code will be very big.

 

[Norie]

You could try something like this.

For I = 1 To 50
    ActiveSheet.ChartObjects("Chart" & I).Visible = blnVisible
    ActiveSheet.Shapes("Scrollbar" & I).Visible = blnVisible
Next I

 

[Bandito1]

It returns the error:

The item with the specified name wasn't found.

 

[Norie]

I've assumed you have 50 charts named 'Chart1', 'Chart2' etc. and similarly 50 scrollbars named 'Scrollbar1', 'Scrollbar2' etc.

If that's not the case then a different approach will be required.

 

[Bandito1]

Well, not yet

It was an example.

Currently i have 3 charts and 3 scrollbars but the sheet is in development.
This amount can grow to for example 50 charts and 50 scrollbars.