how to get a list of objects in a userform 1

[crawfme]

I have created a userform and would like to have a list of the objects that I have placed on the form (labels, textboxes, etc.). I have used 3-character prefixes to differentiate between the different types of objects, but it would be useful to see an alphabetic list of them all. Basically what I'd like is the ability to cut and paste the objects that I see when I select the Properties drop-down box. Is this possible? Thanks.

 

[petrosky]

Hi, this example should get you started. Built using 2007

Private Sub Command45_Click()
Dim i As Integer
Dim controllist As String
Dim Control As Control

For Each Control In Me.Form

controllist = controllist + Control.Name

Next Control

MsgBox conlist

End Sub

Obviously this just dumps it into a msgbox, but I am sure you would be able to change the output to send you an email (docmd.sendObject or whatever).

HTH.

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[fumei]

Basically what I'd like is the ability to cut and paste the objects that I see when I select the Properties drop-down box. "

While petrosky's code certainly can give you the name of each control, it is the above that is of interest.

What exactly do you mean cut and paste the objects?

Say you have a textbox - txtYaddaYadda.

Now what? Just have the name does not seem very helpful. If you Copy txtYaddaYadda, and then Paste...you get Textbox# - with # being the next number. If there IS no number - i.e. all textboxes are named, like txtYaddaYadda - then the Pasted textbox would be TextBox1.

There are some little things incorrect with petrosky's code.

For Each Control In Me.Form

is an error, it should be Me.Controls

Also:

MsgBox conlist

is an error, as there is no variable conlist; there IS a variable controllist.

faq219-2884

Gerry
My paintings and sculpture

 

[crawfme]

Thanks for the input. I have many objects on the form, all of which I have named with meaningful names and prefixes that indicate the type of control e.g. lblName and txtName. The problem was that I wanted to saved the inputs to a table on a worksheet so that I could re-populate the form with the values at another time and I was losing track of the various control names. I used Petrosky's suggestion to build the following code that creates a list of the controls and this worked for my needs. Thanks again.

Private Sub ListObjects()
Dim i As Integer
Dim row As Integer
Dim Control As Control

Sheets("Input Form").Activate
row = 1

For Each Control In frmInput.Controls
Cells(row, 1) = Control.Name
row = row + 1
Next Control
End Sub

 

[fumei]

And what exactly as the variable i do?

Dim i As Integer

Glad it is working for you, although I it seems to me that "I could re-populate the form with the values at another time" would be more of a question logic, than names.

faq219-2884

Gerry
My paintings and sculpture

 

[petrosky]

fumei,

Good points. As you can tell it was a long day. Thank you for your corrections.

In case the original op is still interested...you could also create a new table called "controls" with a field name called "controlname" then run this code.

Private Sub Command45_Click()

Dim controllist As String
Dim Control As Control

DoCmd.SetWarnings False

For Each Control In Me.Controls

controllist = Control.Name
DoCmd.RunSQL "INSERT INTO controls (controlname) VALUES ('" & controllist & "');"
Next Control

DoCmd.SetWarnings True

End Sub

HTH

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[crawfme]

Hi fumei. I'd cut and pasted Petrosky's suggestion and modified it to do what I wanted - I must have missed the "dim i as integer" declaration. Sloppy, I know.

I'm new to programming Excel forms and I'm finding my way by searching the internet and asking questions in forums such as this. It is entirely possible that I'm not taking the best approach, so I'm curious about your comment "although I it seems to me that "I could re-populate the form with the values at another time" would be more of a question logic, than names. "

The project that I'm working on is to build a tool that will allow users to calculate an annuity. I want to give them a form that prompts them for the required inputs and then give them the option to save their inputs as they may have to re-run the calculation at a later time if one of the inputs should change. I've designed the program to copy the inputs from the form to a table on a worksheet using the object names, like this:

With ws
.Cells(2, 1).Value = Me.txtCaseID.Value
.Cells(2, 2).Value = Me.txtPurchDt.Value
.Cells(2, 3).Value = Me.txtIncStart.Value
.Cells(2, 4).Value = Me.cboAnnType.Value
.Cells(2, 5).Value = Me.cboPayFreq.Value
.Cells(2, 6).Value = Me.txtGuarYrs
End With

I have a combo box on the form that has a dropdown of all the Case IDs that they've entered that gets populated with the values in column A from that table, and a command button allowing them to retrieve the information.

Is this the best solution?

Thanks for sharing your expertise.

 

[fumei]

Not quite following, but again, this is a logic problem. My lack of understanding in this, is MY problem. In other words, I am not following what you are trying to do. Let me see if I can describe/follow it.

"I want to give them a form that prompts them for the required inputs and then give them the option to save their inputs "

You have a form where user enter information.
You store their inputs.

OK, this is a fairly common need and there are fairly common solutions. And, yours is one of them.

"I've designed the program to copy the inputs from the form to a table on a worksheet using the object names, like this:"

OK, now what I am not following is:

"I have a combo box on the form that has a dropdown of all the Case IDs that they've entered "

What are these Case IDs? Let's make it simple. A userform with three textboxes. The users enters information into the textboxes, and a commandbutton stores that information into three cells.

Is that a Case ID? One set of three pieces of information? Let's call it Case ID_1.

If the user inputs another set, (calls up the userform and enters information), is that a different set? Case ID_2?

If the userform is used again, logically, what do you want to happen? Textboxes to be prefilled with information from a selected Case ID, selected by, say - "a dropdown of all the Case IDs that they've entered"?

