Auto ReCalc

G

GMet

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top