Solved

Return focus to sheet after displaying modeless userform

Posted on 2013-01-21
26
1,561 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 45

Expert Comment

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

Expert Comment

by:Shanan212
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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 45

Expert Comment

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

Author Comment

by:swjtx99
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi Shanan212,

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

Regards,

swjtx99
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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 45

Expert Comment

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

Author Comment

by:swjtx99
Comment Utility
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 45

Expert Comment

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

Author Comment

by:swjtx99
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
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
Comment Utility
Try

    AppActivate Application.Caption
0
 

Author Closing Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

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,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 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

17 Experts available now in Live!

Get 1:1 Help Now