If the user selects Case ID_2 - a unique set of three pieces of information....where are that explicit three pieces? Because.....

"gets populated with the values in column A from that table"

Column A

So, two unique sets of three pieces of information (indentified by Case ID_1 and Case ID_2) are both in Column A? I am not saying yea or nay to this, I just do not understand precisely.

But let's say, all textbox1 information is in Column B, textbox2 information in Col C, textbox3 in Col D

Case ID are in Col A.

Case ID_1 "Bob" "Harry" "George"
Case ID_2 "Frank" "Larry" "Yaddas"
Case ID_3 "Curly" "Merry" "BlahBlah"

User selects Case ID_2, the userform fills:

textbox1 with Row2, Col B Frank
textbox2 with Row2, Col C Larry
textbox3 with Row2, Col D Yaddas

Simple enough. The information for textbox1 will always be the row (identified by the user selecting a Case ID), and Col B.

Its explicit information will ALWAYS take Col B.

In which case, what I do not understand, is - from the OP:

"I'd like is the ability to cut and paste the objects "

Why the objects? Why do you need the names?

The last post states:

"they may have to re-run the calculation at a later time if one of the inputs should change. "

Fair enough. Logically, if you "re-run" then you want to start with the original information (retreieved from source). Then changes (for the re-run) can be entered.

Logically, my question is:

Are you changing the source information (with a re-run), or are you adding the new values?

In either case, logically, WHY does this have anything to do with the names of the controls? Control content (their values - textboxes/labels for example), logically, should be either:

1. tied to a source of information/data;

OR

2. tied to a calculation of information/data - which comes from a source

You fill control values by cell:
Row (derived from user selection), Column X

Again, this is I admit dense of me, but I do not understand why you need the object names? I am not disputing anything, I just do not understand.

To repeat my original puzzlement, I do not understand what is the problem you are having with - "I could re-populate the form with the values at another time"

Here are your instructions to put the userform values into the cells.

With ws
.Cells(2, 1).Value = Me.txtCaseID.Value
.Cells(2, 2).Value = Me.txtPurchDt.Value
.Cells(2, 3).Value = Me.txtIncStart.Value
.Cells(2, 4).Value = Me.cboAnnType.Value
.Cells(2, 5).Value = Me.cboPayFreq.Value
.Cells(2, 6).Value = Me.txtGuarYrs
End With

OK, so...ummmm, here it is the other way, logically.

User_Choice = comboxbox selection, into a number and will be used as ROW number

Me.txtCaseID.Value = ws.Cells(User_Choice, 1).Value
Me.txtPurchDt.Value = ws.Cells(User_Choice, 2).Value
Me.txtIncStart.Value = ws.Cells(User_Choice, 3).Value
Me.cboAnnType.Value = ws.Cells(User_Choice, 4).Value
Me.cboPayFreq.Value = ws.Cells(User_Choice, 5).Value
Me.txtGuarYrs = ws.Cells(User_Choice, 6).Value

And there you go. Which brings me back to saying I do not understand:

1. copy and paste of objects - rather than information

2. what having the Names of the objects actually does for you, what the purpose is.

As I mentioned, copying an object (a control), and then pasting it gives you a unique name.

Copying a textbox object (txtCaseID) and pasting it will create TextBoxX.

What is the purpose of doing that? In terms of the logic of what you are trying to do (I think):

You have a form where user enter information.
You store their inputs in cells (Row, Col).
You fill control values by cells:
Row (derived from user selection), Column X

"I've designed the program to copy the inputs from the form to a table on a worksheet using the object names, like this:

With ws
.Cells(2, 1).Value = Me.txtCaseID.Value
' etc.

Is this the best solution?

For that part of it, sure, that is fine. That is the logic for getting information IN. What is the logic for getting it OUT? And what does that have to do with getting the names of the controls (as opposed to using the names of the controls)?

I am not an Excel person. The details of data structure/storage within Excel are beyond me. As for that kind of assistance, there are many many people here who can help with those details. If you clearly state what you require, and what you are trying to do, no doubt you can get help/ideas.

However, I do know that populating, and "repopulating", control values has nothing to do with whether the values are numbers (Excel stuff - not my area), or word strings (Word stuff - my area). Processing (DO this, DO that, IF this...then that) are always a matter of logic. Nothing more. It can be very very very very very complex logic, but logic none the less. And that logic - to work well - MUST be short, efficient, and uttterly explicit.

So yes, you can get the names of the controls you have....NOW WHAT?

Repopulating something is exactly the same (logically) as populating it in the first place.

You get the information...and you put it in.

I must be very dense, as I simply do not understand what the issue/problem is.

faq219-2884

Gerry
My paintings and sculpture

 

[crawfme]

Gerry, thank you for taking the time to write such a detailed reply. I'm sorry if I haven't been clear enough about what I'm trying to do.

In your simplified version where the userform has 3 textboxes, the value in textbox1 (which I've called txtCaseID) would be the caseID. The only reason I wanted a list of the objects is because there are actually 46 objects on the form and I just wanted a list that I could refer to when I was writing the code to copy the data to the worksheet.

I'm happy to report that the project is coming along well and I appreciate all the help that the people at Tek-Tips are willing to give.

Cheers.

 

[fumei]

Ah, so it is just for you keeping track of names, not for any coding purpose itself. Fair enough.

faq219-2884

Gerry
My paintings and sculpture