Event triggered by cell data change/entry

[Fred Holmes]

Is there an event that is triggered by changing the data in a
worksheet cell? What is it called?

I have a table that is filled in line by line (row by row). I want
the rows that have not yet been filled in with data to appear
completely blank all the way across. Several columns on the right
contain formulae that process the data entered into the left-hand
columns. I want the process of entering data into one of the
left-hand columns to trigger a macro that writes in the forumulae in
the right-hand columns. Trigger on a single cell, notheing more
complicated than that. Does such a trigger exist?

Right now I'm using an IF(ISBLANK(B13),"",[the real formula]) sort of
formula to accomplish this, but this makes the underlying calculation
of the real formula sort of "inscrutable" which I want to avoid.

The process of using cell formatting to make zero values appear blank
doesn't work because two of the columns are running subtotals and
totals that are not zero, even if the current line contains no data
yet.

Thanks for any help,

Fred Holmes

 

[Bernie Deitrick]

Fred,

The example code below will copy six columns of formulas from columns C:H
whenever you enter a value into a cell in column B. This assumes that the
formulas exist in the row above the one where you are entering the data.

Copy the code below, right-click the sheet tab, select "View code" and paste
the code into the window that appears. Change the 2 to the column number of
the column that you want to use to trigger the copy, and the 6 to the number
of columns of contiguous formulas to copy.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCol As Integer
Dim intFC As Integer
intCol = 2 'makes this work on column B
intFC = 6 'How many columns to the right of B to copy
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = intCol Then
If Cells(Target.Row, intCol + 1).HasFormula Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row - 1, intCol + 1).Resize(1, intFC).Copy _
Cells(Target.Row, intCol + 1)
Application.EnableEvents = True
End If
End Sub

 

[Bernie Deitrick]

Fred,

I should also have mentioned that with XL XP or higher, you could simply use
Tools | Options... Edit tab, and check "Extend list formats and formulas"

HTH,
Bernie
MS Excel MVP

 

[Fred Holmes]

Yes, and presumably with earlier versions, one could select, in the
last used row, the cells in the columns that have formulae, and then
use the fill handle drag down the number of rows one desires. (havent
actually tried it yet) But I'm writing this for someone who is not a
programmer or forumula person or even a real Excel user. He's a
graphic designer who wants a simple, *automatic* "time card" (billable
hours record).

Many thanks for your help.

Fred Holmes

Fred,

I should also have mentioned that with XL XP or higher, you could simply use
Tools | Options... Edit tab, and check "Extend list formats and formulas"

HTH,
Bernie
MS Excel MVP

Is there an event that is triggered by changing the data in a
worksheet cell? What is it called?

I have a table that is filled in line by line (row by row). I want
the rows that have not yet been filled in with data to appear
completely blank all the way across. Several columns on the right
contain formulae that process the data entered into the left-hand
columns. I want the process of entering data into one of the
left-hand columns to trigger a macro that writes in the forumulae in
the right-hand columns. Trigger on a single cell, notheing more
complicated than that. Does such a trigger exist?

Right now I'm using an IF(ISBLANK(B13),"",[the real formula]) sort of
formula to accomplish this, but this makes the underlying calculation
of the real formula sort of "inscrutable" which I want to avoid.

The process of using cell formatting to make zero values appear blank
doesn't work because two of the columns are running subtotals and
totals that are not zero, even if the current line contains no data
yet.

Thanks for any help,

Fred Holmes