ListBox question

[Fuzzhead]

I have the following:

Step #1: With my document open, I open my form and input information.

Step #2: When saving my form information back to my document I load all the
information into a variable. Each field is divided by a ‘\’. Each time I save
the form it added a new record to my variable.

Step #3: I open a new form with a Listbox and load it with my variable. So
in the list box you would see something like this: ‘10/26/2007\NO\Testing\No
change, it all looked good’ on the first line and if the form had been saved
a second time, the second line it would look like this:
‘10/31/2007\YES\Testing\No change, it all looked good’ and so on each time
the form is saved.

Can the following be done?

Step #4: I pick one of the items in the Listbox, then click on my command
button and it would load that information into text boxes in a different
form. The 10/26/2007 would go into TextBox1, ‘NO’ would go into TextBox2,
‘Testing’ into TextBox3 and so on.

 

[Russ]

The split() function can take a string of text and break it down into an
array by separating the text by a delimiter, in this case the backslash
character. Then yourArray(1) would be the date, the yourArray(2) would be
"NO", etc.

 

[Fuzzhead]

Thanks for answering. So once I highlight one of the strings in my list, how
do I get it to my viewing form?

 

[Russ]

Run this for a clue for how to use the split function:

Public Sub SplitTest()
Dim myArray As Variant
Dim myLong As Long
'splits string delimited by backslash character.
myArray = Split("This\is\a\test.", "\")

For myLong = LBound(myArray) To UBound(myArray)
MsgBox "<<" & myArray(myLong) & ">>" & " array element #" & myLong
Next myLong

End Sub

Like you said earlier, your command button code would act on your choice by
parsing it with the split() function and by setting each textbox to a part
of the string.

 

[Fuzzhead]

Hi Russ,

I don’t understand how to apply you macro to my text boxes. Every line in
my ListBox has different information in it. It could look like this:

This\is\a\test.
The cow\jumped\over the\moon
There once was a cow\that\jumped\over the moon

I don’t know how you would load this into a Split(). The text is different
in each line

So when I get to this line in your macro example:
myArray = Split("This\is\a\test.", "\")
do I change it to this:
myArray = Split("\")

then if I highlighted the second one, I have no idea how you get it into the
text boxes?

Text box (TB1) would have “The cow†in it.
Text box (TB2) would have “jumped†in it
Text Box (TB3) would have “over the†in it
Text Box (TB4) would have “moon†in it.

 

[Russ]

Hi FuzzHead,
In vba help you'll see what the split function requires. The first string is
split by the second string. So you would split your listbox value by the
string "\" and place it into myArray. Then you would iterate through each
element of myArray like the the SplitTest subroutine did to set each textbox
in your document.
You could try in the command button code:

Dim myArray As Variant
Dim myLong As Long

'splits string delimited by backslash character.
myArray = Split(me.listbox1.value, "\")

For myLong = LBound(myArray) To UBound(myArray)
MsgBox "<<" & myArray(myLong) & ">>" & " array element #" & myLong
ActiveDocument.Shapes("Text Box " & myLong + _
1).TextFrame.TextRange.Text = myArray(myLong)
Next myLong
....
Provided your document texboxes are named:
Text Box 1
Text Box 2
Etc.
And your listbox is named ListBox1

 

[Fuzzhead]

I am getting the following error message:
The item with the specified name wasn't found.

When I go to the Debug it show this line as the problem:
ActiveDocument.Shapes("TextBox" & myLong + _
1).TextFrame.TextRange.Text = myArray(myLong)

 

[Russ]

Fuzzhead,
You have to find out exactly what your textboxes are named in your document.
Verbatim, with spaces and all.

You could run this piece of code:

Public Sub myShapeNames()
Dim myShape As Word.Shape
For Each myShape In ActiveDocument.Shapes
MsgBox myShape.Name
Next myShape
End Sub


...."TextBox" & myLong + 1 ...
Is looking for
TextBox1
TextBox2
Etc. in the loop

 

[Russ]

Fuzzhead,
The misunderstanding is on my end. I haven't been using form fields and I am
not that familiar with them. I tested by entering a 'plain textbox shape'
under the menu Insert/Textbox. I now realize that you are talking about a
textbox formfield. I'll have to research more on how to address those fields
and get back to you. Someone else with more experience with forms might pipe
in and say how you load a formfield textbox with default text via VBA code.
I tried recording a macro, but that didn't work.

 

[Russ]

Fuzzhead,
Try this adjustment for formfields inside the loop.

ActiveDocument.FormFields("Text" & myLong + _
1).Result = myArray(myLong)

That will inject text into text fields with the bookmark names of
Text1
Text2
Etc.

 

[Fuzzhead]

Russ,

I think that I didn’t explain myself right after looking at your response.
What I do is open a FORM and in this form is ListBox1 on the left side and on
the right side are TextBoxes and Check boxes. I was trying to split the item
in the list box and populate that info into the TextBoxes and Check boxes on
the left side. I tried to split the list box item and load it into variables
and then make the text boxes and check boxes equal to the variables. But when
I run it the information does not load into the variables.


Dim bk1, bk2, bk3, bk4
Dim myArray As Variant
Dim myLong As Long
'splits string delimited by backslash character.
myArray = Split(Me.ListBox1.Value, "\")
ActiveDocument.Variables("bk" & myLong + 1).Value = myArray(myLong)
Next myLong
TBox1 = bk1
TBox2 = bk2
Check1 = bk3
TBox3 = bk4

 

[Russ]

Fuzzhead,
It seems like there is always a mix-up between a 'userform' popup dialog and
a document form with form fields. (Maybe they should have named the
userform, "UserPopupDialog".) In this case, at this moment, it sounds like
you are staying within the userform code.

You're not using simple declared variables, but document variables, so you
have to tell VBA where they were stored. A checkbox's value is either True
or False for checked or unchecked, respectively. So try:

me.TBox1.Value = ActiveDocument.Variables("bk1").Value
me.TBox2.Value = ActiveDocument.Variables("bk2").Value
me.Check1.Value = ActiveDocument.Variables("bk3").Value 'True or False
me.TBox3.Value = ActiveDocument.Variables("bk4").Value

And if you use Option Explicit the compiler should have complained about
those undeclared variables (the simple bk1, bk2, etc.).
http://www.cpearson.com/excel/DeclaringVariables.aspx

 

[Russ]

Fuzzhead,
I see now, that you did declare bk1, bk2, etc.
However you should have declared them for the purpose that they were
intended. In this case as Strings, so that if they were used for something
other than a String, the compiler would complain. That is a simple method of
error checking and uses less memory than a Variant, which is used, if you
don't specify a type of variable.

So in this case, even though they are not needed you should have used:
Dim bk1 As String, bk2 as String, bk3 As String, bk4 As String

In this case though, we don't store anything in them, in this short piece of
code, we are just using them as names of document variables.

 

[Fuzzhead]

Russ,

That did it. It works great now. Thank you so much for your help.

Fuzzhead