User Form properties

Posted on 2012-09-21
Last Modified: 2012-09-24

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?

Rob H
Question by:Rob Henson
    LVL 44

    Accepted Solution

    Take a look here.
    LVL 31

    Author Comment

    by:Rob Henson
    Hi Martin,

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

    Rob H
    LVL 44

    Expert Comment

    by:Martin Liss
    Can you attach your workbook?
    LVL 31

    Author Comment

    by:Rob Henson
    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.

    Rob H

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now