PDA

View Full Version : Solved: Looping in Userform



clvestin
11-27-2005, 06:53 AM
I'm trying to bring up a Userform containing only a textbox and a command button. The user enters a short memo note and enters. the Userform form will continue to come up until no text is entered. These are then used(each note stored in an element of a string array and placed as bulleted items in a Word document). However, the problem concerns exiting the loop: When the Userform is hidden or closed, the test condition (str="") is no longer met and the loop resumes. I need to get back to the calling routine.


Private Sub CommandButton1_Click()
'If TextBox1.Text = "" Then GoTo getout
If i <> 0 Then i = i + 1
Do
ReDim Preserve alphastr(i)
If TextBox1.Text = "" Then Exit Do
alphastr(i) = TextBox1.Text

i = i + 1
Unload Me
Load UserForm1
UserForm1.Show
Loop Until alphastr(i) = ""
'getout:
'TextBox1.Text = Empty
UserForm1.Hide
'Call Module1.MakeMemos
End Sub

tpoynton
11-27-2005, 08:15 AM
Greetings - you should probably have an "end if" in there after the loop; also try putting an "exit sub" there after the loop ends (maybe after the .hide) so that it doesnt start over again...

MWE
11-27-2005, 08:19 AM
I'm trying to bring up a Userform containing only a textbox and a command button. The user enters a short memo note and enters. the Userform form will continue to come up until no text is entered. These are then used(each note stored in an element of a string array and placed as bulleted items in a Word document). However, the problem concerns exiting the loop: When the Userform is hidden or closed, the test condition (str="") is no longer met and the loop resumes. I need to get back to the calling routine.


Private Sub CommandButton1_Click()
'If TextBox1.Text = "" Then GoTo getout
If i <> 0 Then i = i + 1
Do
ReDim Preserve alphastr(i)
If TextBox1.Text = "" Then Exit Do
alphastr(i) = TextBox1.Text

i = i + 1
Unload Me
Load UserForm1
UserForm1.Show
Loop Until alphastr(i) = ""
'getout:
'TextBox1.Text = Empty
UserForm1.Hide
'Call Module1.MakeMemos
End Sub


If you Unload the UserForm, control will pass back to the calling procedure; e.g., Unload UserForm1

malik641
11-27-2005, 08:28 AM
I'm sorry, but I'm not sure I understand the logic behind this. You said that a user will enter text for a short memo and the userform will keep reappearing until the user enters nothing. Why do you need to unload and reload the userform? Why not just clear the text after the command button is clicked, set the focus back on the text box, and keep the userform up?

And I would ask for an example...but unfortunately I'm at work (I know...on sunday :mkay ....it's okay though, I'll be in Florida on Tuesday :dance: ...I know this is unnecessary for this post..but I'm excited!) and I don't have winzip or windows XP.

mdmackillop
11-27-2005, 10:01 AM
Try the following code in your userform; this should write your comments into the Excel file.
Regards
MD

Dim Alphastr(), i As Long
Private Sub UserForm_Initialize()
i = -1
End Sub
Private Sub CommandButton1_Click()
i = i + 1
ReDim Preserve Alphastr(i)
Alphastr(i) = TextBox1.Text
If TextBox1 <> "" Then
TextBox1 = ""
TextBox1.SetFocus
Exit Sub
End If
WriteText i
Unload UserForm1

End Sub

Sub WriteText(i)
Dim j As Long
For j = 0 To i
Cells(j + 1, 3) = Alphastr(j)
Next
End Sub

clvestin
11-27-2005, 10:40 AM
These are all excellent ways to proceed. I'm afraid my head is kinda in a "loop while until if do" sort of whirl right now. thx