Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Archive: Business Applications Archive: Business Applications
Search Search
Search for:
Tech Support Guy > > > >

Solved: Excel Pause Macro for Input into various cells


(!)

mariaa33's Avatar
mariaa33 mariaa33 is offline
Member with 149 posts.
THREAD STARTER
 
Join Date: Aug 2008
Experience: Beginner+
10-Oct-2008, 10:58 AM #1
Solved: Excel Pause Macro for Input into various cells
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.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
10-Oct-2008, 11:20 AM #2
One of the simpler ways is to add this pause routine to the macro
http://www.mrexcel.com/archive/VBA/869.html
mariaa33's Avatar
mariaa33 mariaa33 is offline
Member with 149 posts.
THREAD STARTER
 
Join Date: Aug 2008
Experience: Beginner+
10-Oct-2008, 11:23 AM #3
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.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
10-Oct-2008, 11:26 AM #4
Then I would suggest you use an Input Box.
Here is a good description on how to use them
http://www.ozgrid.com/VBA/inputbox.htm
mariaa33's Avatar
mariaa33 mariaa33 is offline
Member with 149 posts.
THREAD STARTER
 
Join Date: Aug 2008
Experience: Beginner+
10-Oct-2008, 11:31 AM #5
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.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
10-Oct-2008, 11:47 AM #6
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's Avatar
mariaa33 mariaa33 is offline
Member with 149 posts.
THREAD STARTER
 
Join Date: Aug 2008
Experience: Beginner+
10-Oct-2008, 11:56 AM #7
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.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
10-Oct-2008, 12:03 PM #8
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's Avatar
mariaa33 mariaa33 is offline
Member with 149 posts.
THREAD STARTER
 
Join Date: Aug 2008
Experience: Beginner+
10-Oct-2008, 12:05 PM #9
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,617 posts.
 
Join Date: Mar 2005
Location: UK
10-Oct-2008, 12:36 PM #10
It sounds like this should actually be in Access, an Access Query would show empty Cells and allow data entry.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
10-Oct-2008, 12:36 PM #11
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.
Rollin_Again's Avatar
Member with 4,698 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
10-Oct-2008, 01:34 PM #12
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

Last edited by Rollin_Again; 10-Oct-2008 at 01:40 PM..
mariaa33's Avatar
mariaa33 mariaa33 is offline
Member with 149 posts.
THREAD STARTER
 
Join Date: Aug 2008
Experience: Beginner+
10-Oct-2008, 01:38 PM #13
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's Avatar
mariaa33 mariaa33 is offline
Member with 149 posts.
THREAD STARTER
 
Join Date: Aug 2008
Experience: Beginner+
10-Oct-2008, 01:55 PM #14
Unfortunately, my company has blocked that website. They even block google groups.
mariaa33's Avatar
mariaa33 mariaa33 is offline
Member with 149 posts.
THREAD STARTER
 
Join Date: Aug 2008
Experience: Beginner+
10-Oct-2008, 01:55 PM #15
Wait, I know somewhere I can go. Be there in a min
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