Using a variable in am object name on a form

[mr.why]

this is rubbish. There must be a better way to code this using a loop but i cannot use the variable within the textbox name. Please help and i promise i have searched this site and others but dont know what search criteria will get me the right answer.


If y = 1 Then TextBox1.Text = a
If y = 2 Then TextBox2.Text = a
If y = 3 Then TextBox3.Text = a
If y = 4 Then TextBox4.Text = a
If y = 5 Then TextBox5.Text = a
If y = 6 Then TextBox6.Text = a
If y = 7 Then TextBox7.Text = a
If y = 8 Then TextBox8.Text = a
If y = 9 Then TextBox9.Text = a
If y = 10 Then TextBox10.Text = a
If y = 11 Then TextBox11.Text = a
If y = 12 Then TextBox12.Text = a
If y = 13 Then TextBox13.Text = a
If y = 14 Then TextBox14.Text = a
If y = 15 Then TextBox15.Text = a
If y = 16 Then TextBox16.Text = a
If y = 17 Then TextBox17.Text = a
If y = 18 Then TextBox18.Text = a
If y = 19 Then TextBox19.Text = a
If y = 20 Then TextBox20.Text = a

it is actually searching a string then dropping each letter into the next text box. the rest of my code is fine but i am now having difficulty cause the macro is so long. would like it to use for loop for number of text box.

[fumei]

OK. Yes, it is rubbish. But I have to say, unless you can explain clearly why you are trying to do this, it looks like the idea is rubbish. I can not see a purpose to be dropping single letters into textboxes. Perhaps you have a good reason for this kind of processing. If so, by all means tell us.

When posting it may also be helpful to fully clarify any variables. I have NO idea what, or where y is coming from. Perhaps there is a better way to do this.

While of course I can not see your form...I am willing to bet that some of your textboxes do not need to be textboxes at all. In fact, if YOU - as the programmer - are putting content into them, then they most likely do not need to be textboxes.

Textboxes are for one thing only. ONE thing. They are for the user to enter text. If the content of a textbox comes from code, then use a label, not a textbox. Textboxes are for USER input.

OK, back to what you actually posted. You must remember that EVERY line of this code will process. Say y = 2.

If y = 2 Then TextBox2.Text = "a"

Ok, fine. But every one - EVERY one - of the other IF instructions will be parsed and validated to see if it is true, or false...which is what IF statements do.

Use Select Case

In any case:


Select Case y
case 1
TextBox1.Text = a
case 2
TextBox2.Text = a
case 3
TextBox3.Text = a
case 4
TextBox4.Text = a
case 5
TextBox5.Text = a
case 6
TextBox6.Text = a
etc etc etc
End Select

Now - again assuming y = 2 - the code will run through the Select Case statement, hit Case 2, action the instruction for THAT case...and ignore the rest of the Cases. It is done.

Next, you don't say...but you should...if what y IS. An integer? A string?

You CAN use a variable in a textbox name, but you have to use a full reference.



Dim y As Long
y = 5
Me.Controls("Textbox" & y).Text = "a"

will put "a" in Textbox5.

So...you don't need the IF statement at all actually.

I have no idea what you mean by "next textbox". The textbox number seems to be the same as y...so what is "next" about it?

Lastly, I hope you realise that

Me.Controls("Textbox" & y).Text = "a"

will make the textbox content "a". This will NOT add "a" to any existing content of the textbox. It replaces the content.

But I still have to say...this seems a very strange thing to be doing.

[fumei]

Oh....and try to get into the habit of NOT keeping the default names of controls. Textbox20 is, frankly, a bad name. Textboxany number is a bad name. OK, maybe not "bad"...but it much much much better to get the habit of naming ALL controls and objects explicitly.

[fumei]

Personally, I think if ANY userform has more than 10 or so textboxes...it is back to the design stage. For example, I can not think of a single Microsoft dialog (a company not exactly noted for brevity in their coding) that has even 10 user input text fields.

Could you not be using comboboxes? Option buttons? What possibly could require the user to be putting in 20 separate text chunks? And again, if the content of the textbox is put there by YOU - the programmer - then it should not even be a textbox.

[mr.why]

well thank you for the advice but i dont know whether critisising mt methods or my ideas is really all that constructive. I am not a developer i am a teacher of Mathematics who has had to learn this stuff without training or help. I would like to do what i do to create a worksheet for kids that is a little more interesting than page 26 of the text book.

I appologise for for using textbox26 but i chose to do that with the idea of a variable which you have now demonstrated can work.

Please in future do not completely dismiss someone elses work as futile and pointless unless you know the reason behind it.

