Macro operated by button clears worksheet but not executed successfully automatically

[ThiaJay]

I have a macro that runs when I click a button on a worksheet to clear the contents. It works without fault.

Please Login or Register  to view this content.

I want it to also run when the worksheet is opened so that other users don't see the data when they open it up and it's empty, ready for them to use.

So I tried to introduce code into the worksheet.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

It does remove most of the data, but there are still SOME cells in column i and I don't understand why this is?

If I click on the button and run the same code after the worksheet is opened, it clears the remaining cells (as it should). If I test the code in Microsoft Visual Basic for Applications, it runs without fault?

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what the strange behaviour is.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[davsth]

If it is possible, it would also be worth you attaching the file or a desensitised, representative smaller file that exhibits the same behaviour, at the same time as editing your title, you will get a quicker response then. Although no help till as Ali says the title has been changed

[ThiaJay]

Title has been changed, I realise that would make life easier but there's too much sensitive data for me to share the workbook/even just the worksheet names and headings.

[AliGW]

Thanks for changing the title - much better.

Please read the instructions at the top of the page explaining how to create and share a SAMPLE workbook. If members feel they need to see your code in situ, you really need to try to facilitate this.

[romperstomper]

Just in case: an Auto_Open routine has to be in a normal module, not a worksheet code module, or it won't run. I'd also suggest using:

Please Login or Register  to view this content.

[zzzeddy]

Hi

If your Reset1 macro works, why not use

Please Login or Register  to view this content.

..and stick that in a normal module as Rory says.

zeddy

[ThiaJay]

Legendary, thanks. It works!

My concern is that if someone views one of the other worksheets and saves it, then someone else opens it on a different worksheet that it will delete all the data on the other worksheets? How do I avoid that?

[ThiaJay]

Ah, Rory already advised on that?

Please Login or Register  to view this content.

[ThiaJay]

In the same way, except avoiding resetting the data, how do I make all worksheets except Search select A2 to force the view back to the top result on open? Is there a way to do that without specifying the name of each sheet? I don't want it to break if I add more sheets.

[zzzeddy]

Hi ThiaJay

..but your Reset1 macro already specifically refers to Worksheet("Search")
..so you don't need to do the With and End With malarkey.

Unless of course you changed your Reset1 macro to..

Please Login or Register  to view this content.

..in which case you would then do the With etc etc etc

zeddy
Excel Clearance Basement

[ThiaJay]

Sorry, you are right. It's safe!

Please Login or Register  to view this content.

Do you know about the other question I had above?

[zzzeddy]

Hi ThiaJay

You could try this bit of code..

Please Login or Register  to view this content.

zeddy
Excel Window Slider

[zzzeddy]

..so you would then use..

Please Login or Register  to view this content.

zeddy

[ThiaJay]

Thank you so much, it works perfectly of course!

[ThiaJay]

Spoke too soon, if the user switches to another worksheet other than Search and saves it, when it's next opened up it brings up an error.

Run-time error '1004':

Select method of Range class failed.

It's pointing in debug to

Please Login or Register  to view this content.

[Phuocam]

Try this:

Please Login or Register  to view this content.

[ThiaJay]

Thank you, but what about the images that aren't being removed now? You've commented it out and I'm not sure how to make it work.

Please Login or Register  to view this content.

Doesn't work

[zzzeddy]

Hi ThiaJay

You can't 'select' a cell unless you are on that particular sheet - hence the 'Select method of Range class failed' message if you aren't on the Worksheet("Search")

So perhaps we could use this code:

Please Login or Register  to view this content.

Let me know if this works.
If it doesn't, let me know anyway.

zeddy

[ThiaJay]

Thanks zeddy, it seems to be working. I'll keep testing.

[zzzeddy]

Hi ThiaJay

Always a pleasure to be of some service.
Sometimes my help is valuable.
Sometimes it is invaluable.

zeddy

[ThiaJay]

I've given you as much reputation as I can :D

[zzzeddy]

Hi ThiaJay

..nobody has ever done that for me on this Forum before - whopping thanks!

zeddy

[ThiaJay]

You are welcome

Lastly, could you please confirm how I would exclude one other specific worksheet from the search called "Contents"?

[zzzeddy]

Hi ThiaJay

There are lots of different ways to exclude multiple sheets from a particular loop-operation.

For just a couple of sheets, you might just use..

Please Login or Register  to view this content.

Another way, which I like to use, is to get the tab-colour of the sheet (e.g. red, black etc etc) and skip the sheet if say, the tab colour is red etc etc etc

You could also have a named-list of sheets-to-be-ignored, an array-list of sheets-to-be-ignored etc etc etc

I'm sure we could provide more examples if you like.

zeddy
Excel Sheet Laundry Service

[ThiaJay]

Hi ThiaJay

There are lots of different ways to exclude multiple sheets from a particular loop-operation.

For just a couple of sheets, you might just use..

Please Login or Register  to view this content.

Another way, which I like to use, is to get the tab-colour of the sheet (e.g. red, black etc etc) and skip the sheet if say, the tab colour is red etc etc etc

You could also have a named-list of sheets-to-be-ignored, an array-list of sheets-to-be-ignored etc etc etc

I'm sure we could provide more examples if you like.

zeddy
Excel Sheet Laundry Service

Thanks Andy, I'm thinking this will work.

Please Login or Register  to view this content.

It's good to know about the other ways though, I didn't know you could do it based on tab colours. That could help me in the future.

[ThiaJay]

Ah, it didn't work as expected. I think this code needs to go within the search, as it's bring up content from the content page at the moment.

[ThiaJay]

Not sure how to integrate that into

Please Login or Register  to view this content.

[ThiaJay]

Could you please get back to me?

[zzzeddy]

Hi

Are you waiting for Andy???

Is it possible to attach a sample file - it would help a lot
Dummy data is OK

zeddy

[ThiaJay]

Thanks for your reply, not particularly.

I've attached a dummy workbook.

[zzzeddy]

Hi ThiaJay

In the post#26, (quote : Thanks Andy, I'm thinking this will work) your Sub MoveSelect() is missing an 'End If'
You need to have and 'End If' for every 'If' you use, i.e. these need to be in 'pairs' for each worksheet you want to exclude.

I made the change in the attached file, and it seems to work OK

zeddy

[ThiaJay]

Thank you very much.