Dynamic GUI Form Controls

[ChrisGreaves]

I happen to be doing this in Excel, but I might try it in MSWord.
In Excel the user has several worksheets labeled "Income", "Expenses", "People", Businesses" and so on.
The user names each worksheet and creates a simple table with column headings (Date, Amount, Business etc.)

I set myself to answer the question of just how generalized a GUI form could be.
I have got as far as a single GUI form and a single macro that uses the Active Worksheet as a source of data; the column headings determine the labels of the text boxes.

Untitled2.png

So far so good. When the form is loaded with the Income worksheet active, the text labels in the GUI form take on the values of the column headings. To my delight this worked in the Expenses worksheet and the four reference table worksheets too. The GUI form can be used as a general-purpose worksheet data editor.

Having dynamically changed the contents of the labels, I thought to change the names of the text box controls, with the idea that the name of a data entry control "Date ..." would help to identify the nature of the data column in the worksheet.

Untitled3.png

This results in a RTE '382'

I do not need code written here; my question is much more basic:-
Am I expecting too much in wanting to build a GUI form at run-time?

I poked around in the FRX file and could see "labels", but I have no real desire to start editing an encoded file; and I'm not at all sure that I can import an FRX at run-time in the running workbook.

Thanks for any comments.
Chris

[HansV]

Warning: the column headings may contain spaces and punctuation, but those are not allowed in a control name.

You might use code like

ActiveWorkbook.VBProject.VBComponents("frmGenLedger").Designer.Controls(lngControl).Name = "tb" & strLabel

But whether this is a good idea...

[rory]

Also, you know Excel already has a built-in data entry form?

[ChrisGreaves]

Warning: the column headings may contain spaces and punctuation, but those are not allowed in a control name.

Hans, thanks for this. I have gone through all sheets and eliminated spaces from my test-bed column headings.
If I get to production, initialization code would check all column headings for quality.

ActiveWorkbook.VBProject.VBComponents("frmGenLedger").Designer.Controls(lngControl).Name = "tb" & strLabel

As soon as I saw .Designer. I could feel the walls closing in (grin), but laboured on in Excel2003, gradually eliminating or translating components of your statement - but in the end failed again (see Below)

But whether this is a good idea...

Why would it not be a good idea?
My general theory was to be able to customize a GUI form at run-time based on the end-user's column headings. I think that is a reasonable goal - to adapt to the user's local terminology. I tried a similar thing 20 years ago when I made my applications create default INI files, but I wanted a GUI form to self-fabricate based on the contents (section, key, and key-value strings) of the INI file.

Untitled.png

Below: Today I have to settle on Excel2003/VBA's statement that it Just Won't Play Along With Me.
I cannot change the .Name of a text box control. I made several essays using frmMe.Controls(2) and frmMe.Controls("TextBox1") and the like.

My fall back position is that if I need to interface with the end-user, I can leave the form Names intact ("TextBox1", "TextBox2", "TextBox3" ...) and make use of a string array that maps the fixed names to synonyms based on the user's column headings. I already uses these names to reload the Captions(?) of the labels to the left of each text box.

No-one is encouraging me to mess around with the FRX file, and I am grateful for that

Finally: Although this sample is based on a ledger system, my interest is in the design and use of GUI forms automatically tailored to the user's worksheet labels.

Cheers, and thanks again. Chris

[ChrisGreaves]

Also, you know Excel already has a built-in data entry form?

Thank you Rory.
The short answer is "no"; the longer answer is "What do you mean by "a built-in data entry form?".

I am using Office2003/VBA (just to keep every other member of Eileen's Lounge on their toes ), and to date have always used a blank UserForm and the ToolBox to drag controls to the canvas.

Please and thank you: What version of Excel are you using? And Where do you find this built-in data-entry form?
Thanks again, Chris

[HansV]

> Where do you find this built-in data-entry form?

Click anywhere in your data and select Data > Form.

[HansV]

You'd have to execute a line such as

ActiveWorkbook.VBProject.VBComponents("frmGenLedger").Designer.Controls(lngControl).Name = "tb" & strLabel

before you load/show the userform.

[ChrisGreaves]

Click anywhere in your data and select Data > Form.

Thanks Hans. Now I see it. never used it before now; always made my own from UserForm.
I suspect that UserForm is the route to take if I want to implement data-validations, periodic logging of transactions etc.
Cheers, Chris

[HansV]

Yes, the built-in data form is quite simple. If you want more functionality, you'll have to create your own userform.

[ChrisGreaves]

You'd have to execute a line such as...
before you load/show the userform.

Untitled.png

I agree; that was one of my essays.
Here is another essay, same RTE.
I suspect that VBE engineers the UserForm by compiling the control names for Controls whose values the user can change. That is, that textbox controls L(data entry) have names that must be fixed to allow other internal code to operate after the form is Loaded/Shown, but that Labels on the form require no interface with run-time code, so their names/captions can be changed during run time.

A similar argument must hold for the status .Visible and .Enabled - those states do not change the way a user-input control operates, since setting either of those two False means that the user can't operate them, so that their run-time status is unchanged.

I didn't express that very well.

Any control that might need to be monitored (by the developer or by VBE) has its identifier locked so that the VBE run-time code doesn't have to deal with change of identifiers.

Cheers, Chris

[HansV]

You're still running your original code that failed before.

Before:

S2382.png

Code:

Code: Select all

Sub Test()
    ActiveWorkbook.VBProject.VBComponents("frmGenLedger").Designer.Controls("TextBox1").Name = "TB01"
End Sub

After:

S2383.png

[ChrisGreaves]

You're still running your original code that failed before.

Arrrrrrrrgh! Thank you Hans.
It's all PJ's fault for posting that link to SpaceX.
I can now duplicate your results and will post back Real Soon Now!
Cheers, Chris

[PJ_in_FL]

You're still running your original code that failed before.

Arrrrrrrrgh! Thank you Hans.
It's all PJ's fault for posting that link to SpaceX.
...
Cheers, Chris

Seriously! So easily distracted.

Maybe you do secretly yearn to write SF instead of user manuals.

Just do it, Chris!