My For/Next not looping to "Next"

  • Good day

    I'm having a problem that I can't figure out. I have a workbook that, when activated, emails out a report of some of the sheets. It works great, but to clean the sheets up, I want to remove the form controls as they are no longer needed. I found the code from Ron de Bruin site to remove the controls. It works great on one sheet, and does exactly as I need provided the sheet is unprotected. I have a sub to unprotect sheets which I use in another area of the program...(called UnprotectAllSheets (attached below)) ...It works great elsewhere.

    I added the code..."For Each sh In Application.ThisWorkbook.Worksheet & "Next", to the appropriate spot in the sub that I got from Ron de Bruin; to remove the form controls. It's called (Shapes4) also attached below. I'v also included the section of the program, which houses the array that I'm saving in the 'Report' file. My problem is that both subs do what they're supposed to, EXCEPT go to the "Next" sheet to perform the necessary function. It does everything well with unprotecting & deleting the form controls...but just on the first sheet of the array.

    Any help will be greatly appreciated.

    I hope that the code went in as readable. Seems to me, that when I used to hit the </> symbol it gave me 2 words to frame the code. Anyway you can respond to me on that if you'd like, so I'll know for the future

    Thanks JimmyB :)


    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hi Jimmy,


    I think it is due to the activesheet, you either need to activate each sheet prior to starting the instruction, or preferably change the code to avoid the activesheet requirement.


    eg:


    Code
    1. for each sh in worksheets
    2. for each shp in sh.shapes
    3. your code
    4. next shp
    5. next sh

    HTH

  • Hey Justin...you're a genius. As soon as I saw your recommendation I figured, that had to be it.

    Thanks a ton. You're a lifesaver.

    Have a great day and

    Keep a good distance

    Jimmy B ;)

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)