Data Entry to a Cell Range

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 
0
Utf
3/6/2010 6:02:02 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
680 Views

Similar Articles

[PageSpeed] 31

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 
0
Utf
3/6/2010 12:44:01 PM
Reply:

Similar Artilces:

Verification/Import of data
Hello, I have two separate worksheets. I would like to use the first worksheet to verify the same data on the second worksheet is correct. For instance, in worksheet one I have a city and a number (in two individual cells) and I would like this same data in the second worksheet (in two individual cells). Is there a way to somehow link (?) or import or (?) the two sheets so when the first worksheet is updated, the second worksheet will also update. I am so very lost.:confused: Thanks for your help! -- Karmen ------------------------------------------------------------------------ Kar...

Find in Named Range problem (2nd Try)
Hello. I'm using Excel X on a Mac (VBA5?) and attempting to write a macro which names a range of text cells I've selected, then searches the cells in that range and bolds any which contain the ">" character (as text, not as "greater than" in a formula). I'm not using conditional formatting because I also want to get rid of the > characters once the bolding is complete. In stepping through the following, I find that it does name the range and does the finding and bolding in a loop. Unfortunately it doesn't stop at the end of the range, but co...

Entry point not found #3
After running a repair on Windows XP I get the following errror message when opening Outlook; "The procedure entry point GerUMS could not be located in the DLL MSDART.DLL". This error message cycles 6 time during the opening of Outlook. I renamed MSDART.dll and ran an Office repair, but it didn't fix the problem. Any Help would be appreciated. Bill Hi Bill, I understand that you receive the error message "The procedure entry point - GerUMS could not be located in the dynamic link library MSDART.DLL" when opening Outlook. Based on the error message, this is...

How do I change the color for cell selection in excel?
I just started using Excel 2007. In the older versions when I select a row, column, or specific group of cells, Excel would highlight the group in a certain color. In Excel 2007, it simply surrounds the selected group of cells with a thick black line. I've tried going into Excel Options under Advanced but couldn't find how to do this. Can you assist me in changing this feature? On Dec 31 2007, 6:52 pm, dminliberty <dminlibe...@discussions.microsoft.com> wrote: > I just started using Excel 2007. In the older versions when I select a row, > column, or specific grou...

Default SOP Error on Sales Transaction Entry
One user gets the "Default SOP Error" on a regular basis. When the user gets this, it spreads to other users and I have a headache on my hands. I have tried creating a new user account for this user. I have uninstalled and reinstalled GP7.5Vg3. I have run checklinks on the sales side (and the entire system). Why is this happening and how on earth do I make it go away? Firing the user is NOT an option. ...

Outlook 2007 doesn't understand vCalendar entries any more !?!
I occasionally need to copy/paste calendar entries from another calendar application into my private Outlook. Until recently - using Outlook 2002/XP - this worked fine. Since I switched to Outlook 2007 this does not work any more. Instead of pasting a new appointment I only get a new appointment with no title, no date, no other field filled except the comment field which contains the entire (and - as far as I can see: valid!) vCalendar entry. Can one somehow teach OL2007 to accept and properly interpret such entries? Michael I just learned that I can paste that calen...

change cell shading when a number differs from the previous number
I am generally familiar with Excel, but need some help on this particular problem. Is there a way to apply a format where: whenever the number in a column differs from the one previous to it, a cell shading color change is applied. For example, you have a column of numbers in Excel: 2 2 2 5 (cell shading color change applied) 5 5 5 2 (cell shading color change applied) 2 2 3 (cell shading color change applied) etc.... It doesn't matter what color is used, just so that it is applied whenever there is a change from the previous number. There has got to be an easy answer to this.... ...

Duplicate Entries in Bills and Deposits
Recently I was browsing through Bills and Deposits when I noticed several (5 or 6) duplicative entries. Some old, some new. When I delete one, both vanish. Anyone know whats going on? Don't know how long this has been going on. I assume this is not normal? I'm using Money 2004 and Windows XP Home SP2 Thanks, Randy Stop using Microsoft money web service; disconnect your money from the Web. Sign on without a passport. Microsoft tries to synchronize your computer with a copy of it on its server, and it does not work, creating duplicates in your money. Disconnect your passpo...

