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,

I am not sure I follow what you mean. The way you describe the problem sounds like the UserForm is not active and that is why you need to click it to activate it and then it will update. Update from where, a worksheet? Please explain to me in more detail what is happening between the UserForm , Excel and the data that is input/output.

Sincerely,
Leith Ross

 

[Jaye7]

Ok,

The script that I provided shows a captionless modeless form that is locked/ pinned to the screen, the userform stays pinned to the screen even if you change applications, i.e. from Excel to Internet Explorer. The problem is typically you could put a code in the worksheet selection change that would then update a form i.e

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
UserForm1.TextBox1.Value = Range("A1").Value
End Sub

However, with the locked form I have tried changing the userform1 to UF.textbox1 or Me.textbox1 but it will not update.

However if I was to put a click event on textbox1 in the userform with the following it will work, but I must click on the textbox on the form to work.

On Error Resume Next
me.TextBox1.Value = Range("A1").Value

I think that it is because the following sets the form as generic or something, I am not sure though.

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

 

[Jaye7]

Leith,

The following is a link to a dummy workbook, you will probably need to copy and paste the link into your browser.

http://jt7.angelfire.com/TESTBOOK.xls

If you click on the run macros dialog box and run USERF1 it will pop up the userform.

If you change a value in cell A1 the form should update the value in the textbox automatically.

It is only updating if you actually click on the textbox yourself.

Thanks

 

[Leith Ross]

Hello Jaye,

Try this event procedure code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  On Error Resume Next
  UserForm1.Controls("TextBox1").Value = Range("A1").Value
End Sub</pre>

Sincerely,
Leith Ross

 

[Jaye7]

Unfortunately it didn't work.

 

[Leith Ross]

Hello Jaye,

Thanks for the example workbook. I have it working. You can download it from http://www.mediafire.com/?7j5359okivkchez

Sincerely,
Leith Ross

 

[Jaye7]

Hi Leith,

I downloaded the file but if you change applications i.e. from excel to internet explorer the userform does not remain locked/ pinned to the screen so that it is viewable still in internet explorer.

If you test the workbook that I gave you the form remains pinned to the screen when you change from excel to internet explorer or any other app, however it will not update unfortunately.

 

[Leith Ross]

Hello Jaye,

The UserForm will only update from Excel and not any other application because the UserForm is "owned" by the Excel application. I am uncertain as to why you want to keep the UserForm on top of all other windows when it can only accept input and be updated when Excel is the active window.

 

[Jaye7]

I use it as a toolbar to enter data and also as a timer, however the timer will not work while the form is pinned to the screen.

I.e. if you have a countdown clock on the form it won't update, I use a timer to know when to perform certain tasks etc...

I know that you can download apps I just didn't want another app that I have to use where excel might have the ability to work and as it replies on the spreadsheet for info on the timer another app would be annoying.

 

[Leith Ross]

Hello Jaye,

If you use an API timer on the UserForm instead of Application.OnTime then you shouldn't have problems. The API timer runs independently from Excel.