Loop to find sheets and then give me a popup box

  • Hi Guys,


    I'm writing code in a template and I want the user to basically get prompted when the data is already formatted in my template sheet. If it is present (ie the formatted sheet tab is there) the user will be prompted to delete all formatted tabs.



    The code is as follows:



    The only issue is that I get the popup box three times. I only want it to pop up once for any of those instances. If it finds one of them, I have an option to delete all sheets that it finds.


    Hope this makes sense.

  • Re: Loop to find sheets and then give me a popup box


    Hi,


    Firstly, your code will not compile or run, as the first IF statement does not contain THEN.


    I would then suggest that you properly indent the code, so that the logical flow is apparent. You will then see that the code is checking for 'datapresent = True' within the loop through each sheet within the workbook, rather than just once within the workbook.


    In the code below, I have also replaced the undeclared variables 'Sheet' and 'Book' with the variables you have declared, but not used.


    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Loop to find sheets and then give me a popup box


    'Exit For' jumps out of the current For ... Next loop, and goes to the statement following Next.


    Once you have found one sheet that indicates that data is present, there is no point in checking any of the other sheets, as you are not going to change the setting of 'datapresent'. It's just there to make the process a little more efficient.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!