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

 

[Leith Ross]

Hello Jaye,

Did you try executing the macro to remove the caption before or after the macro to lock it? You should lock it first. What version of Windows are you running?

Sincerely,
Leith Ross

 

[Jaye7]

Leith,

I am using 2003 version, it does lock the form first then shows the form and then in the activate event of the form it removes the caption.

 

[Leith Ross]

Hello Jaye,

We are using the same OS. Your last post sounds like it it working, or am I mistaken?

 

[Jaye7]

Leith,

I have it working using a different script but occasionally the script gliches and I end up with no Title Bar in excel even though the removecaption is in the userform activate event.

The script that you provided is locking up at the getwindowlong

WindowStyle = GetWindowLong(hWnd, GWL_STYLE)

I wanted a different script than the one of was using previously as when the script cliches and I lose the Title Bar of the actual excel window then I have to close all my workbooks, close excel and then reopen to get the title bar back.

Really I just want a script that will restore the title bar so that I don't have to close excel. I tried the application.caption = "" (or with a name between " ") but the title bar doesn't get restored.

 

[Leith Ross]

Hello Jaye,

Did you run the macro RestoreCaption that was part of the macro to remove the caption?

Sub Macro1()
    RestoreCaption Application.Hwnd
End Sub

Sincerely,
Leith Ross

 

[Jaye7]

Leith,

It's before that that it jams up, it is within the following script.

Sub RemoveCaption(ByVal hWnd As Long)


  Dim BitMask As Long
  Dim WindowStyle As Long
  
    WindowStyle = [B][COLOR=Blue]GetWindowLong[/COLOR][/B](hWnd, GWL_STYLE)

    BitMask = WindowStyle And (Not WS_CAPTION)

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

 

[Jaye7]

Sorry Leith,

I moved one of the scripts that should have remained in teh API section and that is what was locking it up, now it is restoring the excel windows caption but the caption keeps flickering constantly like it is updating, unless I hit the escape key it just keeps running.

 

[Leith Ross]

Hello Jaye,

There are only 2 reasons I can think of why this API call is failing: The hWnd is zero or invalid or your machine requires the Unicode version of the API calls. If this is true, you will to change the GetWindowLong and SetWindowLong calls. Here are the Unicode versions...

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

Sincerely,
Leith Ross

 

[Leith Ross]

Hello Jaye,

If it is constantly updating then you have loop in your code that should be there. But where the loop is, I can't say without seeing all of the code.

 

[Jaye7]

It's working now Leith, thanks very much, this has been bugging me for a while, now I can restore the caption.

Do you know of a script that will work to update the form when locked to the screen without having to actually click on the form.

The form is called by the following script.

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