Data Validation drop down not showing and Formula Auditing bar greyed out
Good afternoon, I've created a spreadsheet that has multiple Validated cells. Each of these cells is validated using a list, with "In-cell Dropdown" Checked. However the drop down is not showing. The cell is still being validated as I cannot enter a value other than what is in the list. Also, on the formula auditing bar, the Trace Precedents, Remove Precedents, Trace Dependants, Remove Dependants, Remove all arrows and Trace Error buttons are all greyed out. I've checked that the sheets and workbook are not protected and as far as I can tell it has happened between file ...

How can I stop format change when copying data into unlocked cells
How can I stop the format changing when I copy data into an unlocked cell in a format protected worksheet? I need to allow people to both enter data or copy data from another source into cells so I have unlocked these cells but also don't want the format of the cells to be changed. If you enter data directly the format does not change but if you copy and paste data it changes the format. I know you can use "copy paste special values" but would like to know if there is some system way of doing this. Copy/paste this into the sheet module. Right-click on sheet ...

Excel : insert new data series via mouse click on graphic.
Dear Developers, I work as researcher in a sector that uses spatial data. i always return back to excel, honouring Turing principle, after "travelling" to more sophisticated Sw or ambients (like matlab, and others). One of the few thing I find annoyng in excel is that one cannot input data via graphic with a click of mouse on the cartesian space, function very useful in spatial data. Sincerely ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button ...

Putting a "period" at the end of every cell's-worth of text
Hi. I have a large number of cells in a column, each of which contains text. Some cells of text end in a period, some do not. Is it possible to write a function that would add a period to the end of each textbox? I am not worried about corner cases, i.e., where the text ends with "M.D." or suchlike. Thanks for any help - Insert an adjacent column. The following example assumes that the text is in cell A2 and the formula in B2 (The inserted column). =IF(RIGHT(A2,1)=".",A2,A2&".") Copy the formula down for the full length of the data then select the co...

Restricting entry
Hi, I have a simple spreadsheet which I need help with. I need to prevent a customer from using more then one computer (C*) and or laptop. What I need is a way of stopping more then one yes per row. I have validated the cells with a list of Yes/No, but cannot find a way of stopping the Yes being entered if a yes has been entered in a different cell on the same row? Own Laptop C1 C2 C3 C4 C5 Customer 1 Yes Customer 2 Yes Customer 3 Yes Customer 4 Yes Customer 5 Customer 6 Yes Customer 7 Customer 8 Yes Customer 9 Yes Custome...

No e-mail entry under MY Work
No e-mail entry under MY Work Solved the problem. For some reason some of my Sales users were set for Service. Changed from Service to Sales in the Workspace Options. "Yoram" wrote: > No e-mail entry under MY Work ...

Data Validation in 2007
Is there a way to make the Data Validation dropdown list dispaly descriptions but when you select one it puts in the corrosponding ID instead? Example: Name: Phone # Smith 360-482-4747 Bowers 360-482-7878 Gates 360-482-8877 So when I'm in the cell and hit the drop down and Select "Bowers" - it actually saves the phone number "360-482-7878" instead of "Bowers" Not directly but in an adjacent cell you could use VLOOKUP to pickup the phone-number best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme r...

Too many different cell formats #2
I am working with a large spreadsheet (with many worksheets) that will not allow me to change/add any more cell formats and limits my additional data entry. The message pops up "Too many different cell formats". Per Microsoft's support page 213904, they instruct you to reduce the number of formats to standard formats, etc. I've tried to do that but any change backwards to standard formatting is evidently the same as new change. I'm stuck. There must be a trick to quickly change each worksheet back to a standard format. Any help would be appreciated. Excel 2003o...

Double Space Cells
How do you change a cell into a double space cell- one word on top of another in a single cell? Hi joe type the first word, press ALT+ENTER then type the second alternatively, type both words, right mouse click, choose Format / Cells, go to the alignment tab & choose wrap text - this will work if the column is only wide enough to accommodate one of the words on each line Cheers JulieD "Joe" <anonymous@discussions.microsoft.com> wrote in message news:19c1501c44d76$b9b96070$a401280a@phx.gbl... > How do you change a cell into a double space cell- one > word on top of...

