Solved: loop using textbox

[Johnosley]

Hi,

I have about 20 textbox with the same formatting name (textbox1, textbox 2,....).

Only the first textbox is visible when the user starts to file out the userform. Once he entered some data to textbox1, textbox2 is visible etc.

I trying to build the loop to do that but encountered some issues about the coding. Don't know how to change the numbering the textbox.

Private Sub textboxvisible()

Dim i As Integer
i = 1
If "TextBox" & i.Value <> Empty Then i = i + 1 And "TextBox" & i.Visible = True
End If

End Sub

[fumei]

AND is used with the IF....not as a logic result of the IF.

Valid:

Dim i As Integer
i = 1
If "TextBox" & i.Value <> Empty And "TextBox" & i.Visible = True Then
i = i + 1
End If
That means IF "Textbox" & i.Value <> Empty AND Text
box_i Visible Then....

If your AND is a second instruction (which I think it is), then it should be:
Dim i As Integer
i = 1
If "TextBox" & i.Value <> Empty Then
i = i + 1
"TextBox" & i.Visible = True
End If
This has nothing to do with your incrementing issue. Please clarify. Is this supposed to be a tru incrementing action?

If Textbox1 <> Empty Then Textbox2 is visible...but Textbox3, 4, 5...etc are STILL not visible.

Yes?

No?

Because if it is Yes - a true cascading incrementing - then you must write individual code for each control.

If it is No - say Textbox1 will make Textbox2....Textbox20 (and BTW: you may want to start getting into the habit of not keeping those names...) become visible, then:
Sub AllTheRest()
Dim i As Long
If "TextBox1".Value <> Empty Then
For i = 2 To 20
Textbox & i.Visible =True
Next
BTW: "Textbox" & i.Value does not look good to me. Methinks this shoul dnot work if it is coded exactly like that. The object is not defined.

[Johnosley]

This has nothing to do with your incrementing issue. Please clarify. Is this supposed to be a tru incrementing action?

If Textbox1 <> Empty Then Textbox2 is visible...but Textbox3, 4, 5...etc are STILL not visible.

Yes?

No?

Because if it is Yes - a true cascading incrementing - then you must write individual code for each control.

It's a Yes. It will be a true cascading incrementing. But I don't understand why I must write individual code for each control ? Because the i+1 is always based on value on i.

But I'm new in VBA so may be I should read more on the sub concept.


BTW: "Textbox" & i.Value does not look good to me. Methinks this shoul dnot work if it is coded exactly like that. The object is not defined.
How should I defined that then ?

thanks

[fumei]

Please post your actual code.

The problem with the cascading incrementing is that how do you know what i is? You do not. Let's start at Textbox1, and suppose it is going to make Textbox2 Visible. OK.
Dim i As Integer
i = 1
If "TextBox" & i.Value <> Empty And "TextBox" & i.Visible = True Then
i = i + 1
End If
But you are declaring i = 1 to start. If you are then coming from Textbox2 - so you want to Textbox3 to action, then the code above makes...

i = 1

If this procedure is to be used for ALL Textboxes - 1,2,3,4,5,6.....20 - how are you going to get that number? You are declaring i = 1 to start off. Which does not help if what you really want is 12 (or 8, or 7, or 16)

[Johnosley]

For me, if you filed textbox1, textbox2 will be visible. You decide to file out the textbox2, then textbox3 will show up. etc.

If you need the 12, that means that all previous textbox were filed out before.

My declaration i=1 was to start the counting and set the value of i to something known (and obviously the starting point of my form).

But I see your point. May be I can set up the value of i when I'm doing the initialization of the entire form, not only this sub. Can I do that ?

[fumei]

Well yes, you can set your variable value at initializing. But how does that help.

Public i As Long


Sub UserForm_Initialize()
i = 1
End Sub
And OK, you call textboxvisible. Yes Textbox1 <> Empty, so Textbox2 becomes visible, and yes i now = 2. Assuming all your textboxes are using the _Change event to call textboxvisible....yes?, then:

Say you are at Textbox4. The last one was 3, which means i = 4. You call textboxvisible. Yup:
If "TextBox" & i.Value <> Empty And "TextBox" & i.Visible = True Then
i = i + 1
End If
makes Textbox5 now visible.

OK, fine.

What if Textbox1 is now made Empty? Now what?

BTW: how are you calling Sub textboxvisible?

[Johnosley]

What if Textbox1 is now made Empty? Now what?


Good question. I'm using a multipage to build my userform. One page contains my list of textbox. But this page is visible only if the user answered yes to a question on the previous page. The way the textbox are shown is more for presentation purposes. If I can't figure how to do it I will forget that idea for now.


BTW: how are you calling Sub textboxvisible?

Don't know what you mean by that ? sorry about my newbie question :-)

[fumei]

Sub textboxvisible is a procedure. Procedures MUST be called. Otherwise it is just text in a code module.

Something must call the Sub. This can be a commandbuuton, or an instruction in another procedure.

I mean, something must call it via code. Yes, you can be in the VBE (Visual Basic Editor) and click the Run button (or press F5), to execute a procedure, but during run-time (after your userform is loaded and displayed something must make a Call to it.

Often these kinds of things use the _Change event of a control.Sub Textbox1_Change()
If Textbox1.Text <> "" Then
Textbox2.Visible = True
End If
End Sub
This event fires whenever - note that! - anything changes in Textbox1.

So my questions was...what are you doing to make the Sub textboxvisible() execute?

[Johnosley]

So my questions was...what are you doing to make the Sub textboxvisible() execute?

I understand what you mean. My initial thought was that the textboxvisible() will be execute when the user is typing his answer in textbox(i).

But I realize that in order to do I need to define a sub for each textbox. correct?

[fumei]

Correct. Just to emphasize this: "But I don't understand why I must write individual code for each control ? Because the i+1 is always based on value on i."

Take a look at the attached doc. The userform opens on doc_open. As you can see the textboxes do cascade in visibility. Take a look at the code.

[Johnosley]

Correct. Just to emphasize this: "But I don't understand why I must write individual code for each control ? Because the i+1 is always based on value on i."

Take a look at the attached doc. The userform opens on doc_open. As you can see the textboxes do cascade in visibility. Take a look at the code.

Thanks Gerry. Finally I was able to do it with your help. I got almost the same code than that you proposed. I was reading on internet how to call my sub :-)