auto macro's action reactivates same macro endlessly. 1

[cmerch19602006]

Hey folks! I am new here, and really did try searching the threads, but I bet you all know how that can go sometimes.

I am building a project planner in Excel 2003 (I don't like any of the one's I've seen). I want to automatically reposition previous entries when new entries are entered into a sheet. I attempted this by using a "worksheet_change" macro that inserts a new row, then moves the new entry into that row.

A lot of you probably already know what happened with that, but I'll describe it anyway:

Because the macro is activated by a change in the worksheet, and the macro also causes a changes in the worksheet, the changes cause the macro to run again. Endless process!

My question:

Is there a way to cause the Worksheet_Change macro to stop it's own reactivation by, say, turning off all auto macro operations temporarily?

There is code in VBA help to turn off the AutomationSecurity and then turn it back on but it doesn't work for this purpose.

I have considered switching to a userform for input but I thought using the Worsheet_Change would be more efficient and require a lot less code.

 

[DirkStruan]

cmerch

I may have an ugly fix for you to get rid of the infinite loop. Hopefully someone will suggest a more professional fix.

I would put a flag on the worksheet, a 1/0, True/False entry or something. You can then use a second macro (such as selection_change to edit the value and have your spreadsheet_change macro refer to it. Effectively you can use the selection change macro to identify the need for a human input.

Here's an example bit of code for the worksheet that might explain the idea:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") <> 1 Then Exit Sub

qut = MsgBox("Quit", vbYesNo)
If qut = vbYes Then
Range("A1") = 0
Exit Sub
End If

Range("A1") = 0
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Row = 4 Then Range("A1") = 1
End Sub

In this case Worksheet_change only triggers if row 4 is selected.

Until someone suggests something better.....

Dirk

 

[TonyJollans]

Take a look at Application.EnableEvents. Set it to False before making your worksheet changes and set it back to True afterwards.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[DirkStruan]

I knew someone would have a pretty solution!

Dirk

 

[cmerch19602006]

Thank you Dirk. Your right, that would be a "dirty" fix, but also just the sort of thing I was about to do.

Many thanks Tony! Haven't used that one in about five years and just couldn't seem to pull it out of memory. Works like a charm, finally I can see the bugs. Time to go step on em!