Prev: Apply format to >0 cells
Next: Custom Header Aligment
From: Stilltrader47 on 6 Mar 2010 01:02
Can I set up a data entry form, so if every time I enter a value in a cell,
it updates the next empty cell in a range? Thanks
From: Gary''s Student on 6 Mar 2010 07:44
Lets assume that the form is used to update cell A1. We require that
everytime A1 is updated the new value will be recorded in column B. Put the
following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A1 As Range, t As Range
Set A1 = Range("A1")
Set t = Target
If Intersect(A1, t) Is Nothing Then Exit Sub
Application.EnableEvents = False
n = Cells(Rows.Count, "B").End(xlUp).Row + 1
Cells(n, "B").Value = A1.Value
Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

--
Gary''s Student - gsnu201001


"Stilltrader47" wrote:

> Can I set up a data entry form, so if every time I enter a value in a cell,
> it updates the next empty cell in a range? Thanks
 | 
Pages: 1
Prev: Apply format to >0 cells
Next: Custom Header Aligment