Cool Dashboards - Tricks

Anybody got any cool dashboard examples they want to share? Any tricks on how to make dashboards look larger than life? Use of macros, formulas, conditional formatting, pivot charts, colors, etc.???

I will start by sharing a solution to make the dashboard remain in full-screen with no ribbon, tabs, etc. so that the dashboard takes up the full screen. If the user resizes the window (e.g. to move it another monitor), it will go full-screen again in a few seconds.

Hope this helps and hope to get some other good ideas rolling...

abousetta

A little bit too cool my friend.

Can you please post the code you used to scr*** up my user interface.
And can you please post the code to restore everything to normal ?

You don't need all that code, Full Screen hides the FormulaBar, etc

mods

delete this file, it does indeed screw up Excel's settings

If you do want code to do this try searching my posts because I posted code that did this correctly some time ago.

Any one who needs to know

1. Restore Full Screen by clicking the small squares next to the close button, top right of screen
2.View Tab & restore Formulabar, etc,

Isn't that why abousetta called it a TRICK? Just give him bad rep for the post and beware next time.

Roy, if you have a better mouse trap, I obviously would like to see it, but I don't think I'm going to go through your 25,000 post to find it.

snb, sorry about that. The undo code would be:

    With Application
        .DisplayFullScreen = False
        .ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", True)"
        .DisplayFormulaBar = True
    End With
    With ActiveWindow
        .DisplayHeadings = True
        .DisplayGridlines = True
        .DisplayWorkbookTabs = True
    End With

Or simple ctrl + PageDown when the original workbook that caused the problem is open since it will not match the sheet name to the first sheet in the workbook.

abousetta

*** Modified ***

I have deleted the attachments but will instead provide the code for advanced users in a later post

This works in Excel 2003 & Excel 2010.

Display Headngs etc may as well be done manually.

Switching workbooks will also restore settings for the other workbook

Dear RoyUK,

As I searched through the forum for help, I read this thread and found your fullscreen code in the sample file very helpful indeed. I wish to say a big 'thank you' for it. It really met a good deal of my needs.

When I use the code, however, I find that what still shows are the 3 buttons (minimize, restore and close) at the top right-hand corner. For what I am doing, I really need those 3 buttons disabled. I have an EXIT command button on the Main Menu sheet which SAVES & EXITS from the workbook that houses the Excel program that I am trying to design for my small family business.

In my further search, I found a code that disables the said 3 buttons, but I do not know how to incorporate it into your fullscreen code. I am totally new to vba/macros and would, please, appreciate your kind help. The code that I found is as follows:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
End Sub

Private Sub Workbook_WindowResize(ByVal Wn As Window)
Wn.WindowState = xlMaximized
EnableResize = False
End Sub

I should be grateful if you would kindly help me to incorporate this 3-buttons-disable code to your fullscreen code for my purpose, please.

Meanwhile, I hope I am not hijacking another forum member's thread. I was looking for a solution to my Excel 2007 problem, came across the thread and saw that the solution you provided met my needs, hence this post for your attention, please. I do not know how best to reach you concerning this. And if I have done anything wrong, I sincerely apologise.

Thank you in anticipation of your help.

Newqueen

Thanks Roy. I have added Workbook_Activate and Workbook_Deactivate codes to increase functionality. The new code is as follows:

Sheet1:

Option Explicit

Private Sub Worksheet_Activate()
    TurnOff = False
    Application.Run "DashboardOn"
End Sub

ThisWorkbook:

Option Explicit

Private Sub Workbook_Open()
    Application.Run "DashboardOn"
    TurnOff = False
End Sub

Private Sub Workbook_Activate()
    Application.Run "DashboardOn"
    TurnOff = False
End Sub

Private Sub Workbook_Deactivate()
    Application.Run "DashboardOff"
    TurnOff = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.Run "DashboardOff"
    TurnOff = True
End Sub

Standard Module:

Option Explicit
Public TurnOff As Boolean

Private Sub DashboardOn()
    With Application
        .ScreenUpdating = False
        If ActiveWorkbook.Name = ThisWorkbook.Name And _
         ActiveSheet.Name = Sheets(1).Name Then                     ' Change sheet name to fit your needs
            TurnOff = False
            With ActiveWindow
                .DisplayHeadings = False                            ' Remove Heading
                .DisplayGridlines = False                           ' Remove Gridlines
                .DisplayWorkbookTabs = False                        ' Remove Tabs
            End With
            .DisplayFullScreen = True                               ' Force fullscreen
            .ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", False)"   ' Force the ribbon not to show
          Else: Application.Run "DashboardOff"                      ' Another sheet is chosen so return everything to normal
        End If
        .ScreenUpdating = True
        DoEvents
        If TurnOff = False Then .OnTime Now() + TimeValue("00:00:03"), "DashboardOn"
    End With
End Sub

Private Sub DashboardOff()
' Return items back on and restore screen to original size
    TurnOff = True
    With Application
        .ScreenUpdating = False
        .DisplayFullScreen = False
        .ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", True)"
    End With
    Windows(1).WindowState = xlMaximized
    With ActiveWindow
        .DisplayHeadings = True
        .DisplayGridlines = True
        .DisplayWorkbookTabs = True
    End With
End Sub

No hijacking, I'm open to any discussion on the topic... But TMS provided a solution in your original thread. I'm working on another approach. If I get it working I will post back.

post self-deleted

Thanks FDibbins for your clarification.

In my Post #10 above, I wasn't sure if it was OK or not hence I apologised if it turned out that I had done something wrong.

Again, I'm sorry for going against any of the forum rules. It was not intended.

Kind Regards.

Newqueen

Actually, in this case the moderator is wrong as post #10 does not violate Rule #2 as this thread is in the Water Cooler and I (the original poster) requested anyone to share any examples.

Sorry guys, you are both correct, seeing as this is the water cooler. My sincere apologies

No worries... it's all good.

Thanks for the understanding

There's no dashboards to look at in this thread.

In general, I never really got any strong feedback. Post #8 has RoyUK's suggestion. Post #9 has my suggested code.

There used to be a workbook in an earlier post but it was deleted because the setting were not reversed when you moved from one workbook to another. People were freaking out that I messed up their setting and so I posted the remedy code and a new version that addressed the issue of switching between workbooks (or worksheets for that matter).

abousetta