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?

Ed

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:

'Set range
  Set rngRow = wks.Range(Cells(thisRow, 1), Cells(thisRow + cntRows - 1, 103))
  'Delete rows
  rngRow.Delete xlShiftUp
  'Refresh formulas
  y = wks.Range("StartRow").Column
  Set rngRow = wks.Range(Cells(thisRow - 1, 1), Cells(thisRow - 1, y))
  For Each rngCll In rngRow.Cells
    If rngCll.HasFormula Then
      wks.Range(Cells(rngCll.Row, rngCll.Column), Cells(rngCll.Row + cntRows + 2, rngCll.Column)).FillDown
    End If
  Next rngCll
  
  Call ActivateProtection
  
  If bolShar = True Then
    'MsgBox "Don't forget to turn Share Workbook back on."
    Call ShareBook
  End If

What are your recommendations for handling this?
Ed

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:

Private Sub UserForm_Activate()
  Call Wait
End Sub
 
Private Sub UserForm_Initialize()
  Me.L1.BackColor = vbWhite
  Me.L2.BackColor = vbWhite
  Me.L3.BackColor = vbWhite
  Me.L4.BackColor = vbWhite
End Sub
 
Sub Wait()
Dim lbl 'As Label
Dim x As Long, y As Long, z As Long
StartMe:
   For x = 0 To 3
    Set lbl = Me.Controls(x)
 
    lbl.BackColor = vbRed
    Me.Repaint
    For y = 1 To 500
      For z = 1 To 100000
      Next z
    Next y
    lbl.BackColor = vbWhite
    Me.Repaint
  Next x
GoTo StartMe
End Sub

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
DoEvents
Form1_MainMacro
End Sub

Sub Form1_MainMacro
Main Code Here
Close Form2
End Sub

Ed

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() 
  DoEvents 
  frmWaiting.Show vbModeless
  DoEvents 
<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?
Ed
(XL2007, Vista Pro)