Workbook Caption/ Title bar

[Jaye7]

I use the following script to remove the userform caption, however, sometimes it decides to remove the workbook caption instead, can someone please provide a script that I can use to redraw/ show the workbook caption.

'Option Private Module
'Returns the Window Handle of the Window
'that is accepting User input.
 Public Declare Function GetForegroundWindow _
   Lib "User32.dll" () As Long

 Private Declare Function GetWindowLong _
  Lib "User32.dll" _
    Alias "GetWindowLongA" _
     (ByVal hwnd As Long, _
      ByVal nIndex As Long) As Long
               
 Private Declare Function SetWindowLong _
  Lib "User32.dll" _
    Alias "SetWindowLongA" _
     (ByVal hwnd As Long, _
      ByVal nIndex As Long, _
      ByVal dwNewLong As Long) As Long

'Redraw the Icons on the Window's Title Bar
 Private Declare Function DrawMenuBar _
   Lib "User32.dll" _
    (ByVal hwnd As Long) As Long

 Private Const GWL_STYLE As Long = (-16)
 Private Const WS_CAPTION = &HC00000

Sub RemoveCaption1()

  Dim BitMask As Long
  Dim hwnd As Long
  Dim WindowStyle As Long
  
    hwnd = GetForegroundWindow
    WindowStyle = GetWindowLong(hwnd, GWL_STYLE)

    BitMask = WindowStyle And (Not WS_CAPTION)

    Call SetWindowLong(hwnd, GWL_STYLE, BitMask)
    Call DrawMenuBar(hwnd)
   
End Sub

I have tried application.caption="" but nothing happens

 

[RoryA]

Where is that code located? You would be better off using Findwindow with the form's caption to get the window handle rather than getforegroundwindow. That way you can ensure you won't get the application window by mistake.

 

[Leith Ross]

Hello Jaye7,

A call to "RemoveCaption1" should be placed in the UserForm's Activate event module. This will prevent accidental removal of the wrong caption.

Private Sub UserForm_Activate()
    RemoveCaption1
End Sub

Sincerely,
Leith Ross

 

[Jaye7]

Thanks Leith,

Do you know of a script that can be used just in case it is removed.

If I close excel and reopen it it returns the workbook caption/ title bar so I thought that there must be a script available to do it also.

Rorya, thanks I will have a look at using the findwindow method also.

 

[Leith Ross]

Hello Jaye7,

I revised the macro to allow you specify the window rather than defaulting to the window that has the focus (Foreground Window). Since Excel 2000, Excel provides the window handle through the Application.Hwnd property. This eliminates the need to search for the window.

Remove and Restore a Window's Caption

'Written: April 01, 2009
'Updated: May 25, 2011 - Added RestoreCaption function.
'Author:  Leith Ross
'Summary: Removes the Title Bar from the specified Window or UserForm.

'Returns the Window Handle of the Window
'that is accepting User input.
 Public Declare Function GetForegroundWindow _
   Lib "user32.dll" () As Long

 Private Declare Function GetWindowLong _
  Lib "user32.dll" _
    Alias "GetWindowLongA" _
     (ByVal hWnd As Long, _
      ByVal nIndex As Long) As Long
               
 Private Declare Function SetWindowLong _
  Lib "user32.dll" _
    Alias "SetWindowLongA" _
     (ByVal hWnd As Long, _
      ByVal nIndex As Long, _
      ByVal dwNewLong As Long) As Long

'Redraw the Icons on the Window's Title Bar
 Private Declare Function DrawMenuBar _
   Lib "user32.dll" _
    (ByVal hWnd As Long) As Long

 Private Const GWL_STYLE As Long = (-16)
 Private Const WS_CAPTION = &HC00000

Sub RemoveCaption(ByVal hWnd As Long)

  Dim BitMask As Long
  Dim WindowStyle As Long
  
    WindowStyle = GetWindowLong(hWnd, GWL_STYLE)

    BitMask = WindowStyle And (Not WS_CAPTION)

    Call SetWindowLong(hWnd, GWL_STYLE, BitMask)
    Call DrawMenuBar(hWnd)
    
End Sub

Sub RestoreCaption(ByVal hWnd As Long)
  
  Dim BitMask As Long
  Dim WindowStyle As Long
  
    WindowStyle = GetWindowLong(hWnd, GWL_STYLE)

    BitMask = WindowStyle Or WS_CAPTION

    Call SetWindowLong(hWnd, GWL_STYLE, BitMask)
    Call DrawMenuBar(hWnd)
    
End Sub

Example of Removing the UserForm's Caption

Private Sub UserForm_Activate()
    RemoveCaption GetForegroundWindow
End Sub

Example of Restoring Excel's Caption

Sub Macro1()
    RestoreCaption Application.Hwnd
End Sub

Sincerely,
Leith Ross

 

[Jaye7]

Thanks Leith,

I am having a problem with the script that goes into the userform, it is saying sub or function not defined.

Private Sub UserForm_Activate()
    RemoveCaption GetForegroundWindow
End Sub

When I change it to call removecaption all I am getting is the userform caption.

 

[Leith Ross]

Hello Jaye7,

You shouldn't get that error since the function is Public. You could get this error if the API code is not in a separate VBA module.

 

[Jaye7]

Leith,

I have separated the API and now I get the following error.

sub or function error, for the GetwindowLong part of the WindowStyle = GetWindowLong(hWnd, GWL_STYLE).

Does that mean that I should show the userform first before the script is applied?

Sub RemoveCaption(ByVal hWnd As Long)


  Dim BitMask As Long
  Dim WindowStyle As Long
  
    WindowStyle = GetWindowLong(hWnd, GWL_STYLE)

    BitMask = WindowStyle And (Not WS_CAPTION)

    Call SetWindowLong(hWnd, GWL_STYLE, BitMask)
    Call DrawMenuBar(hWnd)
    
End Sub

 

[Leith Ross]

Hello Jaye7,

I will need to review your workbook. Can you post it on a public file sharing site like www.mediafire.com?

Sincerely,
Leith Ross

 

[Jaye7]

Leith,

I can't post the workbook as it has private data, however I think that the problem is caused by the following script which I use to lock the userform to the screen so the userform becomes like a toolbar which is visible no matter what application I am in.

Option Private Module
Option Explicit

Private Declare Function SetWindowPos Lib "user32" ( _
    ByVal hWnd As Long, _
    ByVal hWndInsertAfter As Long, _
    ByVal X As Long, _
    ByVal Y As Long, _
    ByVal cx As Long, _
    ByVal cy As Long, _
    ByVal wFlags As Long) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

Private Const HWND_TOPMOST = -1


Sub Updating()
    
    Dim UF As Updating1
    
    Dim UFHandle As Long
    Set UF = New Updating1
    
    UFHandle = FindWindow("ThunderDFrame", UF.Caption)
    SetWindowPos UFHandle, HWND_TOPMOST, UF.Left, UF.Top, UF.Width, UF.Height, 0&
       
    
   UF.Show vbmodeless
    
End Sub

I have been searching for a script that allows me to update this form via worksheet selection change without having to actually click on the form but I think that because it uses the UF as the name it only works if I click on the actual form and have scripts within the form such as me.textbox1 rather than updating1.textbox1.

Thanks

Jaye