PDA

View Full Version : How to make an input form?



iMAN2
05-31-2009, 07:26 AM
Hii,

Sample Data and Output attached.

Input Data
I need to make an input form for easier data inputting. However im not sure how to do so, and what type of vba coding i will require.

The selected subjects must input the mark under the 'enter marks' sheet for that specfic subject etc.

The save button should enter this data into that 'enter marks' sheet and then clear the form area for the next entry. A clear button should be there just to clear all data in case a mistake in data entry is made or something.

Next


View Data
In the view data sheet a similar form like view, however by selecting the names as a merger of first name and last name in a list form (validation?), as stored in the 'moderated marks' sheet via the input form.

It should display the original mark, as in the sheet 'enter marks' and the moderated mark as in sheet 'moderated marks'.

The Next entry and previous entry button should go to the next entry and previous entry, however, if there is a blank entry, then it should skip it. Note even if a first name doesnt exist or a cell reads 'check' in the marks section they should still be displayed.

Thanks for all your help guyz :friends:

mdmackillop
05-31-2009, 08:22 AM
For Input, do you want all the subjects listed, against which marks will be inserted, or is it a case of pick a subject (drop down or list), enter a mark then save it, repeating for each. Either is readily accomplished.

mdmackillop
05-31-2009, 08:48 AM
Are you really working with Surname or First Name only? Things are simplified if regular data can be depended upon.

iMAN2
05-31-2009, 04:47 PM
Hi,
Both first name and last name are entered into the 'form' sheet, which transfers this data to the 'enter marks' sheet where first name and last name are also kept separatly.

In the form, for a name entry there should be 8 drop-down boxes of which you can enter a mark for. By clicking save/new entry button the name, this data should transfer to the 'enter marks' sheet under respective columns, such as first name, last name, and for the drop down lists, the mark for that respective subject.

If possible in the form, can you put a comment box, which transfers the data to the 'comment' column in the 'enter marks' sheet.

Thanks mate!

iMAN2
05-31-2009, 05:03 PM
It should force the user, however to put in a first name and last name. If either one is blank then error..?

iMAN2
05-31-2009, 11:43 PM
~Bumpidity~

Mr.G
06-01-2009, 05:04 AM
I've done this maybe you could build on it.....

iMAN2
06-01-2009, 06:13 AM
Hmm, need data to be entered under specfic format for the 'enter sheet'. Also subjects are needed as drop-down list selection instead of manually typing.

Thanks for your help though.

Mr.G
06-01-2009, 06:27 AM
One could make each subject a dropdown box?

iMAN2
06-01-2009, 06:29 AM
hmmz, yeap, but the main thing is the data to be transferred to the 'enter marks' worksheet for the specific subjects selected...:S

Mr.G
06-01-2009, 06:37 AM
mmm....why not put the Subjects in Column and then you could from right to left populate. Next to eg.Maths-Name then Marks,Name then marks ect.?

iMAN2
06-01-2009, 06:44 AM
Because these results have to be viewed, but more importantly exported, and with help i've been able to make it so a macro can export from the 'moderated marks' sheet. So whatever data i input through a form should output it into the 'enter marks' sheet in the current format.

Mr.G
06-01-2009, 06:51 AM
Ok.......I am out for now. I think this is over my head.

G'luck

Kenneth Hobs
06-01-2009, 12:55 PM
It would take some work but you could use a userform as I did in my xls. It also does an MSWord mailmerge. You can skip that part.

Obviously, you would need to change several things to meet your needs. Sounds like you want drop lists which is easily done with combobox controls.

http://www.4shared.com/file/109228521/e740bbfb/ElephantsRusDocs.html

iMAN2
06-01-2009, 10:54 PM
Hi Kenneth, that excel sheet is nearly everything i need. Just the main bits that i need different are, within the form have 8 drop-down menus for selecing the subject, of which you can enter a mark, and then store that mark under the respective subject heading. I have no idea how to do this however, and any help would be really great. Thankyou!

Mr.G
06-01-2009, 11:09 PM
That is a excellent document Kenneth....

iMAN2
06-02-2009, 02:11 AM
Would it be possible to apply the 'Kennith Hobbs' style form to this workbook (the formatting has been updated to make form data input easier).

Also, if possible can you prevent duplicate subjects for one entry.

Thanks

