Solved

Return focus to sheet after displaying modeless userform

Posted on 2013-01-21
26
1,598 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
  • 11
  • 10
  • 4
  • +1
26 Comments
 
LVL 46

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
 
LVL 46

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 46

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 46

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 46

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

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 46

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 46

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 46

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 46

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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

895 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

12 Experts available now in Live!

Get 1:1 Help Now