So after Dicks (nice to read) Alive and Well , let’s do some Excel/VBA stuff again.
I intend to get a bit of discussion on this one, so bear with me.
I’m currently developing an Excel workbook for a customer. One of the interesting things with this project is that it consists of multiple data tables, each in its own worksheet. The customer needs to be able to edit the data in these tables.
One of these might contain these fields:
The way I would normally have done this is by creating a userform with a control for each field and all the coding that is needed to handle record selection and stuff. (and yes, I do know there is MS-Access :-))
But since I have an odd 5 worksheets to handle I decided it would be nice to have a generic data entry form that would build itself using a companion worksheet for each data sheet.
For each worksheet that requires data entry I inserted a companion sheet with this information:
My VBA code inside the userform’s code module reads this sheet and builds the controls accordingly.
The form has a couple of properties I can set to control appearance. All it now takes to show the data entry form for worksheet “oSh” is this bit of code:
Set frmDataEntry = New ufDataEntry
Set .Source = oSh
Set .SourceSettings = ThisWorkbook.Worksheets(oSh.Name & “_Fields”)
.Title = sTitle
.RowCount = 14
.FieldWidth = 120
.LabelWidth = 150
.Label2FieldMargin = 12
.VertMargin = 3
.HorMargin = 6
.CurrentDataRow = 1
I’ve got this all up and running, including Validation and all (and yes: I’ll be writing this all up in detail some day).
Now to the questions of the day:
What do you think about the method I chose?
What alternative solutions have you come up with in the past?
Jan Karel Pieterse