User Form & ComboBox

[C''''est_moi]

First attempt at both. Everything works like a charm. 2 questions however.
Word 2003. Should I post separately?

Question 1.
Is there a way to simplify these repetitions or would it be too complicated
for a beginner like me?

Private Sub cmdFinished_Click()
Dim strAAA As String
Dim strBBB As String
Dim strCCC As String
Dim strDDD As String
'and so on

strAAA = UserForm1.txtAAA.Text
strBBB = UserForm1.txtBBB.Text
strCCC = UserForm1.txtCCC.Text
strDDD = UserForm1.txtDDD.Text

Module1.RewrapBookmark "bkAAA", strAAA
Module1.RewrapBookmark "bkBBB", strBBB
Module1.RewrapBookmark "bkCCC", strCCC
Module1.RewrapBookmark "bkCCC", strDDD

UserForm1.Hide
Unload UserForm1
End Sub

Question 2.
Let's say I have 3 items in the Combo DD. How can I link each item to a
number of bkmarks in the form (also to rewrap as above of course).

Ex.: user chooses "item 1" and bkmark 1, bkmark 2, bkmark 3 (...) are
updated with different info in each bkmark. This code has to fit in the above
of course (on finish command button).

PS: I already created a form where I used "Select Case" (5 cases and 7
bkmarks that update depending on a choice in DD field) which also works like
a charm. Can I use the same and if so, how to link the cases to the combo
items.

Any suggestion or reading material on both questions to guide me in the
direction where I want to go would be very mcuh appreciated.

Merci/Thx a million.

 

[Doug Robbins - Word MVP]

If your cmdFinished_Click() is inserting the data from the form into the
range of some bookmarks, you can certainly do it with less lines of code as
follows:

With ActiveDocument
.Bookmarks("bkAAA").Range.InsertBefore txtAAA.text
.Bookmarks("bkBBB").Range.InsertBefore txtBBB.text
.Bookmarks("bkCCC").Range.InsertBefore txtCCC.text
.Bookmarks("bkDDD").Range.InsertBefore txtDDD.text
End With

Re your second question, if your combobox contains multiple columns and you
want to insert into a series of bookmarks, the data from the columns for the
selected item in the combobox, you use the .BoundColumn property of the
combobox as follows

With ActiveDocument
ComboDD.BoundColumn = 1
.Bookmarks("bmark1").Range.InsertBefore ComboDD.Value
ComboDD.BoundColumn = 2
.Bookmarks("bmark2").Range.InsertBefore ComboDD.Value
ComboDD.BoundColumn = 3
.Bookmarks("bmark3").Range.InsertBefore ComboDD.Value
End With

See the following pages of fellow MVP Greg Maxey's website:

http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Gordon Bentley-Mix]

Carmen,

In re: Question 1
Since this code is in the UserForm module, you don't need load the values
from the TextBoxes into String variables first. It should be sufficient just
to pass the value to the RewrapBookmark sub directly. However, there are a
couple of improvements that could be made. I've rewritten your code to
demonstrate, with an explanation to follow.

Private Sub cmdFinished_Click()
RewrapBookmark "bkAAA", CStr(Trim(txtAAA.Value))
RewrapBookmark "bkBBB", CStr(Trim(txtBBB.Value))
RewrapBookmark "bkCCC", CStr(Trim(txtCCC.Value))
RewrapBookmark "bkCCC", CStr(Trim(txtDDD.Value))
etc...

UserForm1.Hide
Unload UserForm1
End Sub

Now the explanation.

As you can see, I've done away with the String variables for the reasons
discussed above. In addition, I've removed all of the "explicit" references
to UserForm1 and Module1. (And you should really consider giving these
meaningful names instead of just using the default names created by the VBE -
just for good practice.) I've done this because these explicit references
aren't really required because: a) for the TextBoxes the context will be
assumed to be the UserForm because the code is in the UserForm module; and b)
if the RewrapBookmark procedure is declared as 'Public' (or at least not
'Private' - and it must be or it wouldn't be accessible) then specifiying the
context isn't required.

I also used the .Value property of the TextBoxes rather than .Text. Either
will work but I'm just in the habit of using .Value. In addition, I've used a
couple of built-in functions to make sure that the right value is passed to
RewrapBookmark. "Trim" will remove any leading or trailing spaces from the
value, and "CStr" will force conversion of the value to the String value that
I assume is required by RewrapBookmark. This elimates the need to load the
TextBox values into String variables first (which probably should have been
done using Trim and CStr anyway...).

That should cover Question 1. Now for Question 2.

You can certainly update multiple bookmarks with different values depending
on the value selected from a ComboBox. In this case, I would use a Select
Case statement that evaluates the .ListIndex property of the ComboBox, like
so:

Select Case ComboBox1.ListIndex
Case 1
RewrapBookmark "bkmark1", "This is the first value for bookmark 1"
RewrapBookmark "bkmark2", "This is the first value for bookmark 2"
RewrapBookmark "bkmark3", "This is the first value for bookmark 3"
Case 2
RewrapBookmark "bkmark1", "This is a different value for bookmark 1"
RewrapBookmark "bkmark2", "This is a different value for bookmark 2"
RewrapBookmark "bkmark3", "This is a different value for bookmark 3"
Case 3
RewrapBookmark "bkmark1", "This is yet a different value for bookmark 1"
RewrapBookmark "bkmark2", "This is yet a different value for bookmark 2"
RewrapBookmark "bkmark3", "This is yet a different value for bookmark 3"
Case Else
MsgBox "I don't know what to do for this ComboBox selection.",
vbCritical, "ComboBox1 Error"
End Select

This code can go into the Click event procedure, but if I might make another
suggestion on improving the code - not neccessarily to make it more efficient
but at least to make it easier to maintain.

Rather than putting everything into one huge procedure tied to the Click
event, break it up into a few logical, smaller procedures (sub-routines) and
then call these from within the Click event procedure. For example, you could
write a procedure for inserting all of the values from the TextBoxes like
this:

Private Sub InsertTextBoxValues()
RewrapBookmark "bkAAA", CStr(Trim(txtAAA.Value))
RewrapBookmark "bkBBB", CStr(Trim(txtBBB.Value))
RewrapBookmark "bkCCC", CStr(Trim(txtCCC.Value))
RewrapBookmark "bkCCC", CStr(Trim(txtDDD.Value))
End Sub

And then write another (maybe called "InsertComboBox1Values") that has the
sample code I provided above to insert the values into the various bookmarks
based on the value selected from ComboBox1. Then you would call these in your
CommandButton Click event code as follows:

Private Sub cmdFinished_Click()
InsertTextBoxValues
InsertComboBox1Values
UserForm1.Hide (or Me.Hide)
Unload UserForm1
End Sub

The two big advantages are:
1) You can use a "top down" programming approach to your template
development in which you break the project down into big chunks and then
slightly smaller bits within those chunks and then finally the code to do
those bits. (Note that "chunks" and "bits" are technical terms. ;-D) Then you
can work on a piece at a time instead of trying to develop the whole code
base in one big hit.
2) It makes debugging easier because if the code falls over, usually only
one piece doesn't execute properly. If all the code is in one procedure, it
stops everything else from executing and just throws control back to the top,
which can make it very difficult to find the problem.
However, with "modular" code if one sub-routine fails, execution picks up
again with the next subroutine, and you can see from the results in the
document which piece didn't execute properly.
Also, if you want to skip over something that's not fully developed yet, you
can comment out just one line of code - the call to the sub-routine within
the main procedure - rather than commenting out heaps of lines of code.

Anyway, this should be enough to get you headed in the right direction. I
won't say anything about separating out the code for manipulating the
document from the code related to the UserForm or talk about the dangers of
"magic forms" and not instantiating specific instances of objects and
referring to these objects instead of "UserForm1" or "ActiveDocument". I
figure you're head is probably pretty full already. ;-P

(And I see that in the time it took me to put this together, Doug has
chipped in with an answer as well. His recommendation for using a
multi-column ComboBox is a good solution, as well. However, I don't think he
knows what your "RewrapBookmark" code does because he wasn't involved in the
discussion we had earlier - so don't hold that against him. ;-D)
--
Cheers!

Gordon Bentley-Mix
Word MVP

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Gordon Bentley-Mix]

Doug,

Just to "fill you up" <g>: Carmen and I had a discussion sometime back about
inserting text into a bookmark safely and efficiently. (At least I _think_ it
was Carmen; it might have been something she picked up from one of my other
posts.) In that discussion, I recommended using a 'generic' procedure that
accepts arguments for doing this operation. This is something that I do all
the time using code that looks like this:

Public Sub InsertBookmarkValue(BkmkName As String, InsertValue As String)
With ActiveDocument
If .Bookmarks.Exists(BkmkName) Then
Dim myRange As Range
Set myRange = .Bookmarks(BkmkName).Range
myRange.Text = InsertValue
.Bookmarks.Add BkmkName, myRange
End If
End With
End Sub

As you can see, it's got some advantages over the InsertBefore method in
that it checks for the existence of the bookmark first and then re-inserts
the bookmark afterwards (which I require because I have 're-run'
functionality in most of my templates and need the bookmarks to remain for
the next time). I reckon Carmen's "RewrapBookmark" does something similar,
which is why my answer is a bit different from yours...
--
Cheers!

Gordon Bentley-Mix
Word MVP

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[ItsMe]

Actually Gordon, my head is not "pretty full" as you suggested... I believe
it has been detached from my shoulders while I was reading your reply and it
is rolling on the floor even as I am typing my reply... Thank God you do not
charge by the word. I read your reply carefully and everything made sense
which I take as a positive sign that I am improving my skills. I will look at
that more closely when I am back at the office on Monday. Let me tell you
that if your office were next to mine, I definitely would stop by on Monday
morning with a big --huge-- box of chocolates; well... maybe not, this is for
girls usually. A box of cigars let's say... well... but smoking isn't the
most popular activity these days (at least here in Quebec) so you probably
don't smoke... Well, I would figure something out. Nice bottle of wine... hum
that sound good... At least I would start out with a big hug and a kiss. Thx
so very much for all your help and suggestions. Without you, I most certainly
would never have progressed as much as I did.

You were right about the rewrap bookmark. Took that either from you (or Greg
maybe, on a previous post).

For now, on my way to yoga class in the hope of reattaching my head to my
shoulders before Monday morning.

Have a nice w-end and, again, very very sincerely, thank you.
Carmen