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" <(E-Mail Removed)> wrote in message
news:0D294E20-48B9-4803-AED8-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:0D294E20-48B9-4803-AED8-(E-Mail Removed)...
> > 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("A150").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" <(E-Mail Removed)> wrote in message
> news:24496D66-A39C-4F0A-ADF3-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:0D294E20-48B9-4803-AED8-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
news:6072EB7F-F4B3-4C0D-A912-(E-Mail Removed)...
> 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("A150").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" <(E-Mail Removed)> wrote in message
>> news:24496D66-A39C-4F0A-ADF3-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:0D294E20-48B9-4803-AED8-(E-Mail Removed)...
>> >> > 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" <(E-Mail Removed)> wrote in message
news:2d_vf.15538$(E-Mail Removed)...
> 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
>