Combo box won't populate

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cissy - 11 May 2006 16:09 GMT

Hi, I'm using Word XP. I must be missing something, but the following code
doesn't work - the combo box won't populate. Thanks for any help.

Option Explicit

Private Sub Document_New()
Dim ofrmLetterhead As frmLetterhead
Dim myRange As Range
Dim combobox1 As ComboBox

Set ofrmLetterhead = New frmLetterhead

With ofrmLetterhead
.txtRecName = ""
.txtRecFirm = ""
.txtAdd1 = ""
.txtAdd2 = ""
.txtCityStateZip = ""
.txtRecName.SetFocus

End With

ofrmLetterhead.Show

If btnOKclicked = True Then

If ofrmLetterhead.optCertified = True Then
ActiveDocument.Bookmarks("bkdelivery").Range.Text = "VIA CERTIFIED MAIL"
End If

If ofrmLetterhead.optFacUS = True Then
ActiveDocument.Bookmarks("bkdelivery").Range.Text = "VIA FACSIMILE AND
U.S. MAIL"
End If

ActiveDocument.Bookmarks("bkRe").Range.Text = ofrmLetterhead.txtRe
ActiveDocument.Bookmarks("bkCC").Range.Text = ofrmLetterhead.txtCC
ActiveDocument.Bookmarks("bkSalutation").Range.Text =
ofrmLetterhead.txtSalutation
ActiveDocument.Bookmarks("bkRecName2").Range.Text =
ofrmLetterhead.txtRecName

If ofrmLetterhead.txtAdd1.Text = "" Then
With ActiveDocument.Bookmarks("bkAdd1")
.Range.Paragraphs(1).Range.Delete
.Delete ' remove the bookmark itself, too
End With
Else
ActiveDocument.Bookmarks("bkAdd1").Range.Text =
ofrmLetterhead.txtAdd1.Text
End If

Set myRange = ActiveDocument.Bookmarks("bkSenderName").Range

myRange.Text = frmLetterhead.combobox1.Value

'Use drop-down list
ofrmLetterhead.combobox1.Style = fmStyleDropDownList
'Combo box values are ListIndex values
ofrmLetterhead.combobox1.BoundColumn = 0
'Set combo box to first entry
ofrmLetterhead.combobox1.ListIndex = 0

With ofrmLetterhead

.combobox1.AddItem "Joe Barber" 'list index 0
.combobox1.AddItem "Sue Turner Budd" ' list index 1
.combobox1.AddItem "Alice Cohen"
.combobox1.AddItem "Tom Chase"
.combobox1.AddItem "Julie Detrick"

End With

Selection.GoTo What:=wdGoToBookmark, Name:="bkStart"

Else
ActiveDocument.Close wdDoNotSaveChanges

End If

Unload ofrmLetterhead
Set ofrmLetterhead = Nothing

End Sub

Private Sub Paste()
'
'
Selection.HomeKey Unit:=wdStory
Selection.GoTo What:=wdGoToBookmark, Name:="bkRecName"
With ActiveDocument.Bookmarks
.DefaultSorting = wdSortByName
.ShowHidden = False
End With

Selection.PasteAndFormat (wdPasteDefault)

'Selection.TypeBackspace
'Selection.Delete Unit:=wdCharacter, Count:=1
End Sub

Reply to this Message

Greg Maxey - 11 May 2006 16:51 GMT

Cissy,

It is not working\showing because you are showing the form and trying
to act on the form results before building the combobox lists.

You need to move that code (and a large part of the rest) into the
Userform_Initialize event code:

Private Sub UserForm_Initialize()
Me.ComboBox1.Style = fmStyleDropDownList
Me.ComboBox1.BoundColumn = 0
With Me
.ComboBox1.AddItem "Joe Barber" 'list index 0
.ComboBox1.AddItem "Sue Turner Budd" ' list index 1
.ComboBox1.AddItem "Alice Cohen"
.ComboBox1.AddItem "Tom Chase"
.ComboBox1.AddItem "Julie Detrick"
End With
Me.ComboBox1.ListIndex = 0
End Sub

Reply to this Message

Cissy - 11 May 2006 17:35 GMT

I'm excited - it worked to populate the combo box, but it puts a "0" at the
bookmark in the document instead of the name, no matter what name I choose.
What am I doing wrong?

Can I ask why we had to put it behind the form and not with the other code
in the ThisDocument area? Just trying to figure this out. Thank you for
your time.

> Cissy,
>
[quoted text clipped - 16 lines]
> Me.ComboBox1.ListIndex = 0
> End Sub

Reply to this Message

Greg Maxey - 11 May 2006 17:51 GMT

Cissy,

Hopefully you have read my second post and understand why you where
getting the 0 instead of a name.

I also meant to say that we both stand to be *corrected* regarding the
best approach for building your form. I only dabble in VBA and
certainly do not understand or apply all of the standard conventions.
However, I feel certain that you should use the initialize event of the
userform to set it up prior to display.

Reply to this Message

Greg Maxey - 11 May 2006 17:16 GMT

Cissy,

Also .ComboxBox1.Value will return the index value not the actual
name.

Use .ComboBox1.Text

