Modify the Title Bar of a User Form?

[SpecV]

Hey all,

I have a user form that will be small, and space is at a premium. Does anyone know of a way to modify the size of the title bar? Additionally, is there any way to add a minimize button to it? All help is much appreciated. Thanks!

Jonathan

 

[Tom Urtis]

You do not have a lot of options with this request. It is possible, but not recommended, to create a user form without a title bar altogether, which was posted on this board at
http://216.92.17.166/board2/viewtopic.php?p=213889&highlight=userform+title+bar#213889

Regarding the modification of adding a minimize button, Microsoft says it cannot be done here:
http://www.microsoft.com/officedev/tips/MaxUsFrm.htm

"Just as you do for ordinary windows, you might want to provide a Maximize button on your custom user form. Unfortunately, there is no Visual Basic for Applications (VBA) command that does this. You can get the same results, however, by writing code to set the form's width, height, top, and left settings to those of the host application (the Office application that's running the user form). Of course, this assumes that the host application is maximized already."


Also, think about it - - When visible, a userform takes focus and keeps it. You can't activate another object in the window until you first hide or unload the user form. Therefore, providing an option to minimize it would only confuse the user, and wouldn't make sense. Even if focus was taken off the user form while it's visible, you'd logically have to re-evaluate what the userform's purpose should be.

 

[SpecV]

After scouring the internet, I found a web site by someone named Steven Bullen. http://www.bmsltd.co.uk/Excel/Default.htm. He had an example workbook that does many manipulations to the form, one of which was to change the title bar type to a tool bar type. Its about half the size. Worked out well for me. The following is the code I extrapolated:

Declare Function FindWindow% Lib "user32" Alias "FindWindowA" (ByVal lpclassname As Any, ByVal lpCaption As Any)
Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Sub SmallTitleBar(Caption As String, IsSmall As Boolean)
Dim Window As Integer
Dim Small As Integer

If IsSmall Then Small = 384 Else Small = 256
Window = FindWindow(0&, Caption)
SetWindowLong Window, -20, Small
DrawMenuBar Window
End Sub

Thanks for everyones help!

 

[Tom Urtis]

You know what, months ago I also downloaded that example and several others from Stephen's site, which has a lot of interesting files, and I should have included that with my answer. The thing is though, even with those title bar manipulations, the focus stays on the user form until it is hidden or unloaded, regardless of if it's minimized. Just something to keep aware of, in case users complain, "Now the keyboard doesn't work!!". I'd be interested in the feedback from your end users after a month, if you were to post a follow-up to this thread on how it went for you.

 

[Jengor]

Tom,

just a small observation about focus on a userform. Your comments are true for 97 but certainly in XP and I think 2000 there is the ability to set the forms 'ShowModal' property to false which allows you to show the form yet work elsewhere.

Regards Gordon

 

[Tom Urtis]

Jengor - -

Yes that's correct and I do work off WXP and Excel2002. Did you download the Bullen example? At first, when I set the ShowModal property in his UserForm to false, not only was the focus unchanged, Excel froze after I unloaded the form.

I didn't dissect Stephen's entire code; there are a lot of API calls in his class module. However, his Activate event includes
cbModal.Value = True
so I guess we could set it to False in other events in the module. I experimented a little with the btnOK events but no luck.

You're probably right; it might be an easy adjustment. I didn't delve into the Bullen code completely to get the Modal property to behave the way I would want, but as long as it works for SpecV that's the important thing.

Thanks.

 

[James]

Userform1.Show
AppActivate."microsoft excel"
Range("A1").Select

James

 

[Ivan F Moala]

Also be warned that if you remove the Titlebar you CANNOT move the form.
You need other API's to do this.

For examples see here

http://ozgrid.com/forum/viewthread.php?tid=3225

The example form Also shows how to Move a captionless form as well
as other things like changing the Shape, making it Tranparent etc