Help using TextBox Value

[Ayo]

Hi,
I craeted a UserForm with 5 buttons and 5 texboxes. I entered values
into the textboxes then I hid it and opened another form using:
Me.Hide
frmSaveTo.Show

This new userform also has buttons and a textbox on it. Now I need to use
the values that were entered in the former userform textboxes at this stage.
That is where I am running into a problem. I was using the following snippet
of code to get the value (or text) from the former userform textboxes:
For Each ctl In frmOpenFiles.Controls
If TypeName(ctl) = "TextBox" Then
If Right(ctl.Text, 4) = ".xls" Then
Workbooks.Open Filename:=ctl.Value
End If
But I don't think it is working right.
How do I get the values (or text) from the first userform to use later in
the code, even though I hide the form?

 

[Tom Ogilvy]

should work - since you only hide the form, it should stay loaded and the
values of the textbox are maintained and accessible. Try making these
changes

Dim ctl as Control
For Each ctl In frmOpenFiles.Controls
If typeof ctl is msforms.TextBox Then
If lcase(Right(Trim(ctl.Text), 4)) = ".xls" Then
Workbooks.Open Filename:=ctl.Value
End If
end if
Next

 

[Ayo]

ctl.Text is showing <Object doesn't support this property or method> and
ctl.value is showing 3

Something is wrong. And I don't know what it is. I can't open any of the
files if I keep getting this. Is there any other way you can help.

 

[Tom Ogilvy]

I set this up:

frmOpenFiles:

Private Sub CommandButton1_Click()
Me.Hide
frmSaveTo.Show
Me.Show
End Sub


frmtoSave:

Private Sub CommandButton1_Click()
Dim ctl As Control
For Each ctl In frmOpenFiles.Controls
If TypeOf ctl Is msforms.TextBox Then
If LCase(Right(Trim(ctl.Text), 4)) = ".xls" Then
Workbooks.Open Filename:=ctl.Value
Else
MsgBox ctl.Name & " - " & ctl.Text & ctl.Value
End If
End If
Next

End Sub

Private Sub CommandButton2_Click()
Me.Hide
End Sub


I suspect you are calling back and forth between the forms - I would
recommend against this. Don't
frmOpenFiles shows frmtoSave
frmToSave shows frmOpenFiles

and so forth.

Use the approach I show.

 

[Ayo]

Ok let me see if I can explain what it is I am trying to do here.

I have a Userform, call it Userform1, which contains a MultiPage with 4 pages
Each of the pages contain at least 2 testboxes and a corresponding command
button
I click a button to open a file and assign the file path to the
corresponding textbox
When all the textboxes has been assigned a file path, I click the OK button
This hides userform1 and show userform2
Userform 2 has 2 buttons and 1 textbox. I assign a folder path to the textbox
I click the OK button on this user form and it hides userform2

Now, this is where it stops working

When I click the OK button, apart from hiding userform2, it is surpose to
open each file in the textbox on userform1
copy a sheet from it and close the file
That what is in the folowing snippet of code:
My ctl.Text is not coming up with the correct values

Private Sub cmd_Ok_Click()
Dim cs As Integer, rw As Integer, sh As Integer
frmSaveTo.Hide
frmOpenFiles.Show
cs = 1
rw = 4
sh = 1
Application.DisplayAlerts = False

For Each ctl In frmOpenFiles.Controls
If TypeOf ctl Is msforms.TextBox Then
If Right(ctl.Text, 4) = ".xls" Then
'MsgBox ctl.Name
Workbooks.Open Filename:=ctl.Text
End If
Select Case cs
Case 1, 2, 3, 5, 8 To 11
sh = 1
Case 4, 6, 7, 12 To 17
sh = 2
End Select
With Workbooks(2)
.Worksheets(sh).Copy After:=Workbooks(1).Worksheets(1)
.Close
End With
End If

FillSummary cs, rw
Worksheets(2).Select
ActiveSheet.Delete
cs = cs + 1
rw = rw + 1
Next ctl

Application.DisplayAlerts = True
ActiveWorkbook.SaveAs Filename:=frmSaveTo.txt_Savetofolder.Value
End Sub

 

[Tom Ogilvy]

And I told you NOT to do this:

frmSaveTo.Hide
frmOpenFiles.Show

but you seem to ignored it.

Until you take out
frmOpenFiles.Show

and do it the way I showed you in my example, I think you will continue to
have a hard time.

 

[Ayo]

This is not working either. I added a ctl.Name watch and I am getting
"Multipage1" and ctl.Value = 3.
I believe this is the problem

Private Sub cmd_Ok_Click()
Dim cs As Integer, rw As Integer, sh As Integer
frmSaveTo.Hide
cs = 1
rw = 4
sh = 1
Application.DisplayAlerts = False

For Each ctl In frmOpenFiles.Controls
If TypeName(ctl) = "TextBox" Then
If Right(ctl.Text, 4) = ".xls" Then

Workbooks.Open Filename:=ctl.Text
End If
Select Case cs
Case 1, 2, 3, 5, 8 To 11
sh = 1
Case 4, 6, 7, 12 To 17
sh = 2
End Select
With Workbooks(2)
.Worksheets(sh).Copy After:=Workbooks(1).Worksheets(1)
.Close
End With
End If

FillSummary cs, rw
Worksheets(2).Select
ActiveSheet.Delete
cs = cs + 1
rw = rw + 1
Next ctl

Application.DisplayAlerts = True
ActiveWorkbook.SaveAs Filename:=frmSaveTo.txt_Savetofolder.Value
End Sub

 

[Tom Ogilvy]

Why do you think you have this line of code in your procedure:

If TypeName(ctl) = "TextBox" Then

Perhaps
when typename(ctl) = "Multipage" it doesn't get processed as a textbox.

In you loop you should get every control you have on the useform, but no
processing should occur for those that don't have a typename of "Textbox"