Data input form

B

Barney

I have 20 cells values that must be input to a row 128 times within about a
half hour to 45 minutes. I need to do this once each month. In Excel 97
there was a 'data input' form that could be used. I cannot find that in
Excel 2002.

Any advice would be appreciated since I think it would really help speed
things up.

Barney
 
G

Guest

Are you looking for....

Select your data range, including col headings.

From the Excel main menu:
Data>Form

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
B

Barney

Actually, I was thinking of a form that only allowed one input at a time.
In Excel 97 I clicked on a button and the form came up and asked for one at
a time. Number 1, number 2, etc.

I can send a copy of that spreadsheet if it would help explain what I am
saying.

Thanks for your help,

Barney
 
G

Guest

Well, it finally happened...my brain must have filled up and dumped some
data! I have no recollection of that functionality in Excel97. I remembered
Data>Form, though, so I'm happy about that.

Could you describe in a little more detail what you want to see? Maybe,
it'll jog my (or someone else's) memory.

***********
Regards,
Ron

XL2002, WinXP-Pro
 
G

Guest

Well, that certainly was necessary information. I thought you were talking
about native Excel97 functionality...not a custom recorded macro!

OK...As long as your workbook has sheets named:
Database
Frequency
Statistics
SortSheet

the code will work. I have no idea what should be on those sheets, though.

Are you having trouble getting the code to run?

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


Barney said:
Ron,

In '97' I had a lottery spreadsheet for tracking the numbers twice a week.
I would click on a form button which would bring up a dialog asking to input
the numbers. It would ask for the first number (see attached) which I would
input, hit Okay and it would put that number in the proper cell and then ask
for "lottery number two" etc.

Here is the VB macro code that the form button would run.....

'
' LottoNumbers Macro
' Macro recorded 3/19/97 by Barney Poston
'
'
Sub LottoNumbers()
Sheets("Database").Select
Range("L9").Select
Selection.EntireRow.Insert
[L9] = [L10] + 1
[M9] = [M11] + 7
Range("N9").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter 1st Lottery Number.")
Range("O9").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter 2nd Lottery Number.")
Range("P9").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter 3rd Lottery Number.")
Range("Q9").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter 4th Lottery Number.")
Range("R9").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter 5th Lottery Number.")
Range("S9").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter 6th Lottery Number.")
Sheets("Frequency").Select
Cells.Replace What:="$N$10", Replacement:="$N$9", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="$S$34", Replacement:="$S$33", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="$S$59", Replacement:="$S$58", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="$S$109", Replacement:="$S$108", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Sheets("Statistics").Select
Cells.Replace What:="$L$10", Replacement:="$L$9", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Sheets("Statistics").Select
Range("B4:E53").Select
Selection.Copy
Sheets("SortSheet").Select
ActiveWindow.LargeScroll Down:=-2
Range("A1").Select
ActiveSheet.Paste
Range("A1:D50").Select
Selection.Sort Key1:=Range("D1"), Order1:=xlDescending, Key2:= _
Range("C1"), Order2:=xlDescending, Key3:=Range("B1"), Order3 _
:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Sheets("Statistics").Select
Range("B4:F53").Select
Selection.Copy
Sheets("SortSheet").Select
Range("F1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("J1"), Order1:=xlDescending, Key2:= _
Range("I1"), Order2:=xlDescending, Key3:=Range("H1"), Order3 _
:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Sheets("Database").Select
Range("L9").Select
ActiveWorkbook.Save
End Sub

Hope some of this helps.

Barney


Ron Coderre said:
Well, it finally happened...my brain must have filled up and dumped some
data! I have no recollection of that functionality in Excel97. I
remembered
Data>Form, though, so I'm happy about that.

Could you describe in a little more detail what you want to see? Maybe,
it'll jog my (or someone else's) memory.

***********
Regards,
Ron

XL2002, WinXP-Pro
 
B

Barney

I am designing a new sheet that needs a similar box. I know nothing about
VB so I am not sure how to make a recorded macro bring up such a box.

Barney


Ron Coderre said:
Well, that certainly was necessary information. I thought you were talking
about native Excel97 functionality...not a custom recorded macro!

