2

I was making an application and I went to hide the formula bar and make it full screen in my Workbook_Activate event, then show the formula bar and make it windowed(is that a word?) in my Workbook_Deactivate event. I actually struggled with this for a while. I kept having a problem with the formula bar showing up when it wasn't supposed to or disappearing when I wanted it there. I finally got it to work by making sure I used the DisplayFullScreen first and only then using the DisplayFormulaBar method.

Does anyone know why you would need to put these in a specific order for them to work together? I couldn't find anything when I was looking for it.

I'm using Excel 2010.

EDIT: Here's my code.

Private Sub Workbook_Activate()
    Application.ScreenUpdating = False

    ThisWorkbook.Sheets(2).Activate
    ActiveWindow.DisplayGridlines = False
    ActiveWindow.DisplayHeadings = False

    ThisWorkbook.Sheets(1).Activate
    ActiveWindow.DisplayGridlines = False
    ActiveWindow.DisplayHeadings = False

    Application.DisplayFullScreen = True
    Application.DisplayFormulaBar = False

    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Deactivate()
    Application.DisplayFullScreen = False
    Application.DisplayFormulaBar = True
End Sub
MatthewD
  • 6,719
  • 5
  • 22
  • 41
PermaNoob
  • 849
  • 5
  • 17
  • Please post your functions. – MatthewD Aug 31 '15 at 18:23
  • I'm not sure why that would happen, but I tried it in both sequences in Excel 2013 and it didn't seem to make a difference. Perhaps it was a bug that was corrected? – Soulfire Aug 31 '15 at 18:24
  • @MatthewD Just made an edit to add the code. – PermaNoob Aug 31 '15 at 18:27
  • the .activate is always suspicious. http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros You can declare dim ws as Excel.Worksheet Set ws = ActiveWorkbook.Sheets("Sheet1") then ws.Activate. I have never had issues using it on a declared object. – MatthewD Aug 31 '15 at 18:41
  • @MatthewD That could be it, but even if I comment those sections out completely, I still run into the same "issue"... – PermaNoob Aug 31 '15 at 19:18
  • 1
    @MatthewD I found it. See the answer below. Bit of an oversight not to at least mention it in the documentation for affected properties. – PermaNoob Aug 31 '15 at 21:52

1 Answers1

2

This is from the MSDN documentation: "Toolbars, the status bar, and the formula bar maintain separate display settings for full-screen mode and normal mode."

https://msdn.microsoft.com/en-us/library/office/ff838060.aspx

I was searching for mostly DisplayFormulaBar topics since that was what was showing up strangely for me. Hopefully this helps anyone who searches for it in the future.

PermaNoob
  • 849
  • 5
  • 17