Excel VBA Running a form in the background?

I am not sure the easiest way to say this... I created a form, with a timer (count up, count down) on it and it displays the time on the form. However, the form is ALWAYS on top of the excel spreadsheets - meaning I can use the form and the form only.

I would like to be able to ahve the form running, but also be able to use Excel as normal - how do I go about this?

Hi Josh,

meaning I can use the form and the form only.

I think what you want to do is show the userform as modeless, ie:

UserForm1.Show vbModeless


HTH
Colin

In case the modeless form is still too "in the way" for your taste, you'd have to look into a way of giving the user the means to toggle the form actively, e.g. by running a macro to show it and providing a "hide" button on it.

That worked good thanks. I also added this to create a minimize button, works great:

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

Private Sub UserForm_Initialize()
'Used to create a minimize button on the form
Call FormatUserForm(Me.Caption)
End Sub
Sub FormatUserForm(UserFormCaption As String)
'Used to create a minimize button on the form
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
Sub ShowForm()
'Used to create a minimize button on the form
UserForm1.Show
End Sub

I don't think there's a direct native way, since the worksheet's and workbook's Activate methods don't appear to be sufficient to return focus to Excel. So, you have two approaches as far as I can see.

Either, you could use an Excel object that does have a SetFocus method. The object browser shows that the only available ones are the CommandBarControls. I haven't tested this, but calling an arbitrary control's SetFocus should result in returning focus to the Excel window with which it is associated.

If that doesn't work, or you don't like it, you can use API to show the form without it ever gaining focus in the first place. Have a look at the code sample I just provided in post #17 in this thread (http://www.xtremevbtalk.com/showthread.php?t=298075), it's the exact same issue, just between two forms - but windows are windows, so it makes no difference. If you haven't used API before, it might look a little cryptic. If so, let me know and I'll extract the relevant part and explain it better. :)