I have an ID number that is automatically calculated/constructed from values
in other fields. When the user adds a row, the formula is automatically
added and the ID number calculated for the added row. However, when the
user deletes a row, I get #REF! in the cell. I have a menu option that
allows the user to recalculate all the ID numbers when this happens - but
they often forget. I would like to remove the menu item and have the app
recalculate automatically when the user deletes a row so they don't have to
think about it.

If I put "call RecalcID" in the workbook SheetChange event or the
SheetCalculate event, I get an endless loop because each time RecalcID is
run, it changes the workbook.

How can I run RecalcID only once when the user deletes a row?

TIA
GMet

Hi
you may post the formula which you're currently using

--
Regards
Frank Kabel
Frankfurt, Germany

"GMet" schrieb im Newsbeitrag
...
I have an ID number that is automatically calculated/constructed from
values
in other fields. When the user adds a row, the formula is
automatically
added and the ID number calculated for the added row. However, when
the
user deletes a row, I get #REF! in the cell. I have a menu option
that
allows the user to recalculate all the ID numbers when this happens -
but
they often forget. I would like to remove the menu item and have the
app
recalculate automatically when the user deletes a row so they don't
have to
think about it.

If I put "call RecalcID" in the workbook SheetChange event or the
SheetCalculate event, I get an endless loop because each time
RecalcID is
run, it changes the workbook.

How can I run RecalcID only once when the user deletes a row?

TIA
GMet

Application.EnableEvents=false
call RecalcId
Application.enablevents=True


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"GMet" wrote in message
...
I have an ID number that is automatically calculated/constructed from
values
in other fields. When the user adds a row, the formula is automatically
added and the ID number calculated for the added row. However, when the
user deletes a row, I get #REF! in the cell. I have a menu option that
allows the user to recalculate all the ID numbers when this happens - but
they often forget. I would like to remove the menu item and have the app
recalculate automatically when the user deletes a row so they don't have
to
think about it.

If I put "call RecalcID" in the workbook SheetChange event or the
SheetCalculate event, I get an endless loop because each time RecalcID is
run, it changes the workbook.

How can I run RecalcID only once when the user deletes a row?

TIA
GMet

Here is the ReCalcID macro.

Sub RecalcID()
'This macro will recalculate IMS ID numbers
col = Range("EACT")
mycol = Cells(1, col).Value
'check of tab is an Event tab
If Cells(1, Range("EACT")).Value < "EACT" Then
MsgBox "Incorrect tab - must select an Event tab"
Exit Sub
End If
'capture name of current sheet
thissheet = ActiveSheet.Name
'capture current row
cRow = Selection.Row
'data rows always start at row 4
Row = 4
Range("A4").Select
'count number of data rows
lastrow = Cells(Rows.Count, 11).End(xlUp).Row
Application.CutCopyMode = False
'copy template of formula into row 4 of selected sheet
Sheets("CalcFormula").Range("A4:H4").Copy Sheets(thissheet).Range("A4")
'select data range
Range("A4:H" & lastrow).Select
Selection.Copy
Selection.FillDown
Range("a" & cRow).Select
End Sub

GMet

"GMet" wrote in message
...
I have an ID number that is automatically calculated/constructed from
values
in other fields. When the user adds a row, the formula is automatically
added and the ID number calculated for the added row. However, when the
user deletes a row, I get #REF! in the cell. I have a menu option that
allows the user to recalculate all the ID numbers when this happens - but
they often forget. I would like to remove the menu item and have the app
recalculate automatically when the user deletes a row so they don't have
to
think about it.

If I put "call RecalcID" in the workbook SheetChange event or the
SheetCalculate event, I get an endless loop because each time RecalcID is
run, it changes the workbook.

How can I run RecalcID only once when the user deletes a row?

TIA
GMet

Charles,
Thank you!! Works great!!

GMet

"Charles Williams" wrote in message
...
Application.EnableEvents=false
call RecalcId
Application.enablevents=True


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"GMet" wrote in message
...
I have an ID number that is automatically calculated/constructed from
values
in other fields. When the user adds a row, the formula is automatically
added and the ID number calculated for the added row. However, when the
user deletes a row, I get #REF! in the cell. I have a menu option that
allows the user to recalculate all the ID numbers when this happens -
but
they often forget. I would like to remove the menu item and have the
app
recalculate automatically when the user deletes a row so they don't have
to
think about it.

If I put "call RecalcID" in the workbook SheetChange event or the
SheetCalculate event, I get an endless loop because each time RecalcID
is
run, it changes the workbook.

How can I run RecalcID only once when the user deletes a row?

TIA
GMet