Solved

Return focus to sheet after displaying modeless userform

Posted on 2013-01-21
26
1,830 Views
Last Modified: 2013-02-17
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
0
Comment
Question by:swjtx99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
  • 4
  • +1
26 Comments
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38801936
Sheets("sheet name").Range("C2").Select
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38801970
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
 

Author Comment

by:swjtx99
ID: 38801984
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 48

Expert Comment

by:Martin Liss
ID: 38802018
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38802245
Forget about what you have now. Can you describe, step by step, what you want to have happen?
0
 

Author Comment

by:swjtx99
ID: 38802467
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
 
LVL 13

Expert Comment

by:Shanan212
ID: 38802521
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38802673
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
 

Author Comment

by:swjtx99
ID: 38802702
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38802929
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
 
LVL 13

Expert Comment

by:Shanan212
ID: 38803002
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
 

Author Comment

by:swjtx99
ID: 38803226
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
 

Author Comment

by:swjtx99
ID: 38803245
Hi Shanan212,

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

Regards,

swjtx99
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38803260
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
 
LVL 13

Expert Comment

by:Shanan212
ID: 38803286
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38803299
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
 

Author Comment

by:swjtx99
ID: 38804049
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38804054
No, MsgBox's are Modal, but what I don't understand is what purpose the Userform serves?
0
 

Author Comment

by:swjtx99
ID: 38804099
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38804103
Please try to explain the purpose of the userform other than displaying a message.
0
 

Author Comment

by:swjtx99
ID: 38804123
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38804142
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
 

Author Comment

by:swjtx99
ID: 38805509
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
 
LVL 48

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38805566
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38822775
Try

    AppActivate Application.Caption
0
 

Author Closing Comment

by:swjtx99
ID: 38900220
I don't think this one has a solution. Thanks for trying.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

626 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