Comb box. how to store the selections??

[scrubgrub]

I'm only mildly familiar with VBA so forgive me, I've scoured the
internet trying to figure this out and have figured out how to build
what I need, but I cannot figure out the code to save the information
selected . . . so store the selections. Can anyone help?

Here's my code as it stands now:

Sub AutoNew()
FillCombo
FillCombo2
FillCombo3
FillCombo4
FillCombo5
End Sub

Sub AutoOpen()
FillCombo
FillCombo2
FillCombo3
FillCombo4
FillCombo5
End Sub

Private Sub FillCombo()
Dim oCB As MSForms.ComboBox
Set oCB = ActiveDocument.ComboBox1
With oCB
.Clear
.AddItem " "
.AddItem "Does Not Meet"
.AddItem "Marginal"
.AddItem "Meets"
.AddItem "Exceeds"
.ListIndex = 0
End With
Set oCB = Nothing
End Sub

Private Sub FillCombo2()
Dim oCB As MSForms.ComboBox
Set oCB = ActiveDocument.ComboBox11
With oCB
.Clear
.AddItem " "
.AddItem "Does Not Meet"
.AddItem "Marginal"
.AddItem "Meets"
.AddItem "Exceeds"
.ListIndex = 0
End With
Set oCB = Nothing
End Sub

Private Sub FillCombo3()
Dim oCB As MSForms.ComboBox
Set oCB = ActiveDocument.ComboBox111
With oCB
.Clear
.AddItem " "
.AddItem "Does Not Meet"
.AddItem "Marginal"
.AddItem "Meets"
.AddItem "Exceeds"
.ListIndex = 0
End With
Set oCB = Nothing
End Sub

Private Sub FillCombo4()
Dim oCB As MSForms.ComboBox
Set oCB = ActiveDocument.ComboBox112
With oCB
.Clear
.AddItem " "
.AddItem "Does Not Meet"
.AddItem "Marginal"
.AddItem "Meets"
.AddItem "Exceeds"
.ListIndex = 0
End With
Set oCB = Nothing
End Sub

Private Sub FillCombo5()
Dim oCB As MSForms.ComboBox
Set oCB = ActiveDocument.ComboBox113
With oCB
.Clear
.AddItem " "
.AddItem "Does Not Meet"
.AddItem "Marginal"
.AddItem "Meets"
.AddItem "Exceeds"
.ListIndex = 0
End With
Set oCB = Nothing
End Sub

 

[Doug Robbins - Word MVP]

Where do you want to store the selected items?

--
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

 

[scrubgrub]

Where do you want to store the selected items?

--
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

With the file itself. So that whatever is selected is saved with the
file.
Or do I need to create a separate Excel file to store the data?
Sorry, really new to the whole thing.

 

[Doug Robbins - Word MVP]

One way

With ActiveDocument
.Variables("combobox1").Value = .ComboBox1.Value
etc.
End With

Better if you tell us exactly what you are trying to do (in terms of the
what you are starting with and the end result), rather than how you are
trying to do it.

--
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

 

[scrubgrub]

One way

With ActiveDocument
.Variables("combobox1").Value = .ComboBox1.Value
etc.
End With

Better if you tell us exactly what you are trying to do (in terms of the
what you are starting with and the end result), rather than how you are
trying to do it.

--
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







- Show quoted text -

The document is a form which will need to be saved after the user has
inputed all the information and made their selections in the combo
boxes. It's very simple, but I'm still very newbie It's a basic
form, but I don't want to use the Word form, as we have to have the
field boxes bright blue not grey. So I'm using the combobox on the
control toolbar.

So what I haven't been able to figure out is how to save the
selections that the user chooses in the combo box, it keeps reseting
when I reopen the document.

Where would I put your with statement? I'm getting an error how I have
it right now.

ie.
Private Sub FillCombo()
Dim oCB As MSForms.ComboBox
Set oCB = ActiveDocument.ComboBox1
With oCB
.Clear
.AddItem " "
.AddItem "Does Not Meet"
.AddItem "Marginal"
.AddItem "Meets"
.AddItem "Exceeds"
.ListIndex = 0
ActiveDocument.ComboBox1.Value = .ComboBox1.Value
End With
Set oCB = Nothing
End Sub

 

[Doug Robbins - Word MVP]

The code as I gave it to you, not your version of it from which you have
deleted a fundamental item, would need to be included in either the
combobox_Change event or the combobox_Exit event or on some other custom
event handler (a command button)

Your description of what this is supposed to do is still a bit lacking.

I would probably suggest that you should be using a userform

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.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

 

[Glenn]

This works in Word. I'm not sure about Excel. See your code below.

"object".EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection

will save the last selection in your combobox. When you reopen the file, it
will have the last picked item in it. At least this is how I did it in Word.

--
Thanks,
Glenn

I'm only mildly familiar with VBA so forgive me, I've scoured the
internet trying to figure this out and have figured out how to build
what I need, but I cannot figure out the code to save the information
selected . . . so store the selections. Can anyone help?