Kenneth Hobs
06-02-2009, 07:06 AM
It should be doable. I will work on a more specfic example. Since you want to not duplicate subjects, I would just code another userform with that that just shows the subjects to pick less those already picked.

I will have to reread what all you wanted. We can update the ENTER MARKS sheet or the Moderate Marks using a userform. I will remove the MailMerge feature. You can always add that sort of thing later if you like.

You could do away with column A in ENTER MARKS. e.g. B4 =IF(E4="","",Row()-3)

iMAN2
06-02-2009, 09:44 AM
Hmm actually keep the mail-merge feature, mite come in handy. data should be entered into a form which updates the 'enter' worksheet which is then linked to the 'moderation' worksheet (and moderates marks) Thanks

Kenneth Hobs
06-02-2009, 08:04 PM
Let's get your first goal completed before adding others.

This needs some more work but it might get you going. You will want to change the tab order.

iMAN2
06-02-2009, 08:45 PM
Nicee. Hmm wat does the rownow function do?
What did u mean by tab order?

I've made another input sheet which will have a second form with same function but more inputs.

In the form can u put besides each mark a box which shows the mark for that person as in the moderated marks sheet (as a non-editable box). So that way when using the form to view entries both enter mark and moderated mark can be viewed.

At the top of the form can u put the current year, as shown in the 'current year' sheet, not the computer clock.

I've attached an updated file, which contains the sheet called units. Is it possible for each subject as it selected to display the subject code beside the box and how many units its worth (next to it.)
Then at the bottom of the form a total of total units selected.
If at least one of the subjects doesnt contain the word 'english' then error.
If total units is less then 10 then error.

Also can u make it select subject first (left hand side) then mark (rhs).

Also if i insert more columns, then is it possible to somehow include those as well, or if u could tell me what to change/add if i insert new columns.
I know how to make new boxes in the form, but the coding for them i have no idea, so maybe a general code + changes i need to make if new columns are added.

Otherwise functioning seems quite smooth, what other modifications did u have in mind?


Thankyou so muchh!!! :bow:

iMAN2
06-03-2009, 05:35 AM
Latest and Final formatted sheet.

thanks Peter

Kenneth Hobs
06-03-2009, 06:31 AM
As you increase the number of things that you want it increases the work needed to achieve your goals. At some point, a custom userform may not meet all of the goals. Of course much of this is easier if it were done in Access.

I will try to answer some of your questions. It is a matter of time as to how much I can do the work for you. You should probably learn some userform basics from sites like: http://www.contextures.com/xlUserForm01.html

I designed the code to work from the sheet that it was played from. You can play macros with Alt+F8 or a command button. Use one from the Control Toolbox toolbar. After adding the button, you may need to click the first icon which toggles design mode. If you right click the button in design mode, it will have handles around it. Double click it to add code. You can also cut and paste the button from one sheet to another but you will need to add the VBA code. e.g.
Private Sub CommandButton2_Click()
frmEntry.Show vbModeless
End Sub

Regarding RowNow: Notice that I added vbModeless as a parameter after Show. This means that you can click outside of the userform. So, if you select say row 200 and click the RowNow button, it will set that userform's active record as row 200. Note that Add is needed to add any changes to the rowNow so set a new rowNow does not add the changes as it is coded.

We could add an exit event code to the rowNow textbox control to jump to that row too. Or, you can click the spin button a bunch of times. Another option is to use a scroll bar to quickly scroll through the records and set rowNow. See Data > Form, for an example sort of like what I did only it does not allow customization. John Walkenbach has a more advanced add-in like Data > Form.

Adding another textbox for the other sheet's mark is doable. However, the code is then sheet specfic. You will have to have row to row matching or some other criteria to match rownow with the row in the other sheet for that to work. Let's leave that for later.

Selecting subject first and then mark textboxes is a matter of tab order or tabindex as I explained. You may want to rearrange the controls and then set your tab order. To set tab order In VBE, doubleclick the frmEntry in Project Explorer. If not shown select it from the View menu. Press F4 or View > Properties Window, to view control properties. As you click the controls in the order that you want, change the tabindex number. The first tabindex is 0 so you will note that BOS Number has a tabindex of 0.

When you need to add more columns to the userform, post back but leave that for a later task. We can show you where these are added. If you doubleclick the Add and New buttons in the VBE, it will show that code for those buttons specifically.

