Validation form concept

[Aussiebear]

I am tryingto build a workbook which will allow me to validate a Grain Harvest Management Scheme (GHMS) number provided by a truck driver. The origonal data will be obtained from another source and maintained within this workbook by an administrator.

The user (weighbridge clerk) will only have access to the form, and should be able to simply enter in a GHMS number or a truck rego and have the correct data filled in the other fields. There will be a maximum possible 9999 rows of data on one sheet only, and its unlikely this will be filled within the next 10 years. I expect that the worksheet will only contain around 3000 rows of data at this stage.

Whilst the initial form design has a combobox to scroll select the GHMS number I will change it to allow the user to type in a number or rego to retrieve the correct data.

I also intend to build a toggle button hidden in an image so that the Administrator can turn on or off the worksheet visibility, but this is a down the track process. Could someone check over the code provided and tell me where it may be improved.

Thank you

[Bob Phillips]

Not much to say Ted, it is quite straight-forward and seems to work well, so here are a few comments.

#1 - you can load the combo much more simply

[vba]

Dim vData As Variant

vData = Worksheets("Data").Range("A2").Resize(LastGHMS)

cboGHMSID.List = vData[/vba]

#2 - I don't really see why you use that loop when adding. Does it not just get added to the end? If it could get inserted, you could use Application.Match to get its position more quickly than looping

#3 - it would be wise to fully qualify ranges with its worksheet parent, either using Worksheets("sheetname") or the worksheet codename

#4 - I think you should think abouts interface as well as its functionality. The form is over-large, and would benefit from reducing and laying out nice and clearly. I always think an image on a form when it is the only one also makes it look better

#5 - when showing a list of ids, it is often a good idea to have a multi-column list and show the basic details of the item in the row as well. It won't bother people who know the ids and can work with them, it will help people who are no so familiar with them

[Aussiebear]

Thanks Bob. The code is adapted from something Malcolm provided, and whilst I've modified it to try and suit this, I haven't really understood it.

I will adapt your suggested code to suit in the morning... well later in the morning when the sun gets up.

Will have a look at Application match as well. Yes I will also name the respective ranges. As to the ID's these are industry recognised, but I will give some consideration to this piece of advice as well.

[Aussiebear]

#1 - you can load the combo much more simply

[vba]

Dim vData As Variant

vData = Worksheets("Data").Range("A2").Resize(LastGHMS)

cboGHMSID.List = vData[/vba]

Bob, I'm gettng an error message on the vData = Worksheets("Data").Range("A2").Resize(LastGHMS) line. Run time error 1004 Application defined or Object defined error. Going to "Help" it tells me that: This message is displayed when an error generated with the Raise method or Error statement doesn't correspond to an Error defined by Visual Basic for Applications. If I remove the (LastGHMS) from the line it moves to the next line to error.

#2 - I don't really see why you use that loop when adding. Does it not just get added to the end? If it could get inserted, you could use Application.Match to get its position more quickly than looping.

In thinking about this, I've named the range in Column A as GHMS ($A$2:$A$9999). Since this will never need to be amended, I'll not need an insert function. However cells within the other 2 columns (Named Ranges- "Rego" and "Owner"), may display blanks, which may need to be edited when a driver presents physical evidence of a GHMS valid ticket.

#4 - I think you should think abouts interface as well as its functionality. The form is over-large, and would benefit from reducing and laying out nice and clearly. I always think an image on a form when it is the only one also makes it look better.

The problem I have here is that this form is the only interface that the User gets to see. If I shrink the form , what do i use as a surrounding background?

#5 - when showing a list of ids, it is often a good idea to have a multi-column list and show the basic details of the item in the row as well. It won't bother people who know the ids and can work with them, it will help people who are no so familiar with them

I could fill the bottom section of the form with text, namely a help assist set of messages I guess, so that the user can understand that which we are asking her to do if certain situations occur.