is there a way to enter data interactively and dynamically?

[cfman]

Hi all,

Can I enter data interactively and dynamically?

To give an example:

Approach 1: Currently, when I enter grades for students, I have to find
his/her name first, after finding that row, I have to scroll the row to the
far right, since there are so many columns already there. Let's suppose
that I scroll to column 32, and it is often times hard to remain on the same
row(then I ended up entering a wrong score for this student ...) ; suppose I
scrolled successfully with my eyes staring at that row, and then I enter
score. And so on and so forth for the next student, etc. The whole process
is quite tedious and time-consuming and error prone.

Is there a data entry program in Excel that can help me with this situation?

Approach 2: I imagine a pop-up window, with auto-completion feature, I just
need to type the first a few letters of students' name, after only 3-4
key-strokes, it should find the right person, then I enter the score in a
place in this pop-up window, and then I hit ENTER the pop-up window save the
score data and prompts for my entry for the next student, so on and so
forth...
Finally the data will be stored automatically to the 32th column or any
other column I specify...

Then I don't have to scroll all the time...

 

[Guest]

Have a look at Data/Forms!

Regards,
Stefi


„cfman†ezt írta:

 

[Otto Moehrbach]

See if this fits with what you have in mind. Imagine that you have several
cells, left to right, on some otherwise blank sheet. There would be headers
above these cells to identify each cell, like Name, Grade, etc,.
The Name cell would be a Data Validation cell. When you click on that cell,
a drop-down arrow appears to the right of that cell. Click on the down
arrow and you see all the names listed. Click on the name you want. Then
enter the grade and other things in the other cells. Finally, there would
be a button above/below/next to these cells. Click on that button and all
the data would be placed where you want it. No scrolling. No hunting for
the right cell. No changing of sheets. Post back if you think this would
be of help to you. HTH Otto

 

[jkend69315]

cf, I created a file with a userform to handle your problem. Works
well and seems to meet your requirements. If you're interested, reply
and we'll see if we can arrange a reasonably secure way to pass fhe
file. Or, I can try to walk you through creating the userform. James

 

[cfman]

See if this fits with what you have in mind. Imagine that you have
several cells, left to right, on some otherwise blank sheet. There would
be headers above these cells to identify each cell, like Name, Grade,
etc,.
The Name cell would be a Data Validation cell. When you click on that
cell, a drop-down arrow appears to the right of that cell. Click on the
down arrow and you see all the names listed. Click on the name you want.
Then enter the grade and other things in the other cells. Finally, there
would be a button above/below/next to these cells. Click on that button
and all the data would be placed where you want it. No scrolling. No
hunting for the right cell. No changing of sheets. Post back if you
think this would be of help to you. HTH Otto

Hey, Otto,

This is almost there. But not completely yet...

-> "Click on the down arrow and you see all the names listed. Click on the
name you want. "

This will be horribly time consuming thinking about I have 200+ students to
enter grades...

I cannot go by searching the names by my eye;

I have to use some kind of auto-completion feature -- I type the first a few
letters of the students name and the program locate the student for me.

-> "No scrolling. No hunting for the right cell. "

Exactly, who wants to play with the cells? That's a terrible UI interface.

All I want is to enter a bunch of scores associated with the name, and then
once I am done, I specify which column of Excel should all these scores go
into. That's all. It can be Test1, Test2, Test3, etc.

----------------------------------------

The best GUI I think it should be:

1. I specify a column in Excel from which the program obtain the names;

2. The program keeps popping up a window, asking me to type the name, by
auto-completion, I just need to type a few letters, and it should be fast
for me to locate the person from out of the 200+ students I am having;

3. Then I enter a score to be associated with that student;

4. Repeat 2 and 3;

5. After the above is done, I specify a column in Excel to which the scores
are stored...

-------------------------------------

Do you know a way of doing this? Currently searching and entering scores
kills me...

Thanks a lot!

 

[cfman]

cf, I created a file with a userform to handle your problem. Works
well and seems to meet your requirements. If you're interested, reply
and we'll see if we can arrange a reasonably secure way to pass fhe
file. Or, I can try to walk you through creating the userform. James

Wow, you've already got one? it can be done in Excel? I don't have to
program in other language to do it? That's great!

The userform can do the following?

-----------------------------------------------------------------

-> "Click on the down arrow and you see all the names listed. Click on the
name you want. "

