0

I've written a little toggle macro for switching between auto and manual calcs (see below). Although a little pop up tells you it's on manual, it'd be cool if the colour scheme could change or some other visually obvious thing to remind them that manual calcs are on.

Some ideas I've had so far:

  • Change interface colour scheme: don't think this can be done with VBA.. also, it changes all office products, not just Excel
  • Change grid line colours: this works but only on the active sheet. Also looks kinda bad
  • A floating box in the top right corner: I made on of these another time but the code has to be embedded in the worksheet tab for it to move around the page. This option would be good if it's possible to do it in a macro, but i'm not sure it is...
  • Change the column header colours: again, not sure if this is possible.
  • Add a new red-coloured tab at the front called 'manual calcs on' - EDIT: added this to my code
  • Change the StatusBar (from Gary's Student's suggestion): also added this to my code

Anyone got any other ideas or know how to pull off these ideas up above?

Any creativity and ideas are greatly appreciated :)

Thanks all, Lucas

PS. Here's my code for the toggle:

Sub Toggle_Auto_Calculate()

' ToggleGUI Macro
'
' Keyboard Shortcut: Ctrl+shift+a
'
If Application.Calculation = xlManual Then
Application.StatusBar = ""
Application.DisplayAlerts = False
  Sheets("Manual Calcs On").Select
    ActiveWindow.SelectedSheets.Delete
MsgBox "Auto calcs on"
Application.Calculation = xlAutomatic

Else
Application.Calculation = xlManual
Application.StatusBar = "MANUAL CALCULATION"
 Worksheets.Add(Before:=Worksheets(1)).Name = "Manual Calcs on"
    With ActiveWorkbook.Sheets("Manual Calcs On").Tab
        .Color = 255
        .TintAndShade = 0
    End With
MsgBox "Manual calcs on"
End If
Application.DisplayAlerts = True

End Sub
Lucas
  • 401
  • 1
  • 8
  • 20

1 Answers1

1

Consider posting it on the status bar:

Sub temp()
        Application.StatusBar = "MANUAL CALCULATION "
End Sub

EDIT#1:

A little less subtle:

Sub temp()
    Application.StatusBar = "****************MANUAL CALCULATION******************"
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99