VBA Userform Minimize problem

[Sharad Singh]

Hi All,

I have added the minimize button in VBA User form through API function. Now the problem is that whenever we start\open or switch to new excel work book the user form automatically popup and all the users have to again minimize it.

The code which I used for minimize:

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

Private Declare Function GetWindowLongA Lib "USER32" _
(ByVal hWnd As Long, _
ByVal nIndex As Long) As Long

Private Declare Function SetWindowLongA Lib "USER32" _
(ByVal hWnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long

Option Explicit

Sub FormatUserForm(UserFormCaption As String)

Dim hWnd As Long
Dim exLong As Long

hWnd = FindWindowA(vbNullString, UserFormCaption)
exLong = GetWindowLongA(hWnd, -16)
If (exLong And &H20000) = 0 Then
SetWindowLongA hWnd, -16, exLong Or &H20000
Else
End If

End Sub
</pre>
Thanks
Sharad Singh

 

[phxsportz]

Why not just HIDE the userform ??

 

[Sharad Singh]

Actually this application is used by 100 users for capturing the time. These all users also works on other excel files as well.
If I hide the form on minimize how will they recall it?
how we can add the recall button in tool bar ??

 

[phxsportz]

Simple .. After you originally load it run

[I]Your Userform Name[/I].HIDE

[I]Your Userform Name[/I].SHOW

You can tie it to a shortcut key, or put it on the menu.. or trigger by an event..

Like

Private Sub Workbook_[B]BeforeClose[/B](Cancel as Boolean)
[I]      Your Userform Name[/I].SHOW   
End Sub

 

[Sharad Singh]

Hi,

Thanks a lot .

But I am not sure that how can we tie it with shortcut key?
Or If I want to add the same in Standard tool bar as button for show and hide option for Form...
can you help me on this??

 

[phxsportz]

This should get you where you want to be Cheers

http://excel.tips.net/Pages/T002926_Adding_a_Macro_to_a_Toolbar.html

 

[Sharad Singh]

Thanks a lot