ComboBox Array troubles

[Chris Stafford]

I am very new to VBA and looking for some guidance.



I'm using a ComboBox (in Word 2007) that is populated by an array. This
allows me to provide an abbreviation and the description of the
abbreviation. Once selected only the abbreviation is visible. Now I need
to display the selected abreactions description in another part for the
document and do not know how to do so. Here is the code I am using for the
ComboBox:



Dim HairArray(8, 2)
ComboBox18.ColumnCount = 2
ComboBox18.ColumnWidths = 30

HairArray(0, 0) = "BLD"
HairArray(1, 0) = "BLK"
HairArray(2, 0) = "BLN"
HairArray(3, 0) = "BRO"
HairArray(4, 0) = "GRY"
HairArray(5, 0) = "MUL"
HairArray(6, 0) = "RED"
HairArray(7, 0) = "SND"
HairArray(8, 0) = "WHI"



HairArray(0, 1) = "BALD"
HairArray(1, 1) = "BLACK"
HairArray(2, 1) = "BLONDE"
HairArray(3, 1) = "BROWN"
HairArray(4, 1) = "GREY"
HairArray(5, 1) = "MULTI - COLORED"
HairArray(6, 1) = "RED"
HairArray(7, 1) = "SANDY"
HairArray(8, 1) = "WHITE"




ComboBox18.List() = HairArray



Thank you for any help you can provide.

 

[Greg Maxey]

Chris,

Say you have a bookmark "BM1" in the document that you want to use as place
to put the selected haircolor. Use something like this in the Userform
commandbutton click event:

Dim oRng As Word.Range
Set oRng = ActiveDocument.Bookmarks("BM1").Range
oRng.Text = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
ActiveDocument.Bookmarks.Add "BM1", oRng
Unload Me

 

[Chris Stafford]

Thanks Greg, this is what I'm looking for.

Is there a way to run this code when the user tabs or clicks out of
ComboBox1 (like a text formfield run marco on exit), in a protected
document? It works in the Private Sub ComboBox1_Change(), until I protect
the document.

Chris

 

[Greg Maxey]

Chris,

Seems odd to mix userforms with protected forms, but have you tried
unprotecting the formfields with the Change event:

If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect
'Your code:
ActiveDocument.Protect wdAllowOnlyFormFields, True
End If

 

[Chris Stafford]

Greg,



This work great! I'm am very new to VBA; "Seems odd to mix userforms with
protected forms". Is there a better or more preferred way to accomplish
this. I have used a combobox in the protected form because of the 25 entry
limit (some of my arrays are 200 plus in length).



Thank you very much for all your help.

 

[Greg Maxey]

Chris,

I have used a combobox in the protected form because of the 25 entry limit
(some of my arrays are 200 plus in length).

In that case it isn't so odd ;-). See:
http://gregmaxey.mvps.org/FormField_UserForm_ListBox.htm

However, if you are using Word2007 then you might consider using content
controls instead of formfields.

 

[thinblueline]

Greg I put together the code you provided and it works. I did (by
accident) hit the delete key while using the combobox and received
runtime error 381 Could not get the list properly. Invalid property
array index. I'm not sure what I'm doing wrong.

Private Sub ComboBox1_Change()

If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect

Dim oRng As Word.Range
Set oRng = ActiveDocument.Bookmarks("BM1").Range
oRng.Text = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
ActiveDocument.Bookmarks.Add "BM1", oRng

ActiveDocument.Protect wdAllowOnlyFormFields, True
End If
Unload Me

End Sub

Chris,

I have used a combobox in the protected form because of the 25 entry limit
(some of my arrays are 200 plus in length).

In that case it isn't so odd ;-).  See:http://gregmaxey.mvps.org/FormField_UserForm_ListBox.htm

However, if you are using Word2007 then you might consider using content
controls instead of formfields.

Greg,

This work great!  I'm am very new to VBA; "Seems odd to mix userforms
with protected forms".  Is there a better or more preferred way to
accomplish this.  I have used a combobox in the protected form
because of the 25 entry limit (some of my arrays are 200 plus in
length).

 

[Greg Maxey]

I don't have time right now to try to recreate your doc/userform. If you
want to e-mail it to me, I will look at it ASAP.

 

[thinblueline]

I don't have time right now to try to recreate your doc/userform. If you
want to e-mail it to me, I will look at it ASAP.

Greg I put together the code you provided and it works.  I did (by
accident) hit the delete key while using the combobox and received
runtime error 381 Could not get the list properly. Invalid property
array index.  I'm not sure what I'm doing wrong.

Private Sub ComboBox1_Change()

If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect

Dim oRng As Word.Range
Set oRng = ActiveDocument.Bookmarks("BM1").Range
oRng.Text = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
ActiveDocument.Bookmarks.Add "BM1", oRng

ActiveDocument.Protect wdAllowOnlyFormFields, True
End If
Unload Me

End Sub

--
Greg Maxey -  Word MVP

My web sitehttp://gregmaxey.mvps.org
Word MVP web sitehttp://word.mvps.org

After some work I came up with this solution...

On my userform I have a combobox and a textbox. This code allows for
the two formfields to be filled without the bookmarks being
destroyed.

Private Sub ComboBox1_Change()
ActiveDocument.FormFields("BM1").Result = ComboBox1.Value
If ComboBox1.ListIndex <> -1 Then
Me.TextBox1.Text = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
ActiveDocument.FormFields("BM2").Result = TextBox1.Text
Else
Me.TextBox1.Text = ""
ActiveDocument.FormFields("BM2").Result = TextBox1.Text
End If
End Sub

Thank you Greg for all your help. Maybe this code will help someone
else too.