Data Entry Macro

[Charles Reid]

Hello All,

I was wondering if someone could help me out with a data entry question. I
was wondering if it is possibe to launch a macro that will propmt a user to
enter in a their name, qty, and amount and enter that into a list at the
end.

Listed below is my example of my list. I would like to hit a button called
ADD and then have it ask to enter NAME, QTY, AmT

Please enter Name: Hulk Hogan
Please enter QTY: 5
Please enter AMT: 6

Jerry Mouse 5 10
Mickey Mouse 2 3
Minny Mouse 2 4
Roger Rabbit 3 6
Bunny Rabbit 3 7
Space Ghost 3 8
Green Arrow 1 5
Bat Man 7 2
Super Man 1 1
Iron Man 2 2
Hulk Hogan 5 6

 

[Dave Peterson]

Maybe you could use Data|Form (xl2003 menu system).

If that's not sufficient, then maybe you could design your own input userform.

Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html

 

[carlo]

If you want to make a Userform, go to your VBA editor and add
a userform. there you make three textfields and a button.

on the button on the sheet you put the code:
'--------------------------------------------------------------------
myform.show
'--------------------------------------------------------------------
on the button you put following code:
'--------------------------------------------------------------------
dim last_row as integer

with worksheets("yoursheet")
last_row = .cells(65536,1).end(xlup).row + 1
..cells(last_row, 1) = me.textfield1
..cells(last_row, 2) = me.textfield2
..cells(last_row, 3) = me.textfield3
end with
unload me
'--------------------------------------------------------------------

you could do the same with three input-boxes.
on your button on the sheet you put this code directly:
'--------------------------------------------------------------------
dim last_row as integer

with worksheets("yoursheet")
last_row = .cells(65536,1).end(xlup).row + 1
..cells(last_row, 1) = inputbox("Please enter Name", "Name","Your
Name")
..cells(last_row, 2) = inputbox("Please enter QTY", "QTY","QTY")
..cells(last_row, 3) = inputbox("Please enter ATM", "AMT","AMT")
end with
'--------------------------------------------------------------------

hth

Carlo

 

[FSt1]

hi
sub adddata()
dim nam as string
dim qty as long
dim amt as long

nam = inputbox("Please enter your name.")
qty = inputbox("Please enter quantity")
amt = inputbox("please enter the amount.")

end sub.

this takes care of the input. where do you want the list?

Range("A65000").End(xlUp).Offset(1, 0).Value = nam
Range("B65000").End(xlUp).Offset(1, 0).Value = qty
Range("C65000").End(xlUp).Offset(1, 0).Value = amt

this code would start a list at A1 and move down as more input is done.

Regards
FSt1

 

[Gord Dibben]

Charles

Have you checked out Data>Form?

You can do the same thing there with three columns titled Name, QTY and AMT

Or all in one column if that's what you wish.


Gord Dibben MS Excel MVP