I added a boat-load of controls to a userform and really had no interest in naming them all manually. The majority of the controls comprise a pseudo-grid so their positions are well defined. That is, I can rely on their Top and Left properties to tell me which control it is. I used the following code to rename the controls:
Dim ctl As Control
Dim uf As VBComponent
Dim vbp As VBProject
Set vbp = Application.VBE.VBProjects(“SalesInfoSheet”)
Set uf = vbp.VBComponents(“UOrders”)
For Each ctl In uf.Designer.Controls
Select Case ctl.Left
Case 6
ctl.Name = “tbxItem” & CLng(((ctl.Top – 30) / 18) + 1)
Case 96
ctl.Name = “tbxDesc” & CLng(((ctl.Top – 30) / 18) + 1)
Case 270
ctl.Name = “tbxQty” & CLng(((ctl.Top – 30) / 18) + 1)
Case 312
ctl.Name = “chkGsa” & CLng(((ctl.Top – 30) / 18) + 1)
Case 330
ctl.Name = “tbxDisc” & CLng(((ctl.Top – 30) / 18) + 1)
Case 384
ctl.Name = “cbxRep” & CLng(((ctl.Top – 30) / 18) + 1)
Case 444
ctl.Name = “tbxRepCom” & CLng(((ctl.Top – 30) / 18) + 1)
Case 498
ctl.Name = “cbxSeng” & CLng(((ctl.Top – 30) / 18) + 1)
Case 557.95
ctl.Name = “tbxPool” & CLng(((ctl.Top – 30) / 18) + 1)
Case 600
ctl.Name = “tbxIndiv” & CLng(((ctl.Top – 30) / 18) + 1)
End Select
Next ctl
End Sub
This made names like cbxRep1, cbxRep2, etc. for all 30 rows. I looked high and low for a free grid control, but none of them did what I want. I need comboboxes and checkboxes, not just a simple grid. The form is for orders and has header information at the top and lines at the bottom. I have to have enough lines to cover every situation although I’ll rarely use more than 5. Here’s a piece of what the form looks like (it’s not done yet):
Why not use Excel’s grid? I’ve done that in the past. I think some applications are suited for that and some aren’t. It’s a lot of work creating controls on a userform like this. It’s also a lot of work setting up a worksheet to look and act like a form. I’d have to prevent copying and pasting over data validation, just to name one example. The pseudo-grid on this form is probably a good indicator that I should have done it in Excel, but for some reason it just didn’t feel right.
How would you do it and why?
It’s a lot of work to set up a grid like this on a UserForm, and it’s a lot of work (maybe more work) to set it up in a worksheet. The various controls of the UserForm provide much better flexibility, and I find it easier to constrain the user and validate the data if I use the UserForm approach.
You are the pro Excel king but this is my humble programming hobbyist opinion. I think that anything done in Excel does not need a form, it’s redundant. The grid is a GUI on it’s own. It’s a lot easier to change things around, if needed, when everything is on the grid. If the client really wanted a form he/she could have asked for a stand alone application written in C# (or other languages like it).
I meant worksheet instead of grid.
I’ve used the “For Each ctl In uf.Designer.Controls” approach in the past, BTW, and it’s very handy. You can also set tab order and other properties in the loop.
Macro Man –
True, the worksheet is a GUI, and it’s easy to move things around, insert/delete columns, or whatever. Using the worksheet as the interface to your application can be problematic if you don’t want the user to think he can easily move things around. When you use the form, the user gets no such crazy ideas, and is more likely to fill it out properly, and less likely to ask you for the password so he can do it his own way, and break the app.
Using a form is not inconsistent with using Excel for the application, because Excel has a great calculation engine and other useful features. It’s better to use Excel than to spend months programming something in C# that recreates its capabilities.
I’d be a little concerned about the repetition of each line and the vagueness about upper limit on lines. So I might look at using one line of controls and moving them to position as the user clicks on the form. This is completely untried in Excel, mind – although it was a technique I recall using when resource handles were scarce in Windows 3.1 days – so take it with a pinch of salt. If I get time tomorrow I might try a little example to see how practical it is in Excel. Then again, I might not. That’s the excitement….
Mike –
In one project, I set up the userform with about six rows of controls but I needed up to about 25 rows. Space was in short supply. I set up a scrollbar which went from 1 to 20, for the starting index. This accommodated viewing records 1-6 through 20-25. When the scrollbar changed, I moved the visible records back into memory and pulled out of memory the new records to be shown. Behind each row of controls I had invisible rectangles one row high and the width of the row; to highlight a row, the rectangle changed from transparent to a highlighting color. It was pretty slick, the whole thing worked nicely, and the project did when the company was bought by its chief rival and all projects were killed.
I’ve spent a lot of time lately developing a template spreadsheet GUI. I develop a lot of little “databases” which always have a header, one or more record template rows with all the formulas and validation built-in. So now I’ve got a bunch of code and a generic GUI that I can re-use to some extent. It handles sorting, filtering, adding and deleting records and a few other common tasks.
I have the data entry on UserForms and then have code to copy it into the spreadsheet. I find that, for my level of Excel/VBA skill, it’s much easier to modify the record structure if it’s all on the spreadsheet. I also like using the built-in validation, as opposed to having to code it all, and have gotten pretty good at making it work.
My users generally prefer doing their data entry in the spreadsheet, rather than in forms. So far if they want it to work better they just ask me :).
Data validation destruction is a problem which I need to investigate. I seem to recall solutions from j-walk and in the PED. If any of you have any leads or pointers I’d like to hear them.
Dick, in answer to your original question, one thought would be to create the form in VB. It has array controls, so copying the first gives them all have the same name, and you can define an event for all of them. Of course, I’ve never used a VB form in VBA, don’t know how much work it is, but I know you can.
Doug, here’s one way to ensure that data validation is not pasted over — as long as the user opens the workbook with macros enabled.
http://www.j-walk.com/ss/excel/tips/tip98.htm
I have a little workbook which dumps all properties of all controls on all Userforms onto a worksheet. It can also do the reverse: create userforms using that data.
Sometimes I use this to mass-edit certain sets of properties of a form (like your renaming task Dick). After having done the editing in Excel cells, I then recreate the form by reading the data from the worksheet.
Hi Dick,
Can we create the Multiples lines of controls on the fly… i.e.When the user completes one row and presses enter a new row of controls should load……
Regards
Sam
Sam –
You can do it this way, create a new row of controls when the current row is completed, or you could have all the controls in place (design time), and make the next row visible when the current row is completed.
JK –
This sounds like a useful utility. Is it in any condition to be shared?
Ditto that JK and Jon.
You are not creating userforms on the fly, in real life apps ? How and why if so ?
Harald –
In a project of yesteryear I had a form built at design time, but the only design time controls it had were a few simple buttons like Back, Next, Quit. These buttons had event code. Then during operation of the program, various arrays of controls would be created for the user to interact with. No code behind these controls, what mattered was their values when one of the buttons was clicked.
I find it too tricky to build userforms and controls with code on the fly, especially if the user hasn’t granted permission to edit the VB projects.
However, I use tricks like JK’s to build forms in design time, and for run time, I simply rely on changing the .Enabled and .Visible properties of existing controls.
Jon, Harald,
It is very crude and not heavily tested, Especially the bit that recreates the form (you need to remove the existing userform first or (alternatively) rename the form on the worksheet before recreation. But you might be able to build something useful out of it for yourself.
Here it is:
http:/www.jkp-ads.com/downloadscript.asp?filename=ControlLister.zip
I too don’t often put controls on a form at runtime, I prefer showing and hiding what ever I need/don’t need.
Duh, URL isn’t complete, this is the correct one (hope this one parses OK too so you can click it):
http://www.jkp-ads.com/downloadscript.asp?filename=ControlLister.zip
I have a copule of things to add:
One thing that has just popped in to my mind is that you could create the “next set”/row of controls dynmically when the last control is existed (or some other event), then commit the whole lot to the back end, so you only have one set of controls at design time – may have scurtity issues.
You could use a more powerful grid control on the UF, maybe even design a .Net class, and use it’s controls. Alot of OXC grids have drop downs, check boxes etc – although this would make deployment harder!
Personally I tend to go with UF for addins and use the worksheet for Dictor apps,
for example this is for a dictor app, it took a lot of effort to set up:
http://img275.imageshack.us/my.php?image=momssheetip6.jpg
but this was a lot easier and quicker for a addin:
http://img151.imageshack.us/my.php?image=trsufxc4.jpg
Jon,
“You can do it this way, create a new row of controls when the current row is completed”
I tried this some time back but could not get it to work…Can you point me to some resource.
What I was trying was to have
A User Form with (1) Lable – called “AMT” (2) An Edit Box (3) Vertical scroll…
The user form opens showing 4 Edit boxes with lables AMT1, AMT2…etc
There is no way of determining how may Amts a user might enter. It could be 4 or 40 or 400…
The task was to keep getting a lable and an Edit box added every time the user completes entery in one above it…..
Finally I had to resort to
“or you could have all the controls in place (design time), and make the next row visible when the current row is completed. “
(created a large amount of Edit boxes and made them visible as required)
But it was not a very efficient way of doing it
Regards
Sam
Sam –
1. Here’s a link to J-Walk’s userform-on-the-fly example:
http://www.j-walk.com/ss/excel/tips/tip76.htm
2. “But it was not a very efficient way of doing it”
Efficiency can be measured in many ways. All those empty hidden controls seems inefficient in terms of stuff lying around and mostly not being used. But isn’t it more efficient in terms of how long it took you to implement? Does it give the user the same “experience”? Elegance is in the eye of the beholder, and the important beholder in this case is the poor sucker who has to use your program. Don’t ever forget that.
Ross –
I would estimate that the worksheet version took you at least ten times as long as the userform, and the validation and debugging were harder to implement in the worksheet.
“I would estimate that the worksheet version took you at least ten times as long as the userform, and the validation and debugging were harder to implement in the worksheet.”
Yes, it did. there are a number of other issues as well.
Worksheet validation can be very quick to apply as you can copy and paste it, but there are major issues with updating values in Excels built in validation drop downs in what if models like the one that screen is taken from. Displaying worksheets as windows is harder too, and over coming the scroll mouse+ctrl combo is a bit trick (i think there is a .dll somewhere). It is much much harder to work with the layout v’s forms, as spacing and sizing is depends on the grid layout.
The upside is its easier to work with the data – to do cals etc and work with charts. swings and rounder-abouts I guess.
Interesting what are others thoughts?