personalized command bar disappears when minimizing excel

personalized command bar disappears when minimizing excel

• T. RUIZ

  So I've tried your Solution, chas, and it works fine on my computer, however, at work the exactly same code produces the following:
  
  Application in full screen works perfekt - when minimizing and restoring again, application goes into windowed mode and my toolbar is gone - have to restart again.
  The application should always stay in full screen, there is no part that's in windowed mode.
  
  Maybe I'll explain my application a little more detailed:
  
  The goal was to create a kalendar to put data about our outpatients into. (When they have to come to the urological ward, what operation is to be done, whose the doctor in charge and stuff like that), in the ward the same excel workbook is running, so the nurses can make printouts about the information given above. So in the background an access-database is running on a server, where on about 11 computers (and in the future even more) the same excel workbook (the one we are talking about) is running. Some of the pc's still run under NT whereas most of the others run under xp.
  
  And no one except me knows a bit about excel or access or stuff like that. So its my goal to create an application thats easy to understand for the user and also easy for me to setup and cultivate.
  
  So any idea would be very helpful, still the idea with the hooking into the excel process sounds very reasonable, but the knowhow seems to be deeply buried :-)!
  
  Thanks a lot, herkus
  
  
  
  
  

• Sam Jost

  Hello Herkus,

  Must admit this one did take a bit of experimenting and I cannot explain why this is happening, BUT I did find a way to get it working.

  Perhaps someone more knowledable (DEREK where are you ) can enlighten us.

  I found that if you call the routine that sets your new commandbar, then open a new window and immediately hide it again, then your custom Minimise button will work. Once again do not know why. To achieve this:

  Put this in your workbook open event.

  Private Sub Workbook_Open()
  Call start ' show custom menubar
  ActiveWindow.NewWindow ' activate new window
  ActiveWindow.Visible = False ' then hide it
  End Sub
  

  This is in your code Module, probably Module 1:

  Sub start()
  Dim oldMbar As CommandBar, newMbar As CommandBar
  Set oldMbar = CommandBars.ActiveMenuBar
  

  For Each cbar In Application.CommandBars ' You need to delete custom bar if it already exists
  If cbar.Name = "tmpMenuBar" Then
  cbar.Delete
  End If
  Next

  Application.CommandBars("Formatting").Enabled = False
  Application.CommandBars("Standard").Enabled = False
  Application.DisplayFormulaBar = False

  Set newMbar = CommandBars.Add _
  (Name:="tmpMenuBar", Position:=msoBarRight, _
  MenuBar:=True, temporary:=True)
  Application.DisplayFullScreen = True

  Set myMin = CommandBars("tmpMenuBar").Controls.Add(Type:=msoControlPopup)
  With myMin
  .Caption = "Minimise"
  .OnAction = "AppMin"
  End With

  With newMbar
  .Visible = True
  .Protection = msoBarNoMove
  End With
  End Sub

  Sub Appmin()
  Application.WindowState = xlMinimized
  End Sub

  After having done this, the Window Caption at the bottom of the screen will display MenuTest:1 rather than MenuTest only (MenuTest was the name of my workbook).

  As long as the caption at the bottom is appended with :1 then you can "custom minimise" to your heart's content !!

  Hope this helps

  Chas

  
  

  
  

• B M E

  The menu/commandbar settings for fullscreen mode are separate from those for the normal windowed mode. The approach I'd take would be to set a hook on the Excel process and trap for the appropriate resize messages. Chas, your solution is probably much more reasonable for the "average" Excel/VBA user.

  
  
  

• AE_Cory

  Thanks for the quick answer. I have found out that the problem only exists if the application runs in full screen (Application.DisplayFullScreen = True).
  With the code below, if I minimise through my personalized button (Makro "Appminimize" down below), and maximize again, my bar has disappeared. When I run the application in normal mode, everything works out fine.
  Any idea Thanks a lot!
  
  Sub Symbolleistendarstellung()
  'deactivate standard bars
  Application.CommandBars("worksheet menu bar").Enabled = False
  Application.CommandBars("Formatting").Enabled = False
  Application.CommandBars("Standard").Enabled = False
  Application.DisplayFormulaBar = False
  Application.DisplayFullScreen = True 'the error occurs with this being true
  'activate personalised bar
  Application.CommandBars(ActCommandBar).Enabled = True
  Application.CommandBars(ActCommandBar).Visible = True
  Application.CommandBars(ActCommandBar).Position = msoBarTop
  End Sub
  
  Sub AppMinimize()
  Application.WindowState = xlMinimized
  End Sub
  
  
  

