Message Boxes in Reset Macro

Hi All,

I have a reset macro here, that is working great. I only have one problem with it and i cannot seem to find a solution. My problem is that when i run this reset macro it is clearing all the contents that the user has inputed into this workbook, it is displaying the message boxes that are on the other pages of my workbook. On the pages "Loss Development Factors" and "Claim Count - Credibility" i have message boxes that pop up when the user opens the page that tell the user to only paste values on this sheet. when my reset macro is running for some reason it is popping those boxes up and i have to click okay a couple of times to keep the macro going. I think there may be something i have to do on the other pages but here is my reset macro along with one of the pages that shows a message box. The other page is exactly the same. I am a VBA newbie so if you could please explain a solution in pretty good detail so i can understand it. Thanks in advance for all of the help.

Here is the reset macro

Please Login or Register  to view this content.

Here is the pop up box that is on both of the pages listed above

Please Login or Register  to view this content.

In your reset macro you have several select statements. These will activate the sheet you're working with, and that in turn will call the worksheet_active event, running the other code.

Either:

a) Remove the select statements from the reset macro, or

b) Add the lines:

Please Login or Register  to view this content.

And

Please Login or Register  to view this content.

To the reset macro - they can go immediately after the two application.screenupdating lines.

Now it is giving me an error on the .Select line

Originally Posted by JosephP

this looks kinda like this question: http://www.excelforum.com/excel-prog...-updating.html
what's different?

It is pretty similar, but i am trying to go about the project a little differently

What error?

Originally Posted by JosephP

what error and why do you need to select those sheets?

Runtime error '1004:' Select method of worksheet class failed. I am selecting those cells so when the user returns to the page they will know where to start.

The sheet is only visible when this is true, Range("A8").FormulaR1C1 = "Rate Indication", which is my first line of code in the reset macro. what it does is changes cell A8 on the first page to "Rate Indication" that then will unhide all the sheets below i am trying to reset.

Move the line:

Please Login or Register  to view this content.

To immediately after the line where you set cell A8 to "Rate Indication"

Originally Posted by Andrew-R

Move the line:

Please Login or Register  to view this content.

To immediately after the line where you set cell A8 to "Rate Indication"

Okay so now i have this and it is giving me an error on the last .Select in my code. I think it is almost there i got past all of the other .Selects

Please Login or Register  to view this content.

So is the sheet "Loss Ratio Proj Instructions" visible?

Originally Posted by Andrew-R

So is the sheet "Loss Ratio Proj Instructions" visible?

When the cell A8 on selections contains "Rate Indication" then the 'Rate Indication Instructions', 'Loss Development Factors', 'Claim Count - Credibility', and 'Summary' Sheets are all visible. When cell A8 on 'Selections' says "Loss Ratio Projection" then the sheets 'Loss Ratio Proj Instructions', 'Loss Development Factors', 'Claim Count - Credibility', and 'Summary' Sheets are all visible. The worksheet is to do two different things just depending on what the user wants to do. that is why i have to go back to A8 on the 'Selections' sheet and change the name from one thing to another in my reset macro above, to make the sheet visible then clear the contents. I hope i am being clear enough. Thank you all again.

Ah, I hadn't seen you'd changed the value in A8 mid-macro.

Before you do that change you need to set Application.EnableEvents to True, and then set it back to False after you've changed that cell.

This is really, really terrible coding, and we should do this properly, but it's a hot and stuffy afternoon in my office and I've lost the will to live

Originally Posted by Andrew-R

Ah, I hadn't seen you'd changed the value in A8 mid-macro.

Before you do that change you need to set Application.EnableEvents to True, and then set it back to False after you've changed that cell.

This is really, really terrible coding, and we should do this properly, but it's a hot and stuffy afternoon in my office and I've lost the will to live

Haha, I figured it was bad coding, but i am very very new to VBA and all i did was record myself then clean it up a little. Is it at all posible could you post my code with your changes in the correct places, just the EnableEvents lines. Thanks so much

Try this:

Please Login or Register  to view this content.

However, if my copy of "Professional Excel Development" attacks me I'm holding you personally responsible

I can see the finish line from here ...

Please Login or Register  to view this content.

Originally Posted by Andrew-R

I can see the finish line from here ...

Haha, Unfortunatly i am now getting those stubborn boxes popping up again. hah, this is so frustrating. It seems to be back at square one.

One more try:

Please Login or Register  to view this content.

I may have made too many sheets hidden there, but you can take out the .Visible = False lines as required.

Originally Posted by Andrew-R

One more try:

I may have made too many sheets hidden there, but you can take out the .Visible = False lines as required.

Okay, I see what you are doing. Now, after the reset runs it is showing 'Selection', 'Loss Development Factors', 'Claim Count - Credibility', and 'Summary'. The only sheet i want to show is 'Selection' and that is what it should be doing when A8 on 'Selection' is "", I am not sure why it isn't doing this. Is there more .Visible statements i can put at the end of the code to just force them to not be visible. The only sheet i want to be visible is 'Selection'. Thanks

I think i got it. All i did was add some lines to the bottom of the code to force the sheets to not be visible. I think this should work, Please let me know if you think in the long run you have a better solution or something looks kind of fishy with my code. Thank you all so much for all the help, Andrew-P especially.

Please Login or Register  to view this content.