We both stand to be correct here (and I had to take some things out as
I didn't understand exactly what you are doing), but if I was building
this userform I would approach it more like:

In the project:

Private Sub Document_New()
Dim ofrmLetterhead As frmLetterhead
Set ofrmLetterhead = New frmLetterhead
ofrmLetterhead.Show
Unload ofrmLetterhead
Set ofrmLetterhead = Nothing
End Sub

In the Userform:

Option Explicit
Private Sub CmdBtnFinished_Click()
ProcessForm
Me.Hide
End Sub

Private Sub UserForm_Initialize()
With Me
.txtRecName = ""
.txtRecFirm = ""
.txtAdd1 = ""
.txtAdd2 = ""
.txtCityStateZip = ""
.txtRecName.SetFocus
With .ComboBox1
.Style = fmStyleDropDownList
.BoundColumn = 0
.List = Array("Joe Barber", "Sue Turner Budd", _
"Alice Cohen", "Tom Chase", _
"Julie Detrick")
.ListIndex = 0
End With
End With
End Sub

Sub ProcessForm()
Dim myRange As Range
Dim oBkMark As Bookmarks
Set oBkMark = ActiveDocument.Bookmarks
oBkMark("bkRe").Range.Text = Me.txtRe
oBkMark("bkCC").Range.Text = Me.txtCc
oBkMark("bkSalutation").Range.Text = Me.txtSalutation
oBkMark("bkRecName2").Range.Text = Me.txtRecName
If Me.txtAdd1.Text = "" Then
With oBkMark("bkAdd1")
.Range.Paragraphs(1).Range.Delete
.Delete
End With
Else
oBkMark("bkAdd1").Range.Text = Me.txtAdd1.Text
End If
Set myRange = oBkMark("bkSenderName").Range
myRange.Text = Me.ComboBox1.Text
Selection.GoTo What:=wdGoToBookmark, Name:="bkStart"
End Sub

Reply to this Message

Cissy - 11 May 2006 18:02 GMT

It works perfectly. Greg, thanks for taking the time to explain this to me
and to redo my project so I can see how it should be done in the first place.
You have certainly gone beyond the call of duty!

> Cissy,
>
[quoted text clipped - 64 lines]
> Selection.GoTo What:=wdGoToBookmark, Name:="bkStart"
> End Sub

Reply to this Message

Greg Maxey - 11 May 2006 18:11 GMT

Cissy,

My pleasure. As I said in a previous post, I am not dead certain that
calling ProcessForm in the Userform cmdbtnFinished_Click event is the
proper convention. I have seen it done that way, in the calling macro
as you proposed initially and in the project as a separate macro. The
third method seems to require a rather convoluted process of passing
data to and from the form and so I avoided it.

True masters please chime in here and educate Cissy and me.

Reply to this Message

Jean-Guy Marcil - 11 May 2006 21:37 GMT

Greg Maxey was telling us:
Greg Maxey nous racontait que :

> Cissy,
>
[quoted text clipped - 6 lines]
>
> True masters please chime in here and educate Cissy and me.

I am not, by far, a true master. But, you could have everything in the
calling sub, which is what I usually try to do. I like to keep the userform
code as lean as possible so that the userform can be easily used in other
projects "as is".

'_______________________________________
Option Explicit
Private Sub Document_New()

Dim ofrmLetterhead As frmLetterHead
Dim myRange As Range
Dim oBkMark As Bookmarks

Set oBkMark = ActiveDocument.Bookmarks
Set ofrmLetterhead = New frmLetterHead

With ofrmLetterhead
.boolProceed = True
.txtRecName = ""
.txtRecFirm = ""
.txtAdd1 = ""
.txtAdd2 = ""
.txtCityStateZip = ""
.txtRecName.SetFocus
With .ComboBox1
.Style = fmStyleDropDownList
.BoundColumn = 0
.List = Array("Joe Barber", "Sue Turner Budd", _
"Alice Cohen", "Tom Chase", _
"Julie Detrick")
.ListIndex = 0
End With

.Show
If .boolProceed Then
oBkMark("bkRe").Range.Text = .txtRe
oBkMark("bkCC").Range.Text = .txtCc
oBkMark("bkSalutation").Range.Text = .txtSalutation
oBkMark("bkRecName2").Range.Text = .txtRecName
If .txtAdd1.Text = "" Then
With oBkMark("bkAdd1")
.Range.Paragraphs(1).Range.Delete
End With
Else
oBkMark("bkAdd1").Range.Text = .txtAdd1.Text
End If
Set myRange = oBkMark("bkSenderName").Range
myRange.Text = .ComboBox1.Text
Else
MsgBox "Procedure cancelled by user"
End If
End With

Unload ofrmLetterhead
Set ofrmLetterhead = Nothing

Selection.GoTo What:=wdGoToBookmark, Name:="bkStart"

End Sub
'_______________________________________

And in the userfrom:

'_______________________________________
Option Explicit
Public boolProceed As Boolean

'_______________________________________
Private Sub CmdBtnCancel_Click()

Me.boolProceed = False
Me.Hide

End Sub
'_______________________________________

'_______________________________________
Private Sub CmdBtnFinished_Click()

Me.Hide

End Sub
'_______________________________________

By the way, there are controls on the userform that are not being use to
populated the document and,
If .txtAdd1.Text = ""
is true, then
.Range.Paragraphs(1).Range.Delete
.Delete
will generate an error because of the double .Delete

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Reply to this Message

Greg Maxey - 11 May 2006 22:24 GMT

JGM,

Well at least you run with pack ;-)

Thanks for you input. Actually I don't think I have ever seen that much
code in the calling macro and was surprised by Cissy's original code. Of
course it works, but it seems that one or more sages has mildly chastised me
for putting *too much* code in the calling macro. I personally like the
ProcessForm idea, but as I mentioned, if it is put in the project you have
all the variables to declare as public blah, blah.

Will be interesting to see what others might say.

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

> Greg Maxey was telling us:
> Greg Maxey nous racontait que :
[quoted text clipped - 100 lines]
> .Delete
> will generate an error because of the double .Delete

Reply to this Message