• Sugan_Dave

  Thanks a lot, duck thing and chas for the quick and efficient help. I'm gonna try your suggestions and will report about it. I was also so far not able to identify an event to hook into the excel process.
  
  herkus
  
  
  
  

• dragoncells

  Thanks for the info Duck Thing, it explains the WHY it was happening.

  Chas

  
  

• ytandeta

  Alright, I've designed a quick workaround for this. Code follows:

  Option Explicit
  Option Base 0

  Private FullScreenCommandBarStatus As Collection
  Private WindowedScreenCommandBarStatus As Collection

  ' demonstrate the toolbar save/load implementation using VBA calls
  ' this is suitable for a situation where the application always knows whether the user
  ' will enable/disable fullscreen mode

  Private Sub CommandButton1_Click()
  Set WindowedScreenCommandBarStatus = SaveCommandBars
  With Application
  .DisplayFullScreen = True
  LoadCommandBars WindowedScreenCommandBarStatus
  MsgBox "Toolbars saved. Now returning to windowed mode."
  .DisplayFullScreen = False
  End With
  LoadCommandBars WindowedScreenCommandBarStatus
  End Sub

  Private Function SaveCommandBars() As Collection
  Dim tcmdbar As CommandBar
  Dim CommandBarStatus As New Collection
  With Application
  For Each tcmdbar In .CommandBars
  CommandBarStatus.Add Array(tcmdbar.Name, tcmdbar.Visible, tcmdbar.Position)
  Next tcmdbar
  End With
  Set SaveCommandBars = CommandBarStatus
  End Function

  Private Sub LoadCommandBars(cbcoll As Collection)
  On Error GoTo ERR_HANDLE
  If cbcoll Is Nothing Then Exit Sub
  Dim pp As Variant
  ' pp(0) contains the command bar name
  ' pp(1) contains the command bar's visibility
  
  With Application
  For Each pp In cbcoll
  .CommandBars(pp(0)).Visible = pp(1)
  .CommandBars(pp(0)).Position = pp(2)
  Next pp
  End With
  Exit Sub
  
  ERR_HANDLE:
  ' Do nothing with errors.
  ' (I'm assuming that any errors will be something we can ignore, like
  ' a toolbar that was present when SaveCommandBars was called has since
  ' been disabled or deleted.)
  
  Resume Next
  End Sub

  The issue with this code, as I mentioned in the comments, is that it assumes the application knows whether the user is "about to switch to"/"has just returned from" fullscreen mode. This may be true for your application. For example, when the user clicks "Show Me The Money", your code might switch the workbook into fullscreen mode. The code I've supplied would be applicable in this case. On the other hand, if the user has free reign over the Excel menus, they could switch to fullscreen mode "on the fly" -- in which case this code wouldn't be activated.

  As I hinted earlier, the way around this limitation would be to set a hook on the Excel process that responds to WM_* messages. I checked all of the events exposed to VBA (e.g., Workbook_Activate, Workbook_SheetActivate, Workbook_SheetChange, etc.) and it doesn't look like any of them are called when Excel switches from fullscreen to "normal" mode. Perhaps I'm just missing the relevant event.

  
  
  

• FredMunro

  Hello again Herkus,

  I am not sure what the rest of your code does but remember to reset the following to what they were originally

  :ie

  Application.CommandBars("Formatting").Enabled = False ' dont forget to set these to true again
  Application.CommandBars("Standard").Enabled = False ' dont forget to set these to true again.

  After experimenting with the code that I sent you, I found that I could no longer "see" these menus any more, even after closing Excel and opening another workbook !!

  Chas
  

  
  

• Hooper

  Hello

  What happens if you minmise Excel with the built in minimise button.

  What code is your custom commandbar "minimise button" running

  Please post your code.

  Chas

  
  

• R Raghu

  Hello Herkus,

  You said that my suggestion works okay on your computer, which of the computers is it NOT working on, only the NT ones or all of them. Am just trying to work out if this is the problem now.

  I will look at another approach but must admit I may come up with nothing!!.

  If I find anything, will post it.

  Let me know whether it is NT the program is not working on though.

  Chas

  
  
• personalized command bar disappears when minimizing excel