Create a maximize & minimize button in userform

[adam]

Hi anyone,

The following code inserts a maximize and minimize button to the userform and places it on the task bar when minimized.

Code: Select all

Option Explicit

Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare Function ShowWindow Lib "user32" (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Private Declare Function SetFocus Lib "user32" (ByVal hWnd As Long) As Long

Private Const GWL_STYLE As Long = -16
Private Const GWL_EXSTYLE As Long = -20
Private Const WS_CAPTION As Long = &HC00000
Private Const WS_MINIMIZEBOX As Long = &H20000
Private Const WS_MAXIMIZEBOX As Long = &H10000
Private Const WS_POPUP As Long = &H80000000
Private Const WS_VISIBLE As Long = &H10000000
Private Const WS_EX_DLGMODALFRAME As Long = &H1
Private Const WS_EX_APPWINDOW As Long = &H40000
Private Const SW_SHOW As Long = 5


Private Sub UserForm_Activate()
    Dim lngHwnd As Long
    Dim lngCurrentStyle As Long, lngNewStyle As Long

    If Val(Application.Version) < 9 Then
        lngHwnd = FindWindow("ThunderXFrame", Me.Caption)  'XL97
    Else
        lngHwnd = FindWindow("ThunderDFrame", Me.Caption)  'XL2000, XP, 2003?
    End If

    'Set the Windows style so that the userform has a minimise and maximise button
    lngCurrentStyle = GetWindowLong(lngHwnd, GWL_STYLE)
    lngNewStyle = lngCurrentStyle Or WS_MINIMIZEBOX Or WS_MAXIMIZEBOX
    lngNewStyle = lngNewStyle And Not WS_VISIBLE And Not WS_POPUP
    SetWindowLong lngHwnd, GWL_STYLE, lngNewStyle


    'Set the extended style to provide a taskbar icon
    lngCurrentStyle = GetWindowLong(lngHwnd, GWL_EXSTYLE)
    lngNewStyle = lngCurrentStyle Or WS_EX_APPWINDOW
    SetWindowLong lngHwnd, GWL_EXSTYLE, lngNewStyle
    ShowWindow lngHwnd, SW_SHOW

End Sub

But When I insert the following code into it I get compile error messages.

Code: Select all

Private Sub UserForm_Initialize()
Me.WebBrowser1.Navigate2 smURL & "http://www.google.com"
End Sub

Could you please let me know what might be the reason for this?

Thanks in advance.

[HansV]

You don't declare smURL.

[adam]

When Private Sub UserForm_Initialise() is changed to Private Sub UserForm_Declare() the userform opens. But does not display the web page? What have I misunderstood here?

[HansV]

Changing the name of Userform_Initialize (not Initialise) to Userform_Declare makes no sense whatsoever. If you need the variable smURL at all, you should declare this variable and assign it a meaningful value.

[adam]

Changing the name of Userform_Initialize (not Initialise) to Userform_Declare makes no sense whatsoever.

I knew this does not make any sense. But since your answer regarding my question wasn't detailed, it didnt clear my doubt. And for this reason I did ask how to make the webpage appear in the userform with the maximize & minimize button in the userform.

But still I cant load my form with the webpage and with the minimize and maximize buttons.

[HansV]

Have you declared smURL yet?
If you don't know what that means, you might consider taking a course on the basics of VBA.

[adam]

Thanks for the reply.

If you don't know what that means, you might consider taking a course on the basics of VBA.

I'll think about that when the right time comes.

[ChrisGreaves]

Have you declared smURL yet?
If you don't know what that means, you might consider taking a course on the basics of VBA.

"I'll think about that when the right time comes."

Hans, I gotta tell you - I don't watch TV, but at long last I'm beginning to comprehend those individuals who worry if they miss an episode of ...

I just can't wait to see what happens next.

[adam]

I just can't wait to see what happens next.

Luckily I have solved the problem.


By the way, thanks to Hans for considering me to take a course on the basics of VBA.

[adam]

By the way there seems to be something that i couldn't figure out. How to minimize the userform to the taskbar?

[VegasNath]

Adam,

I'm not sure that I understand your approach here.. The minimize / maximize buttons seem pointless. When maximixed, only the outer window maximizes, the page size is set in the userform and does not expand with the maximize command. Also the minimize command minimizes excel entirely as the open webpage always keeps focus over the excel session.

What exactly is it that you are trying to achieve?

If you dont mind me saying so..... Why are you trying to reinvent the wheel? All of these features are available by just opening an ordinary browser window, which you can do from excel if you so wish.

[adam]

I'm not sure that I understand your approach here.. The minimize / maximize buttons seem pointless. When maximixed, only the outer window maximizes, the page size is set in the userform and does not expand with the maximize command. Also the minimize command minimizes excel entirely as the open webpage always keeps focus.

Nathan,

The userform in the workbook uploaded will not get maximized when the maximize button is clicked. But it will get minimized with the excel application when the minimized button is clicked.

What exactly is it that you are trying to achieve?

What I'm trying to achieve is that when the minimized button is clicked, the form to get minimized to the task bar instead of minimizing the whole Excel application.

I hope I have made my question clear.

[adam]

Why are you trying to reinvent the wheel? All of these features are available by just opening an ordinary browser window, which you can do from excel if you so wish.

If you dont mind me asking. Tell me how to??!!

[HansV]

You already know - by using Application.FollowHyperlink.

[VegasNath]

By using a userform, minimizing the userform will automatically minimize excel. I don't see a way around that. I would suggest just opening a normal explorer window.

[HansV]

It *is* possible to minimize the userform without minimizing Excel, but...

By default, the userform is modal, i.e. you can't do anything in Excel until you close the userform. This holds even if the userform is minimized. So miminizing a modal userform is all but useless.

It is possible to make the userform modeless; you can then click in Excel, edit cells etc. while the userform is open. But then there's little or no need to minimize the userform - just move it out of the way.

So I see no point in making the userform minimizable.

And as pointed out by Nathan, it's much easier to open a web page in the user's browser. They can then do with it what they want.

[adam]

The point is that I’m having a database in Excel where customers are insured by a company. If I want to check whether a customer is insured I have to open the company’s website and view the page where I have to enter the customer ID to know whether he is insured.

You already know - by using Application.FollowHyperlink.

I did use the following code

Code: Select all

Sub Google()
     ActiveWorkbook.FollowHyperlink Address:="http://www.google.com", NewWindow:=True
End Sub

But each time I click the macro button the explorer opens the website in a new tab. Which is pretty annoying.

But in this userform method I get directed to the exact page where I have to write the customer ID. In this way the user would not know that he or she is searching the registration of customer ID through the company’s website.

It would feel like he is searching from excel. My purpose of the minimize button is that once a user opens the form with the company’s webpage he or she does not have to open it over and over again. Instead he or she could minimize it for the next customer when he wants to know whether he is insured.

I hope I have made my query clear. I would be happy with any help

Note: the website mentioned in the code is not the actual website.

[adam]

It *is* possible to minimize the userform without minimizing Excel,

It is possible to make the userform modeless; you can then click in Excel, edit cells etc. while the userform is open

I hope I would be helped with this as thhe userform with the web browser is opened to search whether customer is insured or not. After verifying the insurance it will be minimized. It will be again maximize to view the insurance status or another customer.

[HansV]

You don't need to open the website of the insurance company each time. Open it once in your browser, then leave it open. You can easily switch between Excel and the web page using the Windows taskbar or using Alt+Tab.

If you want to look up specific information on a web page, you can do so without opening a visible copy of the web page. But that is far too difficult to tackle here.

[adam]

Thank you Hans. I take your advice & will try to follow.

Meanwhile

It *is* possible to minimize the userform without minimizing Excel

How may I do the above mentioned.