1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Pause Macro for Input into various cells

Discussion in 'Archive: Business Applications' started by mariaa33, Oct 10, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    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.
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
  3. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    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.
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
  5. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    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.
     
  6. slurpee55

    slurpee55

    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.
     
  7. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    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.
     
  8. slurpee55

    slurpee55

    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....
     
  9. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    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
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    It sounds like this should actually be in Access, an Access Query would show empty Cells and allow data entry.
     
  11. slurpee55

    slurpee55

    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.
     
  12. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    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
     
  13. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    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.
     
  14. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    Unfortunately, my company has blocked that website. They even block google groups.:(
     
  15. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    156
    Wait, I know somewhere I can go. Be there in a min
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/757855