Return focus to sheet after displaying modeless userform

Hi,

I got some help here to solve a problem using a modeless userform.

This form opens and displays info telling the user to look at the data starting in Column C and correct any errors before continuing. I used it to "pause" the macro to make these corrections.

Now, the userform opens, the cursor is on C2 but when you start typing, you're entering data into the text box of the userform instead of C2

Is there a way to "activate" the sheet so after the userform opens, you start typing into the Cell that is selected without having to click into it?

Lots of google searches indicate this is a common question but I have yet to find a solution that works.

Thanks,

swjtx99
swjtx99Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Martin LissConnect With a Mentor Older than dirtCommented:
You're welcome. One final idea - Put a button on the sheet that says "Amounts Verified" and assign a macro to that button that continues where the first one left off. You could hide/disable that button before the 1st macro starts and unhide/enable it after the MsgBox.
0
 
Martin LissOlder than dirtCommented:
Sheets("sheet name").Range("C2").Select
0
 
Shanan212Commented:
Its a tough one. I have yet to find a solution as well.

A work-around would be to highlight the cells in column C via VBA and unload the userform.

The highlight would be based on conditional formatting such that when a user makes a correct entry, the cell background would turn into white (say from red)
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
swjtx99Author Commented:
Hi Martinliss,

Thanks for the quick reply.

It didn't work. Do I have this in the wrong place?

Sub Edit_Cells()

UserForm1.Show vbModeless
UserForm1.continue_button = False '

UserForm1.TextBox1 = "Correct values in Column C."
UserForm1.CommandButton1.Caption = "Click to Continue" ' describe next action

Sheets("Engines").Range("C2").Select

While UserForm1.continue_button = False
    DoEvents
Wend

While UserForm1.continue_button = False
    DoEvents
Wend

UserForm1.Hide

End Sub

Thanks,

swjtx99
0
 
Martin LissOlder than dirtCommented:
Why do you have

While UserForm1.continue_button = False
    DoEvents
Wend

twice? Why do you have it at all? Do you know how to put a breakpoint on a line? If so put one on the first 'While' line. Is the cell on the sheet selected at that point? (You put a breakpoint on a line by clicking in the left margin)
0
 
Martin LissOlder than dirtCommented:
Forget about what you have now. Can you describe, step by step, what you want to have happen?
0
 
swjtx99Author Commented:
Hi MartinLiss,

I want the form to pop up (it does) and for the active cell to be C2 so when/if you start typing, it's in C2. (currently it is the text box in the userform).

Why twice? Cut/paste mistake. Doesn't seem to hurt anything but I'll delete one :-)

Shanan212, does that mean hiding the form?  It has the "continue" button so I can't hide it.

Thanks,

swjtx99
0
 
Shanan212Commented:
Depends on your userform functioanlity.

- If userform has which data to be corrected, then you can do this:

Have text boxes next to data to be corrected, and whatever correction the user makes will be sent to worksheet. OR

Unload userform and apply conditional formatting to incorrect data (eg: highlight)
0
 
Martin LissOlder than dirtCommented:
What's the purpose of the form? Is it anything other than to show the "Correct values in Column C." message in the textbox?
0
 
swjtx99Author Commented:
Hi Shanan212,

There is usually no problem so the user just hits "continue".

When there is a need to correct, I can't flag it. The message is to have the user verify and correct as necessary based on their personal knowledge, not a criteria I can define in the macro otherwise there would be no need to pause for the verification.

The macro works great at the moment but when a correction is necessary, you can see that the cursor is on C2 so you start typing but what you're typing ends up in the text box where the user message is. At this point, it's just a cosmetic/interface issue, not a show stopper.

Doesn't look like it's possible to show a user form and have a cell on the sheet "active"?

Edit: Hi MartinLiss, the purpose of the form is to pause the macro, tell the user what to do and provide a way to continue after verification (and if necessary, corrections).

Thanks,

swjtx99
0
 
Martin LissOlder than dirtCommented:
Would you be interested in code to add a comment to the cell(s) with the bad data which in Excel 2010 would have a little yellow triangle added? That way you could just display one msgbox saying something "Please correct the errors".
0
 
Shanan212Commented:
try this: when you initiate userform open, change it to

Userform1.Show (vbModeless)

Open in new window