This will be horribly time consuming thinking about I have 200+ students to
enter grades...

I cannot go by searching the names by my eye;

I have to use some kind of auto-completion feature -- I type the first a few
letters of the students name and the program locate the student for me.

-> "No scrolling. No hunting for the right cell. "

Exactly, who wants to play with the cells? That's a terrible UI interface.

All I want is to enter a bunch of scores associated with the name, and then
once I am done, I specify which column of Excel should all these scores go
into. That's all. It can be Test1, Test2, Test3, etc.

----------------------------------------

The best GUI I think it should be:

1. I specify a column in Excel from which the program obtain the names;

2. The program keeps popping up a window, asking me to type the name, by
auto-completion, I just need to type a few letters, and it should be fast
for me to locate the person from out of the 200+ students I am having;

3. Then I enter a score to be associated with that student;

4. Repeat 2 and 3;

5. After the above is done, I specify a column in Excel to which the scores
are stored...

-------------------------------------

Do you know a way of doing this? Currently searching and entering scores
kills me...

Thanks a lot!

 

[jkend69315]

cf, My little file just assumes the Student names are in column A. It
could be taught to use a different column to find the names, if
necessary. You type in the first few letters of the name and it finds
it. Then you tab to the grade box and put in the grade. You set the
column in the form to put the scores in, and it just keeps using that
column until you change it in the form. So it would be a matter of
typing in a few letters, tab, put in grade, press OK and repeat until
all grades are in. Easy. Yes, it's an Excel file, using only Excel
VBA. James

 

[cfman]

cf, My little file just assumes the Student names are in column A. It
could be taught to use a different column to find the names, if
necessary. You type in the first few letters of the name and it finds
it. Then you tab to the grade box and put in the grade. You set the
column in the form to put the scores in, and it just keeps using that
column until you change it in the form. So it would be a matter of
typing in a few letters, tab, put in grade, press OK and repeat until
all grades are in. Easy. Yes, it's an Excel file, using only Excel
VBA. James

Wonderful! It is the smart people such as James in the world that makes our
lives easier and not a myserable one...

Thank you so much James...

Could you please kindly send me the program to my email address:
(e-mail address removed)?

I appreciate your help!

 

[jkend69315]

cf, Glad to be of help. I set up a savefile account to pass files, so
that would be the best way to get it to you. The address is
http://www.savefile.com/files/148073
Just copy that address and paste it in your browser address window.
When you get to the site, click the Download button.
After downloading, open the file and click the little Go button or
press Ctrl-z. Type in a few letters of one of the names to locate a
student's row. Tab to go to the grade box. Type in grade. Tab to go
to the column box. You can enter the column letter or the column
number. Press OK to enter the grade. If you think you made a mistake,
press the < button to go back (it only remembers the last one).
Make a backup copy of your file before trying to hook it up to this.
Let me know if it works for you and if you need help in hooking it up
to your file. James

 

[jkend69315]

cf, obviously I didn't realize the savefile address would show up as a
link, so just click on the link! James

 

[cfman]

cf, Glad to be of help. I set up a savefile account to pass files, so
that would be the best way to get it to you. The address is
http://www.savefile.com/files/148073
Just copy that address and paste it in your browser address window.
When you get to the site, click the Download button.
After downloading, open the file and click the little Go button or
press Ctrl-z. Type in a few letters of one of the names to locate a
student's row. Tab to go to the grade box. Type in grade. Tab to go
to the column box. You can enter the column letter or the column
number. Press OK to enter the grade. If you think you made a mistake,
press the < button to go back (it only remembers the last one).
Make a backup copy of your file before trying to hook it up to this.
Let me know if it works for you and if you need help in hooking it up
to your file. James

James,

That's super! Thank you so much!

I have tried the program and I like it very much!

One question: -- I have several existing Excel sheets, with 200 names and
previous scores in them, how do I import the MACRO in your worksheet into my
existing worksheets?

Thanks a lot!

 

[jkend69315]

cf, Hooking this up to your existing files should be relatively easy.
A couple of questions:
1. The routine assumes that the students' names are in column A and
that all the remaining columns are available for grades. Is that
right?
2. Are you using column headings in row 1 for the individual grade
columns, such as "Test 9/30/06" that it would be easier to refer to in
order to set the grade column instead of just setting the column number
or letter?
Let me know and we'll proceed.