Exce small program

T

Texasowl

I have a 3 column spread sheet. The first column is a consistent letter. The
second column is a numerical value inputted by students. The third column is
the =NOW() command.
What I want to happen is after the student inputs their number and hit's
enter, I want the NOW command to execute and the cursor to return the second
column for the next student input.

I know how to have the cursor move from either the next column cell or the
next down cell. In either case the NOW command is a must to execute without
having to rely upon the students input.

Any, any help would be greatly appreciated.

Thank you .
 
D

Don Guillett

Right click sheet tab>view code>insert this
Now when you enter something in col b now will be in col c and the macro
asks what to put in col D

Private Sub Worksheet_change(ByVal Target As Range)
If Intersect(Target, Columns("b")) Is Nothing Then Exit Sub
Target.Offset(, 1) = Now()
Target.Offset(, 2) = InputBox("enter more data for col D")
End Sub
 
T

Texasowl

Don Guillett said:
Right click sheet tab>view code>insert this
Now when you enter something in col b now will be in col c and the macro
asks what to put in col D

Private Sub Worksheet_change(ByVal Target As Range)
If Intersect(Target, Columns("b")) Is Nothing Then Exit Sub
Target.Offset(, 1) = Now()
Target.Offset(, 2) = InputBox("enter more data for col D")
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Click to expand...

Don,
Thank you very much for the help. One small problem, instead of the cursor
going back to the "B" column, it is going to "D". Is there a fix for this?

Thanks,

Dan
 
T

Texasowl

Texasowl said:
Don,
Thank you very much for the help. One small problem, instead of the cursor
going back to the "B" column, it is going to "D". Is there a fix for this?

Thanks,

Dan
Click to expand...


Don,
I was able to figure out how to make it go to the next line and back into
the "B" column. What I can not do now is to get the program to stop asking
for an input. If I hit either the ok, or the cancel with the input blank, it
enters the NOW and then goes back to the "B" column.
Now I really can use some help on this.

Dan
 
D

Don Guillett

Private Sub Worksheet_change(ByVal Target As Range)
If Intersect(Target, Columns("c")) _
Is Nothing Then Exit Sub
Target.Offset(, 1) = Now()
'Target.Offset(, 2) = InputBox("entermoredata")
ans = InputBox("entermoredata")
If ans = "" Then Exit Sub
Target.Offset(1) = ans
End Sub
 
E

EricG

Perhaps this change to Don's code?

Private Sub Worksheet_change(ByVal Target As Range)
Dim inputChk
'
inputChk = Inputbox("Enter more data for Col D")
'
If Intersect(Target, Columns("b")) Is Nothing Then Exit Sub
Target.Offset(, 1) = Now()
If inputChk = "" Then Exit Sub
Target.Offset(, 2) = InputBox("enter more data for col D")
End Sub
 
T

Texasowl

Don Guillett said:
Private Sub Worksheet_change(ByVal Target As Range)
If Intersect(Target, Columns("c")) _
Is Nothing Then Exit Sub
Target.Offset(, 1) = Now()
'Target.Offset(, 2) = InputBox("entermoredata")
ans = InputBox("entermoredata")
If ans = "" Then Exit Sub
Target.Offset(1) = ans
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Click to expand...

Don and Eric,
Thank you both for your help and my apologies for not getting back to you
sooner.

It seems that Don's first input worked and I was just mis-reading what I was
looking at.

What I need to know now is if there is a command that can be used to force
the second column to be nothing more then a numerical input. I checked it by
typing in alpha and it seems that they can also be inputted.

Thanks,

Dan
 
D

Don Guillett

try

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("c")) _
Is Nothing Then Exit Sub
Target.Offset(, 1) = Now()
'Target.Offset(, 2) = InputBox("entermoredata")
ans = InputBox("entermoredata")
'If ans = "" Then Exit Sub
If Not IsNumeric(ans) Then Exit Sub
Target.Offset(, 2) = ans
'Target.Offset(1) = ans
End Sub
 
Top