The B buttom means Browse the Subject list and optionally pick one. One could use the Click event for a textbox control. I did not do that since some users like to jump around using the mouse rather than tab or enter. We can make B's frmSubject's listbox control not only the Subject but the subject code and the units. Rather than adding more textbox controls, I recommend this route. I would also recommend that you make your subject, subject code, and units list on one sheet rather than 2. This makes lookup more easy and reliable and without repetition. Most people would make the code as the first column and description in the 2nd column. They would sort those rows by the code column or field. This is not needed in my code since I used Find rather than a lookup function per se.

Much later in the project, you can add the mailmerge feature. You need to get these other issues resolved first. As I designed that first example, you can use AutoFilter or Advanced filter while the frmEntry is displayed. The merge would only merge those filtered rows on that activesheet.

iMAN2
06-03-2009, 06:42 AM
Thanks so much for your great advice, really, really appreciate it. The main difficulty i was facing was if i want to put in new textboxes how what code would i use so it goes down that column for each entry. E.g. i have added more columns in the trials sheet. Which numbers or letters do i change from copy/paste of similar textbooks so that it uses the new column/row.

If possible could you still continue working on the basics of the form for both sheets?

Thanks so much.

Kenneth Hobs
06-03-2009, 07:48 AM
If you are going to keep changing things, you need to learn how to do the code yourself if you want to stick with the userform approach. This is like building a house:

The second-floor lintels between
the lally columns, should we rabbet them?

The second-floor lallies?

Second-floor lintels between the lallies.

Oh, the lintels between the lallies.

Yeah, from the blueprints you can't tell.
You want they should be rabbeted?

- No, no, I guess not.
- Okay, you're the doctor.

Hey, fellas! If you got any of them
rabbeted lintels set, rip them up!

My English teacher would not like this but better to measure twice and cut once. The better you plan the better off you will be when you start building.

Ok, fun is over. To rip out the lintels between the lallies, I will show you another way so that you can more readily add or remove userform textbox values to and from your column values for rowNow. There are two approaches one might take. You could Name your column headings. We could then get the column number or from the Named ranges name. Howver, an easier method would be to set the textbox control names in an array and in the 2nd dimension of the array, add the column number or column letter(s) that match. Later today, I will have that ready for you. I will then detail what I did and how to add or remove an array row.

Did you want to set up your code, subject and units list as I suggested?

Not sure what you mean by basics. I have already set up more than my first example. The main part was the part where I set the frmSubject listbox values to remove duplicates. Would showing this userform with the code, subject and units meet your new goals?

iMAN2
06-03-2009, 08:04 AM
yeap putting code, subject, units is fine.