Here's my code as it stands now:

Sub AutoNew()
FillCombo
FillCombo2
FillCombo3
FillCombo4
FillCombo5
End Sub

Sub AutoOpen()
FillCombo
FillCombo2
FillCombo3
FillCombo4
FillCombo5
End Sub

Private Sub FillCombo()
Dim oCB As MSForms.ComboBox
Set oCB = ActiveDocument.ComboBox1
With oCB
.Clear
.AddItem " "
.AddItem "Does Not Meet"
.AddItem "Marginal"
.AddItem "Meets"
.AddItem "Exceeds"
.ListIndex = 0

oCB.EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection

End With
Set oCB = Nothing
End Sub

Private Sub FillCombo2()
Dim oCB As MSForms.ComboBox
Set oCB = ActiveDocument.ComboBox11
With oCB
.Clear
.AddItem " "
.AddItem "Does Not Meet"
.AddItem "Marginal"
.AddItem "Meets"
.AddItem "Exceeds"
.ListIndex = 0

oCB.EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection

End With
Set oCB = Nothing
End Sub

Private Sub FillCombo3()
Dim oCB As MSForms.ComboBox
Set oCB = ActiveDocument.ComboBox111
With oCB
.Clear
.AddItem " "
.AddItem "Does Not Meet"
.AddItem "Marginal"
.AddItem "Meets"
.AddItem "Exceeds"
.ListIndex = 0

oCB.EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection

End With
Set oCB = Nothing
End Sub

Private Sub FillCombo4()
Dim oCB As MSForms.ComboBox
Set oCB = ActiveDocument.ComboBox112
With oCB
.Clear
.AddItem " "
.AddItem "Does Not Meet"
.AddItem "Marginal"
.AddItem "Meets"
.AddItem "Exceeds"
.ListIndex = 0

oCB.EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection

End With
Set oCB = Nothing
End Sub

Private Sub FillCombo5()
Dim oCB As MSForms.ComboBox
Set oCB = ActiveDocument.ComboBox113
With oCB
.Clear
.AddItem " "
.AddItem "Does Not Meet"
.AddItem "Marginal"
.AddItem "Meets"
.AddItem "Exceeds"
.ListIndex = 0

oCB.EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection

 

[Jean-Guy Marcil]

Glenn was telling us:
Glenn nous racontait que :

This works in Word. I'm not sure about Excel. See your code below.

"object".EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection

will save the last selection in your combobox. When you reopen the
file, it will have the last picked item in it. At least this is how I
did it in Word.

I am not sure what you did exactly, but this is not quite right.
If you managed to make this work, you had some other code that did the job
of remembering the selected item.

fmEnterFieldBehaviorRecallSelection is used so that when the control
receives focus (by clicking or tabbing into it, not via .SetFocus), whatever
was last selected will still be selected.

For example, let's say a user selects the fourth item "Bananas" in a
combobox and decides to select (highlight) "Ban", he then moves on to
another control. Whenever this user tabs into the combobox (or clicks into
it) "Ban" will still be selected. If you do not use
"fmEnterFieldBehaviorRecallSelection", then "Bananas" would be selected the
second time around.
It has nothing to do with remembering whichever item was selected in the
combobox and being able to have it displayed automatically the next time the
userform is displayed.

Finally, the poster was writing about ActiveX controls (From the Controls
Toolbar). This behaviour, although available, does not work as advertised
with ActiveX controls. Whether you set the EnterFieldBehavior to
fmEnterFieldBehaviorRecallSelection or fmEnterFieldBehaviorSelecAll, it
always works as if fmEnterFieldBehaviorRecallSelection had been set,
whether you set it via the property dialog or via code. Furthermore, this
only has effect if the document is protected for forms because only tabbing
into the ActiveX control will trigger this behaviour, and tabbing into an
ActiveX control is only possible if the document is protected for forms.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Glenn]

I stand corrected, but then I'm an AutoLisp guy and don't do VBA much at all.
I am trying to learn, though and thanks Jean-Guy for pointing this out with
a very good explanation.

I edited several things out of my code after reading this post and found
that plugging through commands can lead to some unnecessary ones left in
code. My main teaching document is the VBA help in Word.

But if you need some AutoLisp code written for AutoCad, I'm the guy. I guess
I'm used to all those stupid parenthesis.

 

[Jean-Guy Marcil]

Glenn was telling us:
Glenn nous racontait que :

I stand corrected, but then I'm an AutoLisp guy and don't do VBA much
at all. I am trying to learn, though and thanks Jean-Guy for pointing
this out with a very good explanation.

I edited several things out of my code after reading this post and
found that plugging through commands can lead to some unnecessary
ones left in code. My main teaching document is the VBA help in Word.

Although useful, it is incomplete and even wrong at times...

For some more help, see:
http://word.mvps.org/faqs/MacrosVBA/index.htm

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org