PDA

View Full Version : Automatically entering data in cells with Excel 2003



dipdog
08-17-2006, 08:50 AM
I'm sorry if this has been posted before, but I am having difficulty finding an answer that works for me. I'd like to write a simple excel file that would do 3 things.

1) Automatically fill in the next sequential row number every time a cell is selected in the A column. ie. after filling row #1 with data and hitting enter, cell A2 would automatically fill with the number 2, and then, after hitting enter, A3 would automatically fill with the number 3, etc.

2) Automatically fill in today's date (static, so that the value will not change tomorrow) every time a cell is selected in the B column. I've tried adding a couple different versions of macros/module to the VBAproject, but I'm having difficulty getting that to work, and perhaps I need step-by-step directions. One thing I didn't understand is, does a macro have to be manually started (ie. Tools>Macro>Macros>Run) every time you select a cell in order for it to run?

3) Automatically fill in a word every time a cell is selected in the C column, such that if I hit Tab the word will remain or if I hit Backspace I can type in a different word.

I'm pretty new to Excel, so I'm hoping these are easy questions!

Thanx for your help

- Scott

lucas
08-17-2006, 10:07 AM
Cross posted: http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1155828891

Dipdog,
Click here for an explanation of cross-posting
http://www.excelguru.ca/node/7

dipdog
08-18-2006, 05:23 AM
My apologies. First time anyone's ever pointed that out to me. Anyhow, I'd be grateful for your assistance.

mdmackillop
08-18-2006, 09:48 AM
For what it's worth!


Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then IncrementA Target
If Target.Column = 2 Then DateB Target
If Target.Column = 3 Then WordC Target
End Sub

Sub IncrementA(Target As Range)
If Target.Row() = 1 Then Exit Sub
Application.EnableEvents = False
If Target = "" And Target.Offset(-1) <> "" Then
Target = Target.Offset(-1) + 1
End If
Application.EnableEvents = True
End Sub
Sub DateB(Target As Range)
With Target
.Value = Now()
.NumberFormat = "dd/mm/yy"
End With
End Sub
Sub WordC(Target As Range)
With Target
.Value = "ThereMustBeSomeSenseToThis,ButIDon'tSeeIt"
End With
End Sub

lucas
08-18-2006, 03:07 PM
Pretty nice Malcolm. Getting close to the impossible.

mdmackillop
08-18-2006, 03:25 PM
Maybe I'll make a KB out of it!

mdmackillop
08-19-2006, 03:55 AM
:conscious

dipdog
08-22-2006, 07:02 AM
Sorry I'm late replying. That is sweet!! Thank you very much.

- Scott

Again, my apologies about the cross-post. Hope this helps someone else as well.

dipdog
08-24-2006, 05:15 AM
mcmackillop
This is probably a stupid question, but I've been using and customizing the file that you attached (justforfun.xls). I noticed that the number of rows is fixed at 30. Is there an easy way to change it so it will go beyond 30 rows?

Thanx!

mdmackillop
08-24-2006, 06:00 AM
Hi dipdog
The rows are just hidden, Select the worksheet and Format/Rows/Unhide

mdmackillop
08-24-2006, 06:02 AM
BTW, VeryStrange in Post 4 is the "functional" file. JustForFun is just what it says.

dipdog
08-24-2006, 07:23 AM
Yah, I guess that was a stupid question. :doh: Thanx again!