Solved: Excel Pause Macro for Input into various cells

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

mariaa33

Thread Starter
Joined
Aug 7, 2008
Messages
164
I am trying to find the best way to pause a macro, allow the user to edit the spreadsheet in various cells and then resume the macro. I saw on one site about modeless msgbox but could not find information on how to use it. Any suggestions would be greatly appreciated.
 

mariaa33

Thread Starter
Joined
Aug 7, 2008
Messages
164
That would not work because it would take X minutes to make the changes. The number of minutes would vary and I would not want to put in too long of a wait or too short of a wait.
 

mariaa33

Thread Starter
Joined
Aug 7, 2008
Messages
164
Already tried that too but it does not allow the user to go to the actual spreadsheet and enter the data as there will be different cells that need to be entered each time it is used. It needs to just pause the macro and allow the user to enter data whereever it is need and when finished the user click "ok" to continue with the macro.
 
Joined
Oct 20, 2004
Messages
7,837
What does your macro do? I am trying to understand why the user can't make the changes in advance, or have formulas recalculate the values.
If you could post a copy of your spreadsheet (Use Go Advanced - there is an option that you will see then for adding files) then perhaps we could help more.
 

mariaa33

Thread Starter
Joined
Aug 7, 2008
Messages
164
The spreadsheet is very large and after other formatting, it sorts the data so that the rows with the empty cells are at the top. The cells that are empty require the user to look up data in another application and then enter it into the spreadsheet. It could be anywhere from 1 to 30 different cells that manual entry is needed. After they complete this manual entry I want to continue with the macro.
 
Joined
Oct 20, 2004
Messages
7,837
Why not just split it into two macros - one that stops after sorting and another that does the rest?
You could assign two buttons that would run one macro each....
 

mariaa33

Thread Starter
Joined
Aug 7, 2008
Messages
164
That is the way I had it to start with but I was trying to keep the user from having to run several macros. Ok, that may be my only choice. I was looking into using a userform with an ok button. thanks
 

OBP

Joined
Mar 8, 2005
Messages
19,895
It sounds like this should actually be in Access, an Access Query would show empty Cells and allow data entry.
 
Joined
Oct 20, 2004
Messages
7,837
I asked one of the best coders on here about this and he said that you could do it in BASIC but not in VBA. (I would guess you could program in something else also, but....)
An idea I thought of - if there is a cell that they always must enter data into, and it is the last one that they enter data into (or click into) - you could even assign a blank cell to this function; have them type in "ok" or something - but when that cell loses focus, you could auto start the second macro.
 
Joined
Sep 4, 2003
Messages
4,912
Create a modeless form with nothing but an OK button on it. When the user runs the macro the code will execute up to the point where the user input is required and then the form will be displayed. Since the form is modeless you can move it and manually enter data into the workbook. When the user clicks the OK button the form should be hidden and the remainder of your macro code should be executed in the button_click event. To make the form modeless just open the form in the VB editor and set the ShowModal property to False. Let me know if you want me to post an example.


Regards,
Rollin
 

mariaa33

Thread Starter
Joined
Aug 7, 2008
Messages
164
How would I code that? I have never worked with modeless forms. I have tried to figure it out with some examples on the net but no success.
 

mariaa33

Thread Starter
Joined
Aug 7, 2008
Messages
164
Unfortunately, my company has blocked that website. They even block google groups.:(
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top