PDA

View Full Version : Solved: Pass Multiple Values from A Textbox



jo15765
02-22-2012, 05:57 AM
I have a Excel UserForm that has a textbox on it. Pretty straightforward and simple. The code behind the textbox is:

Dim BookNames
Dim BookName
Dim wb As Excel.Workbook
Dim q

BookNames = Array(UserForm_One.TextBox1.Text)

For Each BookName In BookNames
Call Daily_Report(BookName)
Call Cumulative_Manual(BookName)
Next BookName


My question is, is it possible for me to enter more than one Book Name into that textbox and have it enter the Array at a time (w/o doing mass changes to coding)?

Kenneth Hobs
02-22-2012, 06:30 AM
Dim Booknames() as String and use Split() and set the delimiter to vblf or whatever you delimit the textbox value by.

jo15765
02-22-2012, 06:32 AM
How would I use Split()? I have never used this before...

Kenneth Hobs
02-22-2012, 06:37 AM
To find help in VBE, press F1 with cursor in or near the keyword. Or just use F2 to browse to the command.

Private Sub CommandButton1_Click()
Dim BookNames() As String
Dim BookName As Variant
Dim wb As Excel.Workbook
Dim q

BookNames() = Split(UserForm_One.TextBox1.Text, vbLf)
MsgBox Join(BookNames(), vbLf)

For Each BookName In BookNames()
Call Daily_Report(BookName)
Call Cumulative_Manual(BookName)
Next BookName
End Sub

jo15765
02-22-2012, 06:51 AM
I pressed F1 on the vbLF looking to see if it would show me other options that I could use such as a comma or a semi-colon, but it didn't :( Is it possible to use a comma as my separator?

Kenneth Hobs
02-22-2012, 07:14 AM
vblf is a constant. Join() is the command that contains the delimiter parameter.

jo15765
02-22-2012, 07:27 AM
Ah okay, the catch I kept hitting was I needed to add the delimiter to both the JOIN and the SPLIT functions. Thanks for the examples as well as the guidance!