OK...As long as your workbook has sheets named:
Database
Frequency
Statistics
SortSheet

the code will work. I have no idea what should be on those sheets, though.

Are you having trouble getting the code to run?

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


Barney said:
Ron,

In '97' I had a lottery spreadsheet for tracking the numbers twice a
week.
I would click on a form button which would bring up a dialog asking to
input
the numbers. It would ask for the first number (see attached) which I
would
input, hit Okay and it would put that number in the proper cell and then
ask
for "lottery number two" etc.

Here is the VB macro code that the form button would run.....

'
' LottoNumbers Macro
' Macro recorded 3/19/97 by Barney Poston
'
'
Sub LottoNumbers()
Sheets("Database").Select
Range("L9").Select
Selection.EntireRow.Insert
[L9] = [L10] + 1
[M9] = [M11] + 7
Range("N9").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter 1st Lottery Number.")
Range("O9").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter 2nd Lottery Number.")
Range("P9").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter 3rd Lottery Number.")
Range("Q9").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter 4th Lottery Number.")
Range("R9").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter 5th Lottery Number.")
Range("S9").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter 6th Lottery Number.")
Sheets("Frequency").Select
Cells.Replace What:="$N$10", Replacement:="$N$9", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="$S$34", Replacement:="$S$33", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="$S$59", Replacement:="$S$58", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Replace What:="$S$109", Replacement:="$S$108", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Sheets("Statistics").Select
Cells.Replace What:="$L$10", Replacement:="$L$9", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Sheets("Statistics").Select
Range("B4:E53").Select
Selection.Copy
Sheets("SortSheet").Select
ActiveWindow.LargeScroll Down:=-2
Range("A1").Select
ActiveSheet.Paste
Range("A1:D50").Select
Selection.Sort Key1:=Range("D1"), Order1:=xlDescending, Key2:= _
Range("C1"), Order2:=xlDescending, Key3:=Range("B1"), Order3 _
:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Sheets("Statistics").Select
Range("B4:F53").Select
Selection.Copy
Sheets("SortSheet").Select
Range("F1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("J1"), Order1:=xlDescending, Key2:= _
Range("I1"), Order2:=xlDescending, Key3:=Range("H1"), Order3 _
:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Sheets("Database").Select
Range("L9").Select
ActiveWorkbook.Save
End Sub

Hope some of this helps.

Barney


Ron Coderre said:
Well, it finally happened...my brain must have filled up and dumped
some
data! I have no recollection of that functionality in Excel97. I
remembered
Data>Form, though, so I'm happy about that.

Could you describe in a little more detail what you want to see? Maybe,
it'll jog my (or someone else's) memory.

***********
Regards,
Ron

XL2002, WinXP-Pro


:

Actually, I was thinking of a form that only allowed one input at a
time.
In Excel 97 I clicked on a button and the form came up and asked for
one
at
a time. Number 1, number 2, etc.

I can send a copy of that spreadsheet if it would help explain what I
am
saying.

Thanks for your help,

Barney

Are you looking for....

Select your data range, including col headings.

From the Excel main menu:
Data>Form

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


:

I have 20 cells values that must be input to a row 128 times within
about
a
half hour to 45 minutes. I need to do this once each month. In
Excel
97
there was a 'data input' form that could be used. I cannot find
that
in
Excel 2002.

Any advice would be appreciated since I think it would really help
speed
things up.

Barney
 
B

Barney

Ron,

I was able to copy and paste that portion of the macro from my old
spreadsheet into a macro in my new spreadsheet. Then by changing the cell
references and the text in the input boxes everything is working just fine.

What I still don't understand is how I acquired that input box in my old
spreadsheet because, as I said, I know very little about VB.

Thanks for your help on this. Sorry I wasn't clearer in the first place.

Barney
 
R

Roger Govier

Hi Barney

The input boxes came from the 6 sets of code in your workbook like
Range("N9").Select
ActiveCell.FormulaR1C1 = _
InputBox("Enter 1st Lottery Number.")

The Input Box displays on the screen waiting for you to enter the first
number etc.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top