×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel 365 VBA - Prompt the user to take some action, then wait?

Excel 365 VBA - Prompt the user to take some action, then wait?

Excel 365 VBA - Prompt the user to take some action, then wait?

(OP)
I think the tl;dr would be, how can I have Excel prompt me to take some action and then wait until I've completed the action?

I'm writing a subroutine to re-sort columns in a table. I would like to be able to set it up such that the user (me) starts the macro and is prompted to click on a cell in the Source column, and then click on a cell in the Destination column, and then the subroutine moves the Source to the left of the Destination column.

I can move the columns around just fine. What I don't know how to do is start a macro and have a message box pop up and tell the user to select a cell. The message box always takes over control of the GUI such that I can't click in Excel anywhere. After reading the documentation at MS I tried this, knowing better, and as I suspected changing the modality of the message box didn't allow me to select a cell in Excel:

CODE --> VBA

Sub TestModal()
    MsgBox "Message box vbApplicaitonModal", vbOKOnly + vbApplicationModal
    MsgBox "Message box vbSystemModal", vbOKOnly + vbSystemModal
End Sub 

Looking at the docs for an InputBox, seems like that wouldn't work either.

Thanks!!


Matt

RE: Excel 365 VBA - Prompt the user to take some action, then wait?

(OP)
And, like always, right after I post and admit what I don't know, I figure out the answer... meh

Application.InputBox will allow me to pop up a message/instruction, have the user do something in excel, and return a value to the InputBox.

Documentation: https://learn.microsoft.com/en-us/office/vba/api/e...

Hope this helps someone in the future!

Thanks!!


Matt

RE: Excel 365 VBA - Prompt the user to take some action, then wait?

One way... Just step thru this code line-by-line (F8)

CODE

Option Explicit

Sub AskUserToDoStuff()

Call MsgBox("I need you to do stuff.  Look at the bottom Status bar for hints", _
    vbOKOnly + vbInformation)

Application.StatusBar = "Select cell in column"

Application.StatusBar = "Select another cell in other column"

Application.StatusBar = "Thank you"
Application.StatusBar = ""

End Sub 

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel 365 VBA - Prompt the user to take some action, then wait?

(OP)
I thought about using the Status Bar but it's a little too "hidden" for this for me. I can't speak for others but I don't look at the StatusBar that often although the MsgBox would prompt me to do so.

I just use the Status Bar for messages for longer routines that have many automated steps or loops. Good suggestion nonetheless. :) I like this InputBox method because you can see a result when you click on the cell; the address is entered in the field, so that's nice feedback that yep, you clicked on something, heh.

Thanks!!


Matt

RE: Excel 365 VBA - Prompt the user to take some action, then wait?

InputBox sounds like a good idea, assuming you provide the correct input.
Just because you say: "Enter a number: " you still need to validate if the value entered is actually a number. sad

Quote (Matt)

I like this InputBox method because you can see a result when you click on the cell; the address is entered in the field, so that's nice feedback that yep, you clicked on something, heh.

That's nice, but the user may NOT click on a cell, they may just enter something in the box that may or may not be a valid address of a cell.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel 365 VBA - Prompt the user to take some action, then wait?

(OP)
Excellent points, thank you Andy!

Thanks!!


Matt

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close