1

All:

Thank you in advance, you all have been a tremendous resource!!!

I have a couple of spreadsheets where the sheet is protected, but users can still use filters. I'm processing most of the sheets automatically, but what I need to do, is present the user with the sheets that need to be filtered, then have them select a "Finish" type button or toolbar entry, which I already have.

What I need to be able to do, is to bring this sheet up, pause the macro, if possible, while they make their changes (could be up to 5 filters that they select before the sheet is ready.

Then, copy the visible cells only to a specific sheet and then resume the macro.

I don't think that Worksheet change event will do this.

I'm thinking more on the lines of maybe setting a flag on a spare sheet, firing up the next macro and then see if it can find the original macro and pick up where it is flagged?

I thought about a modeless userform that the user could click OK on and then call the next macro, but that does not work.

The calling code is: 

UserForm3.Show
CopyToDisplay "AEP"
LastPos = LastPos + 1

Where AEP is the sheet name to copy the filtered rows from.

Userform displays, but clicking ok does nothing and of course, the macro keeps on going.

Any suggestions would be greatly appreciated!

Thanks,

Jeff

pnuts
  • 58,317
  • 11
  • 87
  • 139
Jeff Hyman
  • 43
  • 1
  • 6
  • 1
    You've asked a number of questions, many of which have been answered (and some apparently satisfactorily) but you have not accepted anyone's answer. Read [THIS](http://stackoverflow.com/help/someone-answers) about what to do when someone answers your question, and also [THIS](http://stackoverflow.com/help/accepted-answer) about what it means to accept an answer. I'm not saying that you *must* accept an answer for every Q you ask, but that is the way that we show gratitude around here -- especially for answers that solve our problems. – David Zemens Sep 23 '14 at 19:50
  • 1
    Further: you have asked enough Q's to know that you should probably be showing some code. Realistically you probably won't be doing this in a single procedure, but you will have one procedure that is invoked to show the worksheet, and that will end. Then when the user is ready to "continue", theyw ill press the button/etc. to trigger the next procedure. – David Zemens Sep 23 '14 at 19:51
  • Did not realize that. I will go back and do that as I have accepted a number of answers. As for code, I don't have any to show yet. I'm asking if it is even possible to do this. I can post the code for the automated sheets, but that won't do any good here as they are fine. I'm just trying to find out if this is possible and if so, a lead on how to do it. That's why I suggested the flag method. – Jeff Hyman Sep 23 '14 at 20:16
  • OK, well I would suggest getting rid of the idea of "pausing" the macro, and just compartementalize it like I mention above. Have one macro to display the sheets/etc. This macro *ends* when the sheets are displayed, allowing the user to manipulate the filters, etc. Then, they will need to press a button or otherwise invoke the second macro which will "finish" the rest of the job :) – David Zemens Sep 23 '14 at 20:25
  • Otherwise it's possible to effectively "pause" the procedure for user input (see [here](http://answers.microsoft.com/en-us/office/forum/office_2010-customize/vba-script-to-pause-macro-allow-editing-then/53027408-ed41-4300-b42e-ed2fc20b8e9c) for one method that would be simple to implement. – David Zemens Sep 23 '14 at 20:27
  • Hi David: Pretty much what I thought. I tried the link you sent me and tested it, but no matter what I do, I can't close the userform to break the loop.... Not sure what I'm doing wrong, but I understand what it's trying to do. oh well... compartmentalization it is... Thanks!!! Jeff – Jeff Hyman Sep 23 '14 at 20:38
  • Show your code and maybe we can help ;) – David Zemens Sep 23 '14 at 20:41
  • David: Just thought of another problem... I'm using a set of Globals...should I save them to a hidden sheet and reload them upon the resumption? – Jeff Hyman Sep 23 '14 at 20:42
  • Shouldn't need to do that. Although I try to avoid globals when possible... I'm in the car now but will try to get back to you later this evening. – David Zemens Sep 23 '14 at 20:44
  • Not a problem.. I hate using globals as well, but absolutely necessary on this project. Talk to you later and thank you for your advice and assistance! Jeff – Jeff Hyman Sep 23 '14 at 21:05

1 Answers1

2

Jeff let's try this. Your current code:

UserForm3.Show
CopyToDisplay "AEP"
LastPos = LastPos + 1

When we display a UserForm, the default behavior is vbModal, which essentially freezes the application and the user cannot interact with anything but the UserForm, that is not what you want. What you need is a way to display the form, and then just wait for the user to signal that s/he is finished with the input.

