VB debug help!

Thoron6

New Member
Joined
Oct 27, 2015
Messages
27
Hi all,

I've got the below code running in my dashboard tab:
Code:
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:
Code:
Private Sub Workbook_Open()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",true)"
End Sub
Any help would be greatly appreciated....
 
Last edited by a moderator:

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,931
Excuse me if I've misread, but what exactly is your question?:confused:
 

Thoron6

New Member
Joined
Oct 27, 2015
Messages
27
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,605
Office Version
365
Platform
Windows
In which sheet is A1 in this line? is it in Sheet4? or Dashboard? (I think it is in Dashboard)
Code:
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)
Code:
Rows("18:31").EntireRow.Hidden = True
 
Last edited:

Thoron6

New Member
Joined
Oct 27, 2015
Messages
27
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

Well-known Member
Joined
Jun 27, 2011
Messages
1,931
So if the sheet changes you need the code to run?


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

Thoron6

New Member
Joined
Oct 27, 2015
Messages
27
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,605
Office Version
365
Platform
Windows
1. Instead of repeating ActiveSheet on every line use With ActiveSheet construction like this
Code:
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 ...
Code:
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..
Code:
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
 
Last edited:

Forum statistics

Threads
1,081,417
Messages
5,358,553
Members
400,503
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top