Activate a spreadsheet while a form is displayed in Excel

[HSMMIT]

Is there any way to use a spreadsheet while a form created in VBA is displayed?

 

[dsi]

You should be able to hide the form (myForm.Hide) and then show the worksheet. You are going to have to consider the criteria for showing the form again. Are you just hiding it for a certain amount of time or are you going to wait for a certain action event to display the form again?

 

[HSMMIT]

Actually I want to keep the form displayed at all times.

 

[dsi]

I am not sure if you can do this. If the form is on top, I don't think that you will be able to activate the sheet for modification.

 

[dsi]

I think that I was mistaken. You should be able to do that using this code. It will force a form to stay on top, but doesn't require it to maintain focus. I am not sure how you want to work this into your form.

Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long

'For SetWindowPos
Const SWP_NOSIZE = &H1
Const SWP_NOMOVE = &H2
Const HWND_TOPMOST = -1
Const HWND_NOTOPMOST = -2

Dim i As Long

'Force Form On Top
i = SetWindowPos(hwnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOSIZE Or SWP_NOMOVE)

'Remove Forced Setting
i = SetWindowPos(hwnd, HWND_NOTOPMOST, 0, 0, 0, 0, SWP_NOSIZE Or SWP_NOMOVE)

Hope this helps!

 

[HSMMIT]

Thanks for your help! I opted for your first suggestion to hide the form. However I will keep the code you sent for keeping the form on top without focus for future reference.

HSMMIT