VB debug help!

[Thoron6]

Hi all,

I've got the below code running in my dashboard tab:

Sub chart_visibility()
     ActiveWorkbook.Sheets("Dashboard").Activate
       If Range("A1").Value = "1" Then
        ActiveSheet.ChartObjects("Chart 3").Visible = False
        ActiveSheet.ChartObjects("Chart 1").Visible = False
        ActiveSheet.Shapes("TextBox 12").Visible = False
        ActiveSheet.Shapes("TextBox 13").Visible = False
        ActiveSheet.Shapes("TextBox 14").Visible = False
        ActiveSheet.Shapes("TextBox 15").Visible = False
        ActiveSheet.Shapes("TextBox 16").Visible = False
        ActiveSheet.Shapes("TextBox 17").Visible = False
        ActiveSheet.Shapes("TextBox 8").Visible = False
        ActiveSheet.Shapes("TextBox 5").Visible = True
        Rows("18:31").EntireRow.Hidden = True
        
        
       Else
        ActiveSheet.ChartObjects("Chart 3").Visible = True
        ActiveSheet.ChartObjects("Chart 1").Visible = True
        ActiveSheet.Shapes("TextBox 12").Visible = True
        ActiveSheet.Shapes("TextBox 13").Visible = True
        ActiveSheet.Shapes("TextBox 14").Visible = True
        ActiveSheet.Shapes("TextBox 15").Visible = True
        ActiveSheet.Shapes("TextBox 16").Visible = True
        ActiveSheet.Shapes("TextBox 17").Visible = True
        ActiveSheet.Shapes("TextBox 8").Visible = True
        ActiveSheet.Shapes("TextBox 5").Visible = False
        Rows("18:31").EntireRow.Hidden = False
      End If
    
End Sub


Private Sub Worksheet_Calculate()
Static OldVal As Variant
If Range("A1").Value <> OldVal Then
    OldVal = Range("A1").Value
    Call Sheet4.chart_visibility                <------ This row is highlighted on debug.             
End If
End Sub

The only other bit of VB is in the worksheet to hide the ribbon:

Private Sub Workbook_Open()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",true)"
End Sub

Any help would be greatly appreciated....

 

[gallen]

Excuse me if I've misread, but what exactly is your question?

 

[Thoron6]

Sorry, so I have all of the above code in the sheet section and it all references a formula cell (A1). Once changes occur on that sheet and the formula changes, I need the code above to kick in. I have no code written in modules (Which I think the issue may be) but when I copy both the codes above into modules, nothing works. I'm just trying to get the above code to work and think it's probably pretty close and needs a little tweak? Thank you

 

[Yongle]

In which sheet is A1 in this line? is it in Sheet4? or Dashboard? (I think it is in Dashboard)

If Range("A1").Value = "1" Then vis = False Else vis = True

In which sheet are the Rows that you want hidden\not hidden? (I think they are in Dashboard)

Rows("18:31").EntireRow.Hidden = True

 

[Thoron6]

They are all in sheet 4 which I have renamed "Dashboard" ... Also, a new update. All of the above works fine until I protect the sheet.

 

[gallen]

So if the sheet changes you need the code to run?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    chart_visibility
End Sub

 

[Thoron6]

Thank you, I figured it out. When I was protecting the sheet it was stopping the macro from running some of the actions. Thanks for the replies. This can be deleted now. cheers

 

[Yongle]

1. Instead of repeating ActiveSheet on every line use With ActiveSheet construction like this

With [COLOR=#ff0000]ActiveSheet[/COLOR]
        .ChartObjects("Chart 3").Visible = True
        .ChartObjects("Chart 1").Visible = True
    etc
End With

2. Me can be used instead of ActiveSheet because chart_visibility is in the sheet module of the sheet that the code applies to

3. Instead of repeating Visible = True (or False) and listing all the objects listed twice, consider using a Boolean variable like this ...

Sub chart_visibility()
    Dim[COLOR=#008080] vis As Boolean[/COLOR]
    Me.Activate  
    If Range("A1").Value = "1" Then [COLOR=#008080]vis = False[/COLOR] Else [COLOR=#008080]vis = True[/COLOR]
    With [COLOR=#ff0000]Me[/COLOR]
        .ChartObjects("Chart 3").Visible = [COLOR=#008080]vis[/COLOR]
        .ChartObjects("Chart 1").Visible = [COLOR=#008080]vis[/COLOR]
        .Shapes("TextBox 12").Visible = [COLOR=#008080]vis[/COLOR]
        .Shapes("TextBox 13").Visible =[COLOR=#008080] vis[/COLOR]
        .Shapes("TextBox 14").Visible = [COLOR=#008080]vis[/COLOR]
        .Shapes("TextBox 15").Visible = [COLOR=#008080]vis[/COLOR]
        .Shapes("TextBox 16").Visible = [COLOR=#008080]vis[/COLOR]
        .Shapes("TextBox 17").Visible = [COLOR=#008080]vis[/COLOR]
        .Shapes("TextBox 8").Visible = [COLOR=#008080]vis[/COLOR]
        
        .Shapes("TextBox 5").Visible = [COLOR=#008080]Not vis[/COLOR]
        Rows("18:31").EntireRow.Hidden = [COLOR=#008080]Not vis[/COLOR]
    End With
End Sub

And for info only..
Both pairs of lines are equally valid and mean the same thing inside a sheet module..

If Range("A1").Value = "1" Then vis = False Else vis = True
If Me.Range("A1").Value = "1" Then vis = False Else vis = True

Rows("18:31").EntireRow.Hidden = Not vis
Me.Rows("18:31").EntireRow.Hidden = Not vis