yes it sure would, if you can do this for the 'trials - enter marks' sheet as well i will be most tremendously appreciating and thankful. =D (the reason i asked what to do if more columns was because in the second enter sheet - 'trials' has a couple of more columns.

This way i'll be able to recognise the difference between the two codes of the two enter sheets also. :D

Thankyou

Kenneth Hobs
06-03-2009, 01:50 PM
I think this includes everything that you wanted except for mailmerge and then some.

Added:
1. ControlTipText to show Subject_Code_Units.
2. Added label for current subject as above.
3. Added textbox controls to hold matching Marks for the sheet after the current sheet as locked textboxes.

The code was re-written to get data from the current sheet's column that matches the Tag property for the textbox control. This goes both ways. It both fills the textbox controls and rowNow columns accordingly.

iMAN2
06-03-2009, 11:46 PM
Hi Kenneth,

Some difficulties im getting:

When click B button it popups with the range..

For subject 5, can't click B button.

Cant see total units add up as a new subject is selected.

when I insert first name in row 11, it shows a box in that cell...

In the i.d. it shows up when there is a bos number. Change to if there is first name.

Can this form be applied to the trials - enter marks sheet, without putting the extra columns into the form.

Thanks

Kenneth Hobs
06-04-2009, 07:07 PM
The longer you go without trying to understand the code, the harder it will be for you to modify later as you seem to want many custom additions or substractions. While I tried to comment the code, maintaining it for various changes becomes difficult over time.

The address shown was simply a MsgBox() which I used to debug and test. A simple search of the code by Ctrl+F would have found it.

Let's review some terms in terms of Excel versus Access or any database.
Excel-Access:
sheet - table
row - record
column - field
column heading - fieldname

In terms of userform, there are only controls. There are no columns (per se), rows or anything else relating to a database of any sort. It could be like a Form of sorts but as you can see, it may take alot of work to set up. The term Form can be confusing as Vista lists userforms under that folder name in the VBE's Project Explorer.

Excel's menu Data > Form..., only works if you set that sheet up as a normal database. No custom features can be used with it. Programs like Access makes these things much easier to setup and customize.

As you add more to your spreadsheet, maintaining your userforms will become more and more difficult. This is why you should spend 80% of your time designing it and 20% writing code, filling data and formatting cells. I often start with a piece of paper to layout fieldnames, field types, tables and relations. Speaking of relations, Access is a relational database. This is the proper way to do a project like yours.

So, I had the code setup for 7 of the 8 Browse buttons. A simple doubleclick of each would have shown you how to code the missing code for the 5th B button based on how I did the other 7.

Maybe there was a box in row 11 because it had one to start with?

Pay special attention to the Delete button as deleting a row for one sheet and not the next may well cause you some major heartburn.

The id problem is because you added another column. A change in the formula is needed to handle that. I simply changed the first row's formula.

I also added a ZvLookup() function to the public module. I then used that function as a UDF formula for the first row to compute the total number of units. I also added code to compute total unit numbers in the userform.

Now you can see why most people do not bother to reply to requests for userforms that are similar to Data > Form. When it is all contained within one sheet, it not too hard.

Running the userform from another sheet will involve several changes. Simply copy the userform and rename it and start testing. You should probably print the code and study it closely. Some of the controls are invisible because they do not yet contain text and the background color is the same as the userform. You can select them from the dropdown box in the Properties. Some of the new controls are labels which look like red squares since I made them so small. These hold each units value for each subject. I put them near the larger unit labels.

Try to get your first goal accomplished before starting the new one.

iMAN2
06-04-2009, 09:42 PM
were u meaning to attach the latest version of my workbook... or your original elephant one?

also i still don't understand what happens by clicking the rownow button and when to use it.

thanks

Kenneth Hobs
06-05-2009, 05:12 AM
I left my stick at home but I think this is close to what I had done.

iMAN2
06-05-2009, 08:08 PM
Thanks Kenneth. Works great. Just one little issue, for the trials sheet, when i enter in marks they don't end up in the right column. If i change the code then itll do the same for the half-yearly sheet which will be problematic. Do i have to create another form with some changed coding for the trials sheet.

Also the correct units don't appear for the trials sheet, and the id number is blank.

Is it also possible to change the trials sheet so it shows out of how many entries already filled (like the half-yearly sheet) instead of 299.


Can you also please explain the role of teh rownow button, im still confused about that aspect. Is it possible so row 4 (the first row of data entry) shows as 1/all entries, instead of 4/all entries.

Thanks so much for your help.

iMAN2
06-06-2009, 07:51 PM
Just this little bit to go.. then solved.

lucas
06-06-2009, 08:01 PM
iMAN2, you don't seem to be very willing to try to understand what Ken is doing with the code but you keep asking for revisions. Why may I ask?

iMAN2
06-06-2009, 09:44 PM
hmmz ill revise my request so i can learn and try it myself:

Do i have to create another form with some changed coding for the trials sheet as if i make changes in the original it will affect the other half-yearly sheet?

If so how do i copy the form as when i export it and import it using a different name, it says the form already exists.

What does the rownow function do, im still perplexed to its function?

How do make row 4 appear as row 1?

Thankyou

Kenneth Hobs
06-07-2009, 07:14 AM
In post #29, I explained why you should copy the userform if you are going to make some other changes for it to work other than as designed.

I explained rownow in post #23. It is the current row of the active sheet. Clicking the RowNow button updates the userform controls with the data from the active sheets row. Just click any row in the sheet and then click the RowNow button to make the userform controls fill with data from the row that is active now. Note, all of the methods to move does not update your data from the userform to the rownow. This only happens when Add is clicked.

I coded it to match rownow with the row number in the activesheet where data is shown. While you could change it, that will be confusing for the user if you change it.

There are at least 4 ways to copy a userform. (1) Right click the userform in the VBE Explorer and Export. Do the same to Import. You change the name so that you don't have name collision problems. (2) Drag and drop the userform in Project Explorer from one sheet to another. When you drag and drop it to a workbook that has the same name, a copy is made. (3) Create a new userform and then paste the code from the other. (4) Use the menu in VBE to do method (1).