What about putting a drop down list of the users' names in cell A1 (via Data Validation) and putting the references starting from A2 ?
As well as disabling Delete Sheet on the Edit menu, to disable the worksheet tab pop-up menu :-
Private Sub Worksheet_Activate()
CommandBars("Ply").Enabled = False
Private Sub Worksheet_Deactivate()
CommandBars("Ply").Enabled = True
You are correct that the PrintPreview Method in the Help file does not mention any argument.
However, a search for PrintPreview produces only 3 topics :-
PrintPreview Method Example
Methods with New Arguments
The 3rd topic states that PrintPreview has an argument called EnableChanges
I took no offense to anything posted in this thread.
Perhaps my last post could have been a bit more delicate - I tend to be a bit abrupt.
The answer to the question posed in this thread is really not so difficult to find.
A search for "PrintPreview" in VBE Help shows that the PrintPreview method has one argument called "EnableChanges".
It doesn't take a great deal of logical deduction to set the argument to False, and then run it to see what it does.
Originally posted by XL Dave
I don't think that will help Pete as I believe he wants to be able to stop the "setup" from being accessible in Print Preview. If he wanted to disable the Print Preview option from the File menu he could use
Application.CommandBars("worksheet menu bar").Controls _
("&file").Controls("&Print Preview").Enabled = False
I cannot find a way to prevent access to the "setup" button in Print Preview - sorry Pete!
Perhaps one of the VBA experts have some ideas?
Why don't you actually try my suggestion, rather than just dismissing it because you think you know what it does?
It does not disable Print Preview.
It opens Print Preview with SetUp and Margins disabled.
"It occurs to me that the For..Next and Find approaches could be improved by using Union for the found ranges and then just one delete of the combined range at the end of the routine. Not sure how much of an impact this would have, just thinking out loud. Perhaps I'll test for that too."
Yes, of course using Union would probably be an improvement (depending upon the make-up of the data), but I don't suggest you spend time checking.
The For...Next is a no-no, plus Excel does not delete a non-contiguous range (whether set by Union or whatever) in one step - it loops through the range one area at a time and deletes each area one at a time.
I have pointed this out previously :- “… suffers from the usual problems of looping – that is, either deleting one row at a time or if the Union method is used, deleting each non-contiguous area one at a time”
Find suffers from the same problems (but of course is much better than For...Next).
"Have you tested on various sorts of data? For example, is the formula approach better with numeric data or text? Or is it just always better"
Can't think of any reason why the type of data (numeric or text) would make a difference. The formula is merely looking for a particular value. Am only theorising - would be nice if you could demonstrate that it could make a material difference.
“I took your suggestion of alternate rows filled with "A" and then "B" and copied down, then looked for a randomly placed text string. (I also looked for a number within text and numbers within numbers (alternate rows of "234" and "567").”
No, this was not what I did.
(Do you mean that you were looking only for 2 rows in every 567 – i.e. 35 out 10000 ?
Also, I don’t follow what you mean by “10 search values” and “100 search values”)
What I did was to fill all rows alternatively with “A” & “B”, then to identify and delete all rows containing “A” - that is, every other row – the “worst case”.
With the Formula/Sort method, the run-times are affected very little by the number of non-contiguous areas to be deleted – the difference between having to identify and delete one row or having to identify and delete 5000 non-contiguous rows is very little.
For the Filter method, the difference is substantial
Also, with the worst case, try running the Filter/SpecialCells method on 17000 total rows (that is, having to delete every other row).
It won’t work since the SpecialCells method fails for this number of cells when Excel needs to do some kind of calculation.
I previously said that the Formula/Sort method would also fail, but in fact it will work for any number of total rows and any number of rows for deletion (up to the worksheet’s total rows, of course).
Also, I haven't tested it but know that the Find method will also take substantially longer for the worst case.
Thanks for your file, but I think the basis used for your testing is not appropriate (and too complex).
How many tests of random data would you consider to be necessary before a reliable conclusion could be reached?
We know what increases run-times, so why not just test based on worst case scenarios?
In my last post, I don’t think the run times based on the scenarios set out lend themselves to a lot of different conclusions or interpretations.
The difference between run-times of 1 second versus 2 minutes and 58 seconds is not small.
To check the run times of the Filter macro versus the Formula/Sort macro, I copied the OP’s data down to row 18,011 and recorded the run times with :-
I’m using Excel 2000 and the run-times were :-
Filter : 43 seconds
Formula/Sort : 2 seconds
Times, of course depend upon the make-up and the size of the data.
The run-times for the Filter method will depend upon how many non-contiguous areas there are in the range to be deleted.
With the Formula/Sort method, there is only one area to be deleted.
Also, deleting areas near the top of a list takes longer than deleting areas near the bottom.
With the Formula/Sort method the area to be deleted is at the end.
Both methods will fail completely if the SpecialCells range consists of more than 16,000 (approx) rows (perhaps this is not so in later versions of Excel?).
So the Filter macro will fail when the total rows of data exceed the limit, whereas the Formula/Sort macro can handle larger data and will fail only when the rows to be deleted exceed the limit.
If the limit is likely to be exceeded, then the Find method would appear to be the obvious alternative. But, the Find method suffers from the usual problems of looping – that is, either deleting one row at a time or if the Union method is used, deleting each non-contiguous area one at a time.
However, with large amounts of data that exceed the SpecialCells limit, there may be better ways than Find.
For example :-
Identify the rows to be deleted via a formula
Sort the data to put the rows to be deleted at the bottom
Via Find (or via a formula), identify the first row of the range to be deleted and set the range (i.e SpecialCells method not necessary)
Delete the range
I did two more tests on the two methods.
I set up what should be a worst case scenario for the Filter method (and for the Find method) :-
In cells G1:P1 entered “A”
In cells G2:P2 entered “B”
For the first test the data was filled down to G10000:P10000 and down to G15000:P15000 for the second test.
The results for deleting rows that contained “A” in column I were :-
Filter : 1 minute 15 seconds
Formula/Sort : less than 1 second
Filter : 2 minutes 58 seconds
Formula/Sort : 1 second
What was your code using Find?