1 Click Data entry

Microsoft Microsoft excel 2000
July 22, 2009 at 04:24:10
Specs: Windows XL Pro
Need to enter DATE, TIME in 1st col 100+ times a day. {=NOW()} is exactly what I need, but typing that in 200 times, it will be correct 100 times and in ERROR 100 times (wrong format, mistyped, etc.). My first thought was a [CMD] button that when DOUBLE_CLICKed, would enter {=NOW()} .
However, I have forgotten more VB than I thought. Can someone lay out the EXACT steps... or is there an easier and simpler way?

See More: 1 Click Data entry

Report •


#1
July 22, 2009 at 12:08:58
You do realize that if you do it that way,
every time your sheet re-calcs your =NOW() function
will change to the current Date/Time,
thus changing the Date/Time of your original entry?
Which is probably not what you want to happen.

MIKE

http://www.skeptic.com/


Report •

#2
July 22, 2009 at 21:48:17
Not if you set recalc to MANUAL (AUTO OFF).

Report •

#3
July 23, 2009 at 08:14:34
First create your Macro:

On the Task Bar,
Select Tools,
Select Macro,
Record New Macro,

A small window should open,
Give the Macro an appropriate name, like “MyDate”
Click OK

A small box with the word “Stop” should appear.
If it does not don’t worry, will take care of it later.

Place your cursor in any cell and enter the formula:

=NOW()

Press Enter
.
Click the “Stop” button to end the macro.
If there is no Stop button, then, On the Task Bar
Select Tools,
Select Macro
Click on Stop Recording Macro


Create a button for the macro

On the Task Bar
Select Tools,
Select Customize

In the small window, under “Categories”,
Scroll down and click on “Macros”
The right panel should change and there should be a smiley face next to “Custom Button”
Left click on the smiley face, hold and drag up to the Task Bar and let go.
Where, on the Task Bar, you put it is up to you. I usually place it on the far left or next to the Help button.

Click “Close” on the small window.

Next, click on the smiley face button and another window opens that reads “Assign Macro”
Find your “MyDate” macro and double click and your done.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
July 23, 2009 at 09:22:07
Or:

ALT-F11 to open the VBA options

Insert / module

sub getnowdatetime()
selection.formula = now()
selection.offset(1,0).select
end sub

Then ALT-F8 to bring up the list of macros.

Highlight getnowdatetime and click options...
Assign a shortcut key (I use ctrl-d).

From then on, every time you hit ctrl-d you will replace the contents of the currently selected cell with NOW(), but just the value (not the formula)


Report •

#5
July 23, 2009 at 12:33:50
TERRIFFIC! JON K!

It worked EXACTLY as you instructed and I followed what you were telling me.

I can now use the same procedure to do other tasks.

You can't possibly know how helpful this is going to be.

Many - many - many thanks!


Report •

#6
July 23, 2009 at 12:39:55
Mike:

Everything worked down to:

On the Task Bar
Select Tools,
Select Customize

In the small window, under “Categories”,

THERE WAS NO SMALL WINDOW under “Categories”,

THERE WAS NO “Categories”, anywhere that I could find.

FYI this is Excell 2000 (9.0.2720) if that makes a difference.

I looked & looked, but could not continue with “Categories”, or the small window, under “Categories”.

Got any ideas where to go from here?


Report •

#7
July 23, 2009 at 14:21:42
When you click on Customize,
Another window should open with the heading Customize
There should be three Tabs, Toolbar, Commands, and Options
I left out to select the Commands Tab
Then you will see the Categories list.

Sorry.

MIKE

http://www.skeptic.com/


Report •

#8
July 23, 2009 at 17:47:52
OK... Now THAT works!

Didn't know about that COMMANDS - MACRO - ... jump...

(I flunked out that MIND-READING 101 class... :-) )

Seriously, I can understand when you are trying to recall all the steps from memory, some get overlooked.

It was very much worthwhile going through it by your steps as it taught me what's involved and another way to do it. Better to know TWO ways to do something than only one. It opens the mind to the bigger picture of MACROS.

I would like to thank EVERYONE that took the time to help me out here. I have been using Excel for about 100 years (well OK maybe just 30+) but never got into MACROS. I can see many uses for them now.

My very best to all of you here.

Theo


Report •

#9
July 26, 2009 at 18:57:44
Or...

Right click the sheet tab and choose View Code.

Paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Column = 1 And Target = "" Then Target = Now()
End Sub

Whenever you select any empty cell in Column 1 it will place the current date and time in that cell.


Report •

#10
August 3, 2009 at 00:42:56
Thanks for your posts. They are really very cool, very wonderful

