write macro to input data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new at Excel. I want to write a small macro, four or five columns where
I can go to column 1 and input data, then go to column 2 and input date,
etc., then when I get to the end of the fifth column have it return and go to
add more data in column 1. Any help will be appreciated from the experts
in Excel. Thanks.
 
one way to do this is to use a worksheet_change event
right click sheet tab>view code>copy/paste this>modify to suit>SAVE

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Row > 5 And Target.Column = 8 Then ActiveCell.Offset(1, -6).Select
End Sub
 
This is a basic method, the way I started, before getting into UserForms
etc.
Let's assume, you have some data in columns A thru E (5 columns).

Code:
--------------------
Sub NewData()
Dim Config As Integer
Dim Ans As Integer
Application.ScreenUpdating=False
Range("A1").End(xlDown).Select
'This inserts a value of 1 for tabulation. Use Only if you want to'
ActiveCell.Value=(1)
ActiveCell.Offset(rowOffset:=0,columnOffset:=1).Activate
ActiveCell.Value=InputBox("Enter Your Data")
ActiveCell.Offset(rowOffset:=0,columnOffset:=1).Activate
'Inserts the current date'
ActiveCell.Value=Now
ActiveCell.Offset(rowOffset:=0,columnOffset:=1).Activate
ActiveCell.Value=InputBox("Enter Your Data")
ActiveCell.Offset(rowOffset:=0,columnOffset:=1).Activate
ActiveCell.Value=InputBox("Enter Your Data")
ActiveCell.Offset(rowOffset:=0,columnOffset:=1).Activate
ActiveCell.Value=InputBox("Enter Your Data")
'User is prompted to continue or quit'
Ans=MsgBox("Do You Have Additional Entries?", vbYesNo)
If Ans=vbYes Then
Application.Run("NewData")
End If
'Cursor is returned to the Home Cell of A1'
Application.Range("A1").Select
End Sub

--------------------


Hope this helps you some. I know VBA can be a little initimidating when
you first get into it.
Good Luck in your efforts.
 
_I_whole-heartedly_agree_ with D. Peterson's comments. Userforms are
definitely the neatest and cleanest way to go.

I was just imagining, from your initial question...(asking for a
macro)...that you may be familiar with some older or different programs
and the macros used within them, such as Lotus 123, or CA SuperCalc, or
QuattroPro (which is what I used last, up until September of 2004).
I'm by no means a programmer of any sorts, but I have now created
userforms, along with my macros, and I'm at a fairly comfortable level,
although I'll be the first to admit there's so much farther to go, but
my point is, the learning curve is only as difficult as you make it, so
hang in there.
It will take some time getting your head around VBA, but I'm sure
you'll do it.

A big help to me has been: "Excel VBA Programming for Dummies"...John
Walkenbach. Wiley Publishing.
That...and the help, such as I have received, from the experts on this
forum.
Good Luck.
 
Back
Top