×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Activate a spreadsheet while a form is displayed in Excel

Activate a spreadsheet while a form is displayed in Excel

Activate a spreadsheet while a form is displayed in Excel

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

RE: Activate a spreadsheet while a form is displayed in Excel

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?

RE: Activate a spreadsheet while a form is displayed in Excel

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

RE: Activate a spreadsheet while a form is displayed in Excel

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.

RE: Activate a spreadsheet while a form is displayed in Excel

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!

RE: Activate a spreadsheet while a form is displayed in Excel

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close