Having a control paste text into a table

[John Eddy]

Ok I've tried several different wasy to do this and can't
quite get it right. I have a table that I would like to
paste text into based on a selection someone makes from
checkboxes on a userform. The kicker is that I need them
to be able to select more than one checkbox and have the
selected text be pasted in from each checkbox. I suppose
an "if" statement would work but I'm still a beginner and
I'm not sure how to address this problem.

Basically I'd like the user to be able to make as many
selections as they'd like and when they click on my "OK"
button have the corresponding text pasted into the table.

Would a code that pastes one line if they only choose one
box and have each possible choice of multiple choices all
lined up in an if statement work? And if so how would that
code look? I can't seem to find anything like that in my
books or the online help!

Thanks in advance for any help!

John

 

[Jay Freedman]

Hi, John,

The idea of having a giant If statement that covers all possible choices is
not a good one, as you may already know. It would be a huge pile of
spaghetti code that you would have a hard time debugging or maintaining, and
it might easily grow large enough to be noticeably sluggish.

There's a little trick I use a lot that will help. VBA unfortunately doesn't
have the concept of control arrays like VB, but if you use the same base
name for all the controls of one type plus a sequence number (exactly the
way they default: CheckBox1, CheckBox2, etc.), you can pretend that they're
in an array and address them as Controls("CheckBox" & number). That lets you
handle them in a For loop.

Have a look through this sample, and let me know if you need anything else
explained.

In a regular macro module:
--------------
Sub GetChex()
Dim dlg As UserForm1

Set dlg = New UserForm1
dlg.Show

Set dlg = Nothing
End Sub
--------------

In the userform, UserForm1:
--------------
Private Sub CommandButton1_Click()
Dim MyText As Variant
Dim NewDoc As Document
Dim MyTable As Table
Dim BoxNum As Integer

' stuff an array with the text that will
' go into the document
MyText = Array("You checked box 1", _
"You checked box 2", _
"You checked box 3")

' see if there's anything to do
If Not (CheckBox1.Value Or CheckBox2.Value _
Or CheckBox3.Value) Then
' nothing was checked
Me.Hide
Exit Sub
End If

' make a new doc and put a table in it
Set NewDoc = Documents.Add
Set MyTable = NewDoc.Tables.Add( _
Range:=NewDoc.Range, numrows:=1, numcolumns:=1)

' populate the table with text chosen by
' whether the corresponding checkbox is checked
For BoxNum = 1 To 3
If Controls("CheckBox" & BoxNum).Value Then
MyTable.Cell(MyTable.Rows.Count, 1).Range.Text = _
MyText(BoxNum - 1)
' the array starts with 0, not 1
MyTable.Rows.Add
End If
Next BoxNum

' there is an extra row, to be deleted
MyTable.Rows(MyTable.Rows.Count).Delete
Me.Hide
End Sub