So we need to modify a few things:

The UserForm needs to effectively "pause" while also allowing the user to interact with the worksheet. A vbModal form can't do this (it pauses, without interaction), and really neither can a vbModeless (it continues execution AND allows interaction).

Conundrum? No. we can simulate a pause with the vbModeless form, and preserve the user's ability to interact with the sheet. The best of both worlds!!

We will show the form with the optional vbModeless, this allows the user to interact with the rest of the Application /worksheets/etc. Since a modeless form continues code execution, we need to simulate a pause and we can do this with a Loop. The loop will run indefinitely, and only break once the UserForm is closed, at which point the rest of the code will continue to execute.

UserForm3.Show vbModeless 

Do While UserForm3.Visible
    DoEvents
Loop

LastPos = LastPos + 1

'You MAY need to reset some variables, if the Filter/Autofilter has affected these/etc.

Design-wise, give your form a single Label control and set its .Caption property (and the form's .Caption property) in some useful/instructive way. You could add a command button but that seems unnecessary, since all the button would do is invoke the Terminate event (which can always be done with the red "X")

enter image description here

For your issue with copying (apparent failure to paste), try changing this:

Sheets("AEP").Select  

With ActiveSheet      
    .UsedRange.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("Display").range("A" & LastPos)     
    .AutoFilterMode = False     
    Application.CutCopyMode = False  
End With

To this:

With ActiveSheet      
    .UsedRange.SpecialCells(xlCellTypeVisible).Copy 
     Sheets("Display").range("A" & LastPos).PasteSpecial
    .AutoFilterMode = False     
    Application.CutCopyMode = False  
End With
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • David: That's gorgeous.... I had failed with modeless for the exact reason above.... Let me try this and get back to you!! – Jeff Hyman Sep 24 '14 at 13:15
  • David, seems to work, initially, but I cannot close the UserForm by either the 'x' or the cmd button that just has the command **unload userform3** . Do you have any idea what the issue would be? – Jeff Hyman Sep 24 '14 at 13:21
  • That can't *possibly* be happening, unless you have made a `UserForm_QueryClose` event handler and some reason disabled the form's close event. I am pretty sure you didn't do that, though. Are you sure you do not have an active Breakpoint set in your code? That would prevent *any* interaction with the form, if the code is in break mode. – David Zemens Sep 24 '14 at 13:47
  • David: Ignore the previous... Damn debug mode... I've added that code in, works find, and when I resume, trying to do a simple copy of the visible cells, but the sub just bombs out and returns to calling routine.`Sheets("AEP").Select UserForm3.Show Modeless Do While UserForm3.Visible DoEvents Loop LastRow = range("A65536").End(xlUp).Row LastPos = LastRow + 1 With ActiveSheet .UsedRange.SpecialCells(xlCellTypeVisible).Copy _ Destination:=Sheets("Display").range("A" & LastPos) .AutoFilterMode = False Application.CutCopyMode = False End With` – Jeff Hyman Sep 24 '14 at 14:04
  • oooh that's bizarre, no errors, it just fails to paste, right? – David Zemens Sep 24 '14 at 14:19
  • That did it! I'm in the green now!!! Thank you David for your assistance. I really appreciate it! – Jeff Hyman Sep 24 '14 at 16:08
  • David: Quick question... Why does Excel just bounce out of a sub without any error message whatsoever? I have a piece of code `Worksheets("Display").range("D" & LastPos & ":" & "AM" & LastPos).Select ` .... excel hits it and even though the workbook is open to this page, it just bounces out. Any ideas? – Jeff Hyman Sep 24 '14 at 19:50
  • I honestly don't think I've ever seen that happen before. It is strange indeed. The earlier case was the first I have seen it, and I know ther are some funky things about the `UsedRange` method, so I figured that was the culprit *somehow*. This particular case I am not able to replicate but you might be able to avoid it with this: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10717999#10717999 It's generally not recommended to use `Select` or `Activate` methods, nor to rely on selections or active cell references, etc. – David Zemens Sep 24 '14 at 20:20
  • I would suggest trying to replicate the similar error in a new/empty workbook, with the minimum amount of code required to produce the error. Then post that as a *new* question. If nothing else, at least some other people will see it and may have additional insight as to what the problem/bug/error might be. – David Zemens Sep 24 '14 at 20:22
  • Excellent reference. Thank you! I've pretty much removed all selects and activates and gone with variables or explicit definition. Thanks again! – Jeff Hyman Sep 25 '14 at 13:02