how do I make a cell in Excel required to be populated?
I am attempting to use Excel for a form I am creating and need to make certain cells required. I am unfamiliar with Macros, but am thinking this may be the only way to do this. Thoughts? You could use a macro that looks at those cells and counts how many are filled in. But since macros are new, how about an alternative? Use an adjacent cell. Put a formula like: =if(c3<>"","","<----Please fill in this cell") (I used D3 for this) Then format it in a nice bright bold red. The user sees the warning immediately. If you have formulas that depend on al...

Non-VBA to gow grow based on another cell?
Hello, If I type in dog;cat;fish;cow;rat in cell A1, what I want in cell B1 is: dog cat fish cow rat stacked on top of each other. I used =substitute(a1,";",char(10)) and checking "word wrap", so now it looks like it is supposed to. But when I add more entries in cell A1, I don't know of a way to get the cell to expand row height so the entries don't scroll off. And when I reduce entries down to three, the cell should shrink. Basically, the height of the row should match the contents. Can this be done non-VBA? Thanks for all your help with this and past ques...

Prefixing a number to the number in a cell
I have in column A, 1000 numbers, now i need to prefix an identical number to all the 1000 numbers in A column. What is the easiest way?. I want the number to be prefixed in the same A column.. Pls help Regards Sherees -- Life isa journey not a destination Hi Sherees, Try this, as I don't believe you can do this with a formula. Sub PrefixItsSelf() Dim cell As Range For Each cell In Selection cell.Value = cell.Value & cell.Value Next End Sub Copy into the sheet module and select the cells you want to prefix and then run the macro. HTH Regards, Howar...

data label
I am trying to import a .cvs or .txt file into an email program. The error comes up saying - field name headers do not exist. any help out there? thanks.liz. Hi Liz, Might help if you specified the name of the other program. Sounds to me like your first row should have specific content words to identify the column content. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm "lizchase" <lizchase@discussions.microsoft.com> wrote ... > I am trying to import a .cvs or .txt file into ...

Scroll Area _Named Range in worksheet
Hi I have named range in my worksheet "INVME" in sheet1 replaced as "Master", now I want to control thr user to move within the named range. How can i control this in VBA. Thanks in advance -- Ron Rony Private Sub Workbook_Open() With Sheets("Day") ..ScrollArea = "invme" End With End Sub Or use worksheet.activate code in the worksheet Gord Dibben Excel MVP On Sat, 26 Nov 2005 12:16:06 -0800, "Rony" <Rony@discussions.microsoft.com> wrote: >Hi >I have named range in my worksheet "INVME" in sheet1 replaced as &qu...

Cell colours not visible in linked document
I'm using Word 2003 in XP and have a Word document for a price list in which the data is in a linked Excel 2003 spreadsheet. For clarity, alternate rows in the Excel table have a fill colour (25% gray). The problem is that this colouring is not visible in the Word document, print preview or when output to PDF. Printing to a laser printer is fine (using PCL5e and PS drivers), but not having it visible in the PDF is a problem. Any ideas? JF. ...

Append data / Prevent duplicates
Hi all, I am working across platforms with my data (ESRI/GIS and Access). I am using Access for billing and archiving (and a lot of other cool tasks that I don't have the patience to perform in ArcGIS). What iwant to do is take data from my tblInspection (ArcGIS) and move it to tblInspection_Archive (Access). My Key field is ParcelID (non-unique in archive) and I have a Round # field (e.g.: 2009_R1, 2009_R2). I need to use these fields to create a unique key so if someone tries to run the query multiple times on the same Round it will error out. SQL: INSERT INTO tblV...

cell format #5
I have a vlookup formula. =IF(ISERROR(VLOOKUP($A7,avail,7,FALSE)),"",VLOOKUP($A7,avail,7,FALSE)) the formula is looking for a date. the formula works fine. but what is happening is that if it finds the item and then column 7 is blank. the formula returns the following. 01/00/00 how can i get it so it doesnot return anything at all if there is nothing in the column. What do you want to see? I'm guessing nothing: =if(iserror(yourformula),"",if(yourformula="","",yourformula)) alternatively since you want to see "" in either cas...