Posts by EdFromAZ

    I distributed an XL2007 macro-enabled workbook (most worksheets protected). It worked fine for me when I gave it out - I had no idea so many people could find different ways to break this thing!!

    So I need to make so changes to the code in all these workbooks. Unfortunately, our corporate environment has permanently disabled "Trust access to the VBA project object model". So I can't simply write an update script that will modify the code, correct? (Please tell me I'm wrong!!)

    So the only other way I can see to make this update is to ask everyone to send me their files so I can add in new code. Or maybe distribute a new workbook with corrected code that will copy over the worksheets from the old file, rename the new file the same as the old one, and SaveAs over the top of it? Might that work?


    I have an XL2007 workbook that is protected and shared. Because of formulas in other parts of the worksheets, I had to create a macro to allow users to insert and delete rows in the section they use; the macro ensures the insertion/deletion occurs only in the section they can use and that the formulas are copied down.

    One user pointed out that, even though I do ask for "Are you SURE??" confirmation, someone *is* going to make a mistake and want to undo the row deletions. If I remember correctly, it doesn't save until I make it shared. But I don't know if macro steps are undo-able, and how to know how many steps to go back.

    Here's my code for the deletions:

    What are your recommendations for handling this?

    Re: Display "Please Wait" form

    Thank you for responding and helping, Ger. Sorry for the title mix-up - I tried to make it a more complete description of what I was dealing with.

    I presume the issue wouldn't be so bad if I had a static "Please Wait" message on frmWaiting. But then the user would just have one more form to stare at and wonder if the code is stuck. So I made four text boxes change color and chase each other like so:

    Since both the main macro in the first form and the cycling code in the Waiting form both work, I figure my problem is a lack of understanding of how to correctly call two forms like this with DoEvents. I presume that if the second form simply had a label with "Pleae Wait" on it, I could simply call the form to open and continue on with business. But since the form runs the code loop to cycle the colors when it opens, it grabs focus and doesn't let the main code continue.

    (I did read the link you pointed me to. I didn't get much out of it - sorry for my lack of understanding.)

    Should I change the order everything is called?
    Something like:

    Sub Form1_ButtonClick
    Call Form 2
    End Sub

    Sub Form2_Open
    Start Loop
    End Sub

    Sub Form1_MainMacro
    Main Code Here
    Close Form2
    End Sub


    I've got a pretty long-running macro inside a UserForm, and I wanted
    to open a second form just to show that things are still working and
    not crashed. It's pretty simple - four labels and I cycle around
    turning one at a time a different color.

    (The macro in the main form doesn't loop.
    This is all inside an add-in, and I can't display a separate sheet.
    I opted for this approach vs a simply status bar comment
    because this user will never look there!)

    I call it from the main form with

    Private Sub CommandButton1_Click() 
      frmWaiting.Show vbModeless
    <rest of macro code>

    Unfortunately, once the code opens the second form (frmWaiting),
    the main code in the first form never continues to execute.
    The code inside frmWaiting does indeed cause my little red
    boxes to march around the form - but they'll do that forever
    because the execution nevwer returns to the main form!

    I thought DoEvents was supposed to let the running code "drop
    through" to allow both forms to run their code? Obviously, I'm
    very incorrect!! 8>(

    How can I make this happen correctly?
    (XL2007, Vista Pro)