Simple phonebook application starting to get complicated.

I have a phonebook application developed in this question.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28636136.html

This application is limited to 4 columns and has been extended from a 2-column application. To increase its functionality, I would like to improve on it such that the fields are defined from the column labels in row 1.

So basically the routine would look at row 1 and setup fields according to the cell entries in row 1. This would enable to have option to play with the field names as well as the number of fields.

Another minor change is to have the phone numbers treated as text so that leading zeros are not trimmed.
LVL 43
Saqib Husain, SyedEngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Getting the field names from the header row can be done on userform initialize, setting the labels with the text.
Added display of the entire list on open.
Moved the text boxes and resized the labels to have the longer text.

Formatting column D as text keep the leading zeros.

I don't have a solution for variable number of fields.
How should that work, without having the space for it on the form.
If the number of fields are defined = made on the form, it is possible to not show not used fields, but that require some kind of logic to define which fields to display and not to display.
PhonebookEEv4.3.xls
Saqib Husain, SyedEngineerAuthor Commented:
Hi, thanks for the effort.

I don't have a solution for variable number of fields.
How should that work, without having the space for it on the form.

I think that the labels and the textboxes should be "added" at initialize. The form height should be adjusted accordingly.
Robert SchuttSoftware EngineerCommented:
Try this one please.
PhonebookEEv4.4.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Saqib Husain, SyedEngineerAuthor Commented:
Hi, when I removed the two columns you added and then inserted one of my own the usedrange remembered the previous column count and I ended up with an extra textbox with no label.

I replaced the usedrange methods with a variable ur defined as

    Set ur = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range(Cells(1, 1), Cells(1, Columns.Count).End(xlToLeft)).EntireColumn)
PhonebookEEv4.5.xls
Saqib Husain, SyedEngineerAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for Robert Schutt's comment #a40691493
Assisted answer: 0 points for Saqib Husain, Syed's comment #a40691580

for the following reason:

Thanks a lot.
Saqib Husain, SyedEngineerAuthor Commented:
Robert SchuttSoftware EngineerCommented:
Sure thing!

It's up to you of course but I would suggest adding the first comment as an assisted solution as well since the basis was there and the column D text setting was also suggested there.

The problem with the empty column I had forgotten to mention but when it happened to me I simply saved the file (which seems to delete unused space unless there is some content left in those columns) and the problem was solved.

I'm not sure yet when I'll have time to look at your next question, probably later tonight.
Saqib Husain, SyedEngineerAuthor Commented:
To follow the expert's suggestion
Saqib Husain, SyedEngineerAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.