write macro to input data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Macro Help - 28 Dec 2005 19:19 GMT

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.

Reply to this Message

Don Guillett - 28 Dec 2005 20:39 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>I am new at Excel. I want to write a small macro, four or five columns
>where
[quoted text clipped - 4 lines]
> experts
> in Excel. Thanks.

Reply to this Message

VBAvirgin - 29 Dec 2005 22:50 GMT

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.

Signature

VBAvirgin

Reply to this Message

Dave Peterson - 29 Dec 2005 23:48 GMT

How about not using a macro?

Data|form might be enough???

And if you want to try creating your own userform, Debra Dalgleish has some
getstarted instructions at:
http://contextures.com/xlUserForm01.html

> 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.

Signature

Dave Peterson

Reply to this Message

VBAvirgin - 30 Dec 2005 00:10 GMT

_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.

Signature

VBAvirgin

Reply to this Message