?
Solved

User Form properties

Posted on 2012-09-21
4
Medium Priority
?
325 Views
Last Modified: 2012-09-24
All,

I have a file with a simple User Form with a Single button for running a routine. The routine can be triggered from either of two input sheets.

I have chosen to use a User Form and have it set with ShowModal = False so that it is visible on all sheets where required with various .Show and .Hide commands on the sheets where required or not required.

I have used a form rather than a button so that as the user navigates down the sheet populating the necessary fields they can then click on the button in the form to process the data rather than having to scroll back up the screen to click the button. With one sheet I could freeze panes and put the button above the freeze line but this is not an option on the other sheet.

This all seems to be working fine except when the process is run (triggered by the button on the form) the necessary update is done and the cursor goes to the required sheet but the UserForm is still active. I have to click into the sheet to get the sheet active rather than the form. Might seem a simple thing but when the user has clicked the form to do the update they will often then just use the direction arrows to move down the sheet to check it. They often do this on my machine and I have a trackball that they don't like so prefer to use the keyboard.

So question is: How can I deactivate the UserForm without hiding it or is there a setting in the UserForm properties that I need to set? I have set ShowModal to false so that I can leave the Form shown and I ahve tried the Enable property but if I set that to false the button then doesn't work.

Any suggestions?

Thanks
Rob H
0
Comment
Question by:Rob Henson
  • 2
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
Martin Liss earned 1000 total points
ID: 38422032
Take a look here.
0
 
LVL 34

Author Comment

by:Rob Henson
ID: 38429240
Hi Martin,

Trying the link but don't seem to be able to get it to work. Is it compatible with xl2003?

Thanks
Rob H
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38429253
Can you attach your workbook?
0
 
LVL 34

Author Comment

by:Rob Henson
ID: 38429577
Unfortunately not, not without a lot of scrubbing!!

However, I have managed to get a workaround. I was having problems with Chip's code and ended up having to minimize and maximize the window each time I ran the code so it led me to thinking what happens if I just minimize and re-maximize. Lo and behold, that works to deactivate the form.

Thanks for the link. It at least pointed me in the right direction.

Regards
Rob H
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question