Change userform1 with your user form's name
0
 
swjtx99Author Commented:
Hi MartinLiss,

I don't know what is bad data. this pause is to give the user a chance to review and make determinations.

The data is pricing. Most prices are in the $200 to $400 range so I sort by highest price first and ask the user to verify amounts over $400 are accurate. Sometimes they are, sometimes it's a typo/system error. Just need to make sure amouts in the thousands are verified.

Thanks,

swjtx99
0
 
swjtx99Author Commented:
Hi Shanan212,

Thanks for the suggestion. That is what is is already. See code in post above^

Regards,

swjtx99
0
 
Martin LissOlder than dirtCommented:
I guess I'm not understanding something because to me it seems to me that after you do the sorting you could check to see if there were amounts over 400 and just display a MsgBox which says "Are the amounts over $400 correct?", with perhaps Yes/No/Cancel buttons and only do further processing if the answer was yes.
0
 
Shanan212Commented:
If you want to 'verify' the amounts regardless of the actual amounts present (say you do have amounts over $400 present and you suspect that they might be wrong), then a message box with Yes/No is sufficiant.

You can place a button to load the userform after or when user clicks 'no' to the above msg box.
0
 
Martin LissOlder than dirtCommented:
You could also use conditional formatting to highlight the cells over 400 and display "Please verify the highlighted amounts and click 'Yes' when done".
0
 
swjtx99Author Commented:
Hi Shanan212,

That's an idea but was trying to keep the interface/usability as slick as possible. If I had a msg box and the answer was no (requiring an edit) and then a userform popped up, they would then have 3 clicks (1. msg box Y/N, 2. click on cell to edit, 3. click on userform continue button.)

Hi MartinLiss,

Thanks for the suggestion. If they had to edit a cell would they be able to do that while a message box was displayed? I can't remember but thought that you can't which was why I went to a modeless userform. I'll give that a try.

Thanks for all the suggestions.

swjtx99
0
 
Martin LissOlder than dirtCommented:
No, MsgBox's are Modal, but what I don't understand is what purpose the Userform serves?
0
 
swjtx99Author Commented:
Hi MartinLiss,

I tried the Msg Box but the sheet is locked and can't be edited while the msg box is displayed.

I guess that is why I went to the modeless userform.

I did run across something that sort of worked. It was Application.Userform.Caption added after calling the userform (I think) and it did return control to the sheet but then clicking on the "continue" button on the userform didn't work until you clicked the userform, then clicked the "continue" button which is more confusing that just adding more to the message in the box in the userform which says "CLICK ON THE CELL TO EDIT IT".... :-)

Which is where I'm at now. I then tried to bold that part of the message which proved impossible. Could not figure out how to format text in a text box, geesh!

I think it's safe to say there's no way to do what I'm trying to do?

I appreciate the help and responses.

swjtx99
0
 
Martin LissOlder than dirtCommented:
Please try to explain the purpose of the userform other than displaying a message.
0
 
swjtx99Author Commented:
Hi MartinLiss,

The userform just pauses the Macro after a bit of sorting and calculating to give the user the opportunity to error check (and possibly edit) for the most likely problems (amounts over $400).

Thanks,

swjtx99
0
 
Martin LissOlder than dirtCommented:
Well that doesn't really explain (at least to me) why the userform is necessary. Why can't it go?

Sort
Calculate
MsgBox (which the user reads and closes)
User validates data
0
 
swjtx99Author Commented:
Hi MartinLiss,

After the user validates the data, additional processing is necessary. It's just makes the usability better to pause and have a "continue" button rather than ending sub and having the user go back to "click on Macros Icon, choose the next one in list" etc.

I clicked through a thousand google search results as well as EE searches for "pause macro" and the modeless userform was the only solution I could find. It's really a minor point that the user has to click on the Cell to edit it once the userform is displayed but would have just been slicker if a certain cell could have been "active" after displaying the userform.

I also clicked through another thousand search results for "return focus to sheet from userform" and discovered it is a common question but no one has discovered a solution that works.  

Thanks for taking your time to offer suggestions and advice. I appreciate your help.

swjtx99
0
 
Saqib Husain, SyedEngineerCommented:
Try

    AppActivate Application.Caption
0
 
swjtx99Author Commented:
I don't think this one has a solution. Thanks for trying.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.