Data input form

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

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


"Barney" wrote:

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

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

"Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
news:0D294E20-48B9-4803-AED8-744C9E64DD0C@microsoft.com...
> 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
>
>
> "Barney" wrote:
>
>> 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
>>
>>
>>

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


"Barney" wrote:

> 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
>
> "Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
> news:0D294E20-48B9-4803-AED8-744C9E64DD0C@microsoft.com...
> > 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
> >
> >
> > "Barney" wrote:
> >
> >> 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
> >>
> >>
> >>
>
>
>

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" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
news:24496D66-A39C-4F0A-ADF3-A800E3479D46@microsoft.com...
> 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
>
>
> "Barney" wrote:
>
>> 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
>>
>> "Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
>> news:0D294E20-48B9-4803-AED8-744C9E64DD0C@microsoft.com...
>> > 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
>> >
>> >
>> > "Barney" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>
>>
>>
>>

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" wrote:

> 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" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
> news:24496D66-A39C-4F0A-ADF3-A800E3479D46@microsoft.com...
> > 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
> >
> >
> > "Barney" wrote:
> >
> >> 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
> >>
> >> "Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
> >> news:0D294E20-48B9-4803-AED8-744C9E64DD0C@microsoft.com...
> >> > 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
> >> >
> >> >
> >> > "Barney" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
news:6072EB7F-F4B3-4C0D-A912-B8315CB24FA2@microsoft.com...
> 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" wrote:
>
>> 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" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
>> news:24496D66-A39C-4F0A-ADF3-A800E3479D46@microsoft.com...
>> > 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
>> >
>> >
>> > "Barney" wrote:
>> >
>> >> 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
>> >>
>> >> "Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
>> >> news:0D294E20-48B9-4803-AED8-744C9E64DD0C@microsoft.com...
>> >> > 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
>> >> >
>> >> >
>> >> > "Barney" wrote:
>> >> >
>> >> >> 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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

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

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.

--
Regards

Roger Govier


"Barney" <postonbl@yahoo.com> wrote in message
news:2d_vf.15538$0y2.902@bignews2.bellsouth.net...
> 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
>