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

[MattGreer]

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:

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

 

[MattGreer]

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/excel.application.inputbox

Hope this helps someone in the future!

Thanks!!


Matt

 

[Andrzejek]

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

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

 

[MattGreer]

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

 

[Andrzejek]

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

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

 

[MattGreer]

Excellent points, thank you Andy!

Thanks!!


Matt