[url=http://globolstaff.com/][color=#CDCDCD][u]keyword[/u][/color][/url]


Report •

#11
August 3, 2009 at 11:40:11
TO: DerbyDad03
Date: July 26, 2009

Yet ANOTHER way to do something....

GOOD GREIF I am learning more now than I have in the past 30 years!!!

Many thanks DerbyDad03


Report •

#12
August 3, 2009 at 12:08:23
Well, you did entitle your post "1 Click Data entry" and that's what the SelectionChange code will give you. <g>

Plus, if you don't want the date in the cell you selected, all you have to do is start typing just like you would in any empty cell.


Report •

#13
August 4, 2009 at 14:01:34
I am [IN FACT] the theoNeth that started this thing...It seems there is someone who knows more about computer network security than I and he came in last night and took me out.

So until I am able to get back on that system, I am without the login/pwd for this forum.

TO: DerbyDad03

The 1st two solutions, I was able to follow along and in fact, I have changed things to do "something completely different!".

Your solution takes me deep into unexplored territory. you stated: "Or... Right click the sheet tab and choose View Code. Paste this in:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 And Target = "" Then Target = Now()
End Sub

Whenever you select any empty cell in Column 1 it will place the current date and time in that cell."

That's -not exactly- what I got.

Unfortunately, I cannot paste the .xls file cells here, but NOTHING happens in COL A. ALL ROWS and ALL COLS B to ZZZ show NOW() when selected.

I did some reading and what I have run into at first look is the admonition in CH 7 pg 157 (MS Excel 2000 Power Prog. w/ VBA) which alludes to "CELLS property in RANGE OBJECT" ... "...is -RELATIVE- to the UPPER LEFT cell..."

This COULD indicate that COL #s are not COL A = 1, COL B=2... etc but rather COL A=0, COL B=1... and your use of "1" would mean that this code would not apply to COL 0. That's why nothing shows NOW() in COL A.

But I don't understand why -all- other cols are "with code". Does this mean if you used "If Target.Column = 0" instead of "If Target.Column = 1" , then ALL cols would produce NOW()?

I don't mean to draw you into a full lecture of RANGE - TARGET and their PROPERTIES, so if you can come up with a "quickie" that explains how this code of yours works, and then send me off on my way.

I would very much appreciate it.

Theo (the 100 year-old student)


Report •

#14
August 4, 2009 at 15:22:11
First, let me state that I have tested my code in Excel 2003 only, but I don't believe that it should act any differently in any other version.

re: "CELLS property in RANGE OBJECT" ... "...is -RELATIVE- to the UPPER LEFT cell..."

The Cells Property that Mr. Walkenbach is referring to in this section is not related to the code I offered. The Cells property can be used to refer to a cell by number, and that number is relative to the upper left cell of the range being used.

For example,
- Cells(1) of B2:C3 is B2, the upper left cell in the range
- Cells(2) is C2, one cell to the right
- Cells(3) is B3, etc.

Simply start in the upper left corner of the range and count to your right until you get to the edge of the range and then go down one row and all the way over to the left.

Interestingly enough, Cells(5) of B2:C3 will return B4 even though it is outside of the specified range. Excel will just keep counting cells, relative to the upper left cell but only as wide as range object that was specified.

Run this to see a real life example:

Sub CellsProp()
 MsgBox "Cells(1) of B2:C3 is: " & Range("B2:C3").Cells(1).Address
 MsgBox "Cells(2) of B2:C3 is: " & Range("B2:C3").Cells(2).Address
 MsgBox "Cells(3) of B2:C3 is: " & Range("B2:C3").Cells(3).Address
 MsgBox "Cells(5) of B2:C3 is: " & Range("B2:C3").Cells(5).Address
End Sub

So, again, that has nothing to do with the Selection_Change code that I offered.

That said, I have no idea what is going on with your spreadsheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Column = 1 And Target = "" Then Target = Now()
End Sub

This code monitors the user's cell selection and checks for 2 things:

Is the selected cell in Column 1 (A)?
Is the selected cell empty?

If both of these conditions are met, it puts the current value of NOW() in the target (selected) cell only. Not the formula =NOW() and not the words NOW(). If I ran it right now, it would put 08/04/2009 6:10:39 PM in the cell.

In fact, it is so rudimentary, it will error out if you select more than one cell at a time, so there is no way it can write NOW() (or it's value) into any more than 1 cell. However, if you have some other code running that gets triggered by a change in Column A, that's not something I would know about.

May I suggest you start with a brand new workbook, paste my code into the Sheet1 module and then select a single cell in Sheet1!Column A. It should put the current date and time in the cell.

Next, select another cell in Column A, type something over the date and time that appears and then select a cell in any other column. Your data should show in the cell in Column A and nothing should happen in the newly selected cell, or any other cell. Now go back and select the cell you just entered data into. Nothing should happen since the cell isn't empty.

Next select more than 1 cell anywhere in the workbook and the code should throw up a Type Mismatch error. Note: If you click End, the code will be ready to run the next time you select a cell. If you click Debug, it will take you into the VBA editor and the line that caused the error will be highlighted. You'll need to stop the code before you can run it again.

If anything other that what I described above happens when you run the code in a new workbook, I will be very surprised.


Report •

#15
August 5, 2009 at 10:38:39
TO: DerbyDad03

Because I had run all the above code examples in one workbook (and many more learning variations for me), I also wondered if some of that code had somehow corrupted your code example proper functioning. (altho I had put your code on a separate sheet.)

So I started a new workbook and just copied in your code.

Same results. Every cell but those in ROW A would give "NOW()" but none in ROW A would.
Then when I got an ERROR, I ran DEBUG and your line was HIGHLIGHTED:

After I stared at it for some time, I realized your code:

...
If Target.Column = 1 And Target = "" Then Target = Now()
...

Had become:
...
If Target.Column - 1 And Target = "" Then Target = Now()
...

AHHA!

The = had become - !

I -ALWAYS- COPY-C PASTE-V code so I don't make an error. This time it seems I thought your example was so simple that I understood it and just entered it by hand. And the - key is right next to the = key and there is no obvious syntax error that would be noted...

Mea -ABSOLUTELY- culpa!

[We THINK we learn, but sometimes we don't.]

Ctrl-C and Ctrl-V are there for a very good purpose.

Many thanks for your palliated response.

Theo


Report •

#16
August 5, 2009 at 12:18:49
I'm glad you solved your problem.

Now, for the next step:

Modify the code as follows to eliminate the error caused by selecting more than one cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Cells.Count > 1 Then Exit Sub
 If Target.Column = 1 And Target = "" Then Target = Now()
End Sub

P.S.

At first I was a little confused by your explanation because the "error" didn't seem like it would cause the symptoms I thought you were describing earlier.

When you originally described the problem you said:

"ALL ROWS and ALL COLS B to ZZZ show NOW() when selected."

I took that to mean that every cell from B1 to ZZZ(LastRow) ended up with the string NOW() in it - all at the same time - whenever you selected a cell in Column A.

I pictured a totally filled spreadsheet that looked like this after one click in Column A:

	 B	 C	...	ZZZ
   1	NOW()	NOW()	NOW()	NOW()
   2	NOW()	NOW()	NOW()	NOW()
   3	NOW()	NOW()	NOW()	NOW()
   4	NOW()	NOW()	NOW()	NOW()
   5  	NOW()	NOW()	NOW()	NOW()
   6	NOW()	NOW()	NOW()	NOW()
   7	NOW()	NOW()	NOW()	NOW()
  ...	NOW()	NOW()	NOW()	NOW()
LastRow	NOW()	NOW()	NOW()	NOW()

I now see (after some testing) that you meant the value for NOW() was showing up in each cell, when selected, except for those in Column A.



Report •

#17
August 10, 2009 at 13:33:31
For DerbyDad03:

*****'''Your last''*****
Now, for the next step:

Modify the code as follows to eliminate the error caused by selecting more than one cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 1 And Target = "" Then Target = Now()
End Sub

***''MY NEW''***
Yes that code works. Pretty hard to ignore:

If Target.Cells.Count > 1 Then Exit Sub

*****'''Your last''*****
At first I was a little confused by ...

When you originally described the problem you said:

"ALL ROWS and ALL COLS B to ZZZ show NOW() when selected."

I now see (after some testing) that you meant the value for NOW() was showing up in each cell, when selected,
except for those in Column A.

***''MY NEW''***
Yes I -do- know I should have said "VALUE of NOW()" instead of just "NOW()" but.... here's -my- but... I have been
removed for some time from the company of those programmers/coders who deal in the explicit.

My time away from programming (last was VB v.6) and its discipline has allowed my language to become very sloppy.

That is something just not tolerated in the mathematical/ syntactical world of programming. (I honestly miss it). But I am 70 now and my world no longer includes the academic or profession I left behind. What I do now uses computers to
facilitate my Projects of Interest to keep me out of a "Vegetable Home". So long as my brain can come up with projects and have the capability to follow them thru, I enjoy the thrill of accomplishments. And these small ones here are large in my life. Having looked askance at "macros" for all these years, I can now count on them as valuable friends. Learning at 70 is not what they do in those "Vegetable Homes".

I am already using the original code sought after here. Modified it, and adapted it. This new idea of applying it <globally> (probably wrong syntax) in one step saves applying manually to a column. Another step up!

Hey, enough going on and on, I am sure your time is at least as valuable as mine and you have better things to do than
hold lectures in vb4excel. So do you take personal checks, MasterCard or prefer cash in a plain brown wrapper for these
lectures? :-)

Seriously, your words here have the imprimatur of an exemplary Teacher.

My very sincere appreciations for your help.

Theo.


Report •

#18
August 10, 2009 at 18:36:44
Once again, glad to have been of assistance.

Thanks for the kind words.


Report •


Ask Question