Again appologies to anyone here who took offence at my complete lack of knowledge on this subject i will try to figure stuff out on my own in future.

[fumei]

My apologies for seeming to be critical of you. It was not intended to be any personal comment.
Please in future do not completely dismiss someone elses work as futile and pointless unless you know the reason behind it.
1. I did not dismiss your work as futile. I would suggest you read more carefully.

2. "unless you know the reason behind it"....was I not, IN FACT, asking precisely that? Asking for what reason there may be.

Perhaps I could have worded it better. Granted. However, I believe you took this way more personally than needed. I say that , based on:
Again appologies to anyone here who took offence at my complete lack of knowledge on this subjectWhy are you apologizing? That is way over reactive. YOU have nothing to apologize for, and no one here has taken any offence. I certainly was not offended. If you believe that someone (I am assuming me) took offence, then rest assured...I was not. I am never "offended" by someone not knowing things. That is why - in fact - I write that sort of stuff. To actually be helpful, and point out possible better direction and better methods of doing things.

If I can across as too gruff and personal....my apologies. I repeat, there was NEVER any personal stuff intended. There was no criticism of YOU intended...heck, I don't know you, how can I possibly be critical of YOU? I was, 'tis true, critical of aspects of what you seem to be doing, but...shrug...it really was intended to try and point you in a possible better direction.

And the fact that you seem to feel insulted, or slighted, does not change the other fact...there is very likely a better design/process to what you may be doing.

You may not believe this, but all that text I wrote was actually intended to give you information that would help you.

Oh well.

[mdmackillop]

Hi Mt Why,
Welcome to VBAX
Using Gerry's basic premise, you can reduce the select case for your sample data as follows


Private Sub CommandButton1_Click()
Dim y As Long
y = InputBox("Enter a number greater than 0")
Select Case y
Case 0
MsgBox "no such textbox"
Exit Sub
Case Is <= 20
Me.Controls("Textbox" & y).Text = "a"
End Select
End Sub

The point of Select Case (as pointed out) is that it checks until a "True" is found, performs the action, then moves directly to the end of the Select case procedure.

[fumei]

And Mr.Why...I am very sorry if you feel that we(...uh...me) are unfriendly.
i will try to figure stuff out on my own in future.I can't say that trying to figure stuff out on one's own is a bad idea, but I would not be happy if you felt rebuffed or reluctant to post questions in the future.

I encourage you to reconsider that position.

[mdmackillop]

By the way, Mr.Why,
You mention Worksheet in your earlier response. Are you looking to create Spreadsheet or Word examples, as you have posted your question in a Word forum. In either case, if you are looking for examples, feel free to ask. There won't be many things you can think of that haven't been created by one of us over the years.

[mr.why]

My thanks to both for the help. Yup maybe I was being a little over sensative but I had just had a particularly bad episode with bottom set year 11. I do apreciate the help that has been given. You must understand though from a complete novices point of view that if they ask for help then get someone who is obviously very well read on the subject (this is a compliment BTW) saying its all wrong it can make that novice feel a little intimidated.

Anyway I don't want this to become something bigger than it is. Thank you for the help and yes, i would prefer to continue using this site in the future and would apreciate fumei's help wherever possible.

BTW i would have used a spreadsheet (Though you may not believe it, i am more competent at using objects and VBA in Excel) i need better text formatting than Excel would give (superscipt, subscript and the like)

[Norie]

mr why

Glad to see you'll still use the forum.:)

One thing I was wondering was what formatting was missing from Excel?

As far as I know super/subscript are available.

[mr.why]

Needed this kind of look



#2=3(root symbol)#8
#7=3#2+1
#5=#7-2
#13=3#5-2
#19=#13+6
#20=#19+1
#12=(#20+4)/2



Once the various textboxes were filled a search and replace would change #1 for the first text box value (text box would be needed so I could amend the letters if I chose) and #2 with the second etc and voila, a set of formulas to solve would be presented.


There is more but this is the gist of it.



If Excel could do this then please let me know. As I said I'm more comfortable dealing with a spreadsheet.

[Norie]

I honestly can't say if Excel can do what you want, but that's probably because it isn't quite clear what that is.

That's probably a problem on my part rather than you.:)

Any chance you could attach a sample workbook/document/whatever?

[mr.why]

4102

appologies for any misunderstandings. I really am at sea trying to use the right words to explain myself here. It seemed such a straight forward idea this morning :)

[Norie]

mr why

There's no need for any apologies.:)

We just like to try and help, and the more information we can get then the easier it is to do so.
I don't have time to look at your file tonight.:beerchug:

I'll have a look later, or tomorrow but hopefully someone else will pop in.