Exce small program

  • Thread starter Thread starter Texasowl
  • Start date Start date
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 .
 
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 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
(e-mail address removed)
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
 
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
 
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
 
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
 
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
(e-mail address removed)
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
 
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
 
Back
Top