Userform Word Combobox

[naturalsn]

Good Morning

I was hoping someone could perhaps assist I am really overlooking something

I am currently creating a second userform (combobox) in my word document.
(Reason i am using the user form is due to the fact that I have more than 25 items listed in my combo box.)

I have A skills Selection part in my word doc
This Selction consist of a variety of 20 different fields to choose from
The bookmark name Start Skill 1 Thru to 20
I then created a userfrom wich will have 20 comboboxes on it, To fill bookmarks 1 -20

I have used the following code to upon clicking on bookmark one open my userform
and then select from the comoboxes but i just dont get any information in my dropdown at all

A module to open the form
**********************************
Sub gocombobox()
SkillForm.Show
End Sub
***************************************
Code on Combobox 1 (Skill1)
********************************************
Private Sub CmdSkills1_Change()

ActiveDocument.FormFields("Skill1").Result = CmdSkills1.Value

End Sub
Private Sub SkillForm_Initialize()

CmdSkills1.ColumnCount = 1

CmdSkills1.List() = Array("Eod", "Eod2", "etc", "etc1")
End Sub


Private Sub CmdCloseSF_Click()

Unload Me

End Sub
************************************
This code is exactly the same (except for names) as my first user form to open another individual combobox, and it works perfectly fine. Is there maybe something i am not putting in due to the fact that i am maybe repeating something,
I would really appreciate any assitance or advise

Kind regards
Natural

(I am using Word 2003)

 

[fumei]

Code on Combobox 1 (Skill1)
********************************************
Private Sub CmdSkills1_Change()

Why are you using "Cmd"? This is normally a commandbutton.

And you in fact use: Sub CmdCloseSF_Click(), which seems to be a commandbutton. Try to use consistent names.

Plus: "open another individual combobox,". OPEN a combobox??? You do not "open" comboboxes.

Plus: "creating a second userform (combobox) ". A userform is not a combobox, nor is a combobox a userform.

I have A skills Selection part in my word doc
This Selction consist of a variety of 20 different fields to choose from
The bookmark name Start Skill 1 Thru to 20
I then created a userfrom wich will have 20 comboboxes on it, To fill bookmarks 1 -20

Bookmarks.

Yet your code has:

ActiveDocument.FormFields("Skill1").Result = CmdSkills1.Value

which is for a formfield.

Please be clear.

So let me see if I have this correctly.

1. You have 20 formfields in the document.
2. open the userform. I not not clear how you are doing this.

I have used the following code to upon clicking on bookmark one open my userform

is not clear. You are clicking a "bookmark" to somehow open the userform???

3. I am lost at this point. " select from the comoboxes" does not make sense to me.

A combobox on a userform is indeed populated (usually) with UserForm_Initialize.

ComboBox1.AddItem [i]item[/i]

You add items to the combobox. Yes, you can certainly use an array to populate it.

Sorry, but : "created a userfrom wich will have 20 comboboxes on it, To fill bookmarks 1 -20 " does not make sense either. Comboboxes have multiple items. This sounds like you are using one combobox for one "bookmark".

I will change the nomenclature a little, but:

Private Sub SkillForm_Initialize()
Dim var
Dim myArray()
myArray = Array("Eod", "Eod2", "etc", "etc1")

' cbo is standard prefix for comboboxes
For var = 0 to ubound(myArray)
   cboSkills1.AddItem myArray(var)
Next
' have first item displayed
cboSkills1.Listindex = 0
End Sub

I do not get the 20 comboboxes though. WHY??

Gerry
My paintings and sculpture

 

[naturalsn]

thanks for any assistance so far

Sorry for the vagueness, I have been stuck with this for a while.
A brief Description.
The document is an appliction form

On which candidates will fill in the relevant fields. (I created a table within word allowing users to tab threw the necessary fields)

I would like the users to select from a dropdown their nationality, but can't use the normal drop down field due to the 25 limit.

What i did was as a candidate tabs/click on my bookmark nationality,
the following module runs
*********************
Sub gocombobox()

UserForm.Show

End Sub

************************

This then opens my user from called nationality in which there is one combobox with the following code in it.
***************

Private Sub CmbNationality_Change()

ActiveDocument.FormFields("PresentNationality").Result = CboNationality.Value

End Sub
____________________________
Private Sub UserForm_Initialize()

CboNationality.ColumnCount = 1

CboNationality.List() = Array("Afghan", "Ukrainian")

End Sub
____________________________

Private Sub Cmdclose_Click()
Unload Me
End Sub

And the above listed cmd to close my userform.
_________________________
This word perfectly no problems

But then

I then need the candidates to select from a list of 20 dropdown fields (all of these dropdown fields consist of the exact same data.)their skills. (due to the nature of the applicants we work with it is all just short Abriviations and Necessary) due to the fact that there are more than 25 to chose from had to create another userform, Which again upon tabbing to the first fields of my skill area that the userform SkillsForm will open via
the following module
**************
Option Explicit

Sub gocombobox()

SkillsForm.Show

End Sub
*******************
I then on the userform created 20 Comboboxes. Which in return should update the relevant fields on my word document. Again just selecting data to update the Bookmark Called Skill1 to 20 (If they have that many)
And this is where i am stuck and getting no data at all in the comboboxes.

Yes I am using one combobox for one "bookmark". 1 - 20. (Due to a candidate maybe having 15 different skills listed in my dropdown)

My problem . I am getting no data within my comboboxes on my second user form at all. (Skills)

Is there something that i maybe listed in my first userform that allows that to be the only one working, or am i approaching the way i have written the code far, completely wrong.


thanks in advance
sn

 

[naturalsn]

O.. The reason As you stated

Bookmarks.
Yet your code has:
CODE
ActiveDocument.FormFields("Skill1").Result = CmdSkills1.Value
which is for a formfield.

This is due to the fact that i created Text from field and identified the bookmark within, The reason for this is due this document being used to extract data into a Database.

Hope that explains why

 

[fumei]

This is due to the fact that i created Text from field and identified the bookmark within, The reason for this is due this document being used to extract data into a Database.

Hope that explains why

No...it does not. That does not make sense. Data is data. Text is text. It makes zero difference if that data/text comes from a bookmark Range, or a formfield Result. The point being is that when asking for assistance you need to use proper terms. You can identify it as a formfield...which is what it IS.

What i did was as a candidate tabs/click on my bookmark nationality,

It is NOT - as far as I can see - a bookmark. They seem to be formfields. You do not tab between bookmarks, you CAN'T tab between bookmarks. You tab between formfields.

If this is correct, then what you are have (apparently) is an OnEntry macro for the formfield which opens the userform. The user tabs into the formfield and the OnEntry macro executes, opening the userform

However, you do not describe it that way, so I may be wrong - although again...you do not tab into bookmarks, so I can not see how you are making this work.

Next.

And this is where i am stuck and getting no data at all in the comboboxes.

The code:

CboNationality.ColumnCount = 1
CboNationality.List() = Array("Afghan", "Ukrainian")

does populate a combobox with "Afghan" and "Ukrainian". I don't know what you problem is. True, it does not show any default visible item. I showed you how to do that, so I presume you do not want any item shown.

As for the other comboboxes....I have read your post five times. I am both confused and dismayed. It seems poor design. I base that statement on this:

I then need the candidates to select from a list of 20 dropdown fields (all of these dropdown fields consist of the exact same data.)their skills

Sorry, but if you are using the same data in the dropdown(s)...then you the same dropdown. 20 different comboboxes with the SAME data is simply poor design.

Finally, you still have not mentioned how you are trying to populate these comboboxes. You state you are not getting any sat in the,. OK...then state how you are trying.

Gerry
My paintings and sculpture

 

[naturalsn]

Good Morning
Thanks for the response,
Yes you are correct, I do use an OnEntry macro for the formfield which opens the userform. The user tabs into the formfield and the OnEntry macro executes, opening the userform.

Now the code listed above

CboNationality.ColumnCount = 1
CboNationality.List() = Array("Afghan", "Ukrainian")
does populate my combo box perfectly and No problems at all with this combobox and Userform , (This is Userform No 1 Nationality)

I have used this same process when i was testing just one combobox on my second userform (Skills). And still no data would appear within my Skills1 combobox

(If I However delete the Nationality Userform. My second Form does have data in the combobox if I use the exact same code as above the above stated Nationality Code. (changed combobox names to cboskills1) I am wondering if something i did in the first Userform, is affecting the comboboxes in my second Userform. )

I do however need the 2 userforms (Nationality / Skills)

In my second userform where i would like the candiates to select from the skills comboboxes.

I am indeed using 20 different comboboxes with the same data in on my Skills User form.
I am unfortunatly am to not to experienced in VBA to know how to make the same data appear in 20 different comboboxes.
I have taken the expample you have provided me with and tried the following (This is just to get data in my combobox Skills1 (testing))

*************************************************
Private Sub SkillsForm_Initialize()
Dim var
Dim myArray()
myArray.List() = Array("Eod", "Eod2", "etc", "etc1")

For var = 0 To UBound(myArray)
CboSkills1.AddItem myArray(var)
Next
CboSkills1.ListIndex = 0

End Sub
***********************************************************

Private Sub CboSkills1_Change()
ActiveDocument.FormFields("skill1").Result = CboSkills1.Value

End Sub
**************************************************
Private Sub cmdCloseSF_Click()

Unload Me

End Sub
*********************************************************
I would really appreciate your advise on how to populate my 20 comboboxes with the same data, to update my Text Form Fields in Word.

Thanks in advance
SN

 

[fumei]

Yes you are correct, I do use an OnEntry macro for the formfield which opens the userform. The user tabs into the formfield and the OnEntry macro executes, opening the userform.

Thank you. May I suggest you look at the FAQ section, and in particular the one on how to get the best answers. One of the things is to write good, clear, and ACCURATE posts.

I fail to see why you need 20 comboboxes. Why? Why? WHY? Why not use ONE combobox for it, since these 20 comboboxes use the same data?

I also fail to see why your are not getting items in your comboboxes...but then I suspect you are not exactly posting full and accurate code.

I will make a final try to help. If you can, strip off anything that you don't want anyone to see (if that is applicable) and send me the document. I will take a look and see what you are actually doing. You are not being very good at doing that in your postings. Frankly, I am finding them a bit frustrating.

Send it to me, or not. Up to you. If you want to, send it to: myhandle at telus dot net

Please zip the file.



Gerry
My paintings and sculpture

 

[naturalsn]

Good Monring Gerry

I was wondering if you could perhaps assist again. .
I am currenly working with the code, you help me with so incredibly and wondered if you could perhaps advise.

I recently amended the code hoping to insert a Message for when users select more than twenty skills from the list box, they will get and error stating "Please unselect "the" amount of skills.

By amending the code, I seem to do something that duplicates the entry if a user selects the skills and click okay, and if the user has selected more than twenty he will deselect and upon clicking ok I get the following error

Runtime Error 5941
The Requested member of the collection does not exist.
and if I select Debug it takes me to the following string

ActiveDocument.FormFields("Skill" & k).Result = _
SkillsArray(var2)

I would really appreciate any assistance if possible.
Thanks in Advacn
Kind Regards
SN

The code looks as follow

Private Sub cmdOK_Click()
Dim StrSkillList As String
Dim j As Long, k As Long
Dim var, var2, var3
Dim SkillsArray()
j = 0
For var = 1 To lstSkills.ListCount
If lstSkills.Selected(var - 1) = True Then
ReDim Preserve SkillsArray(j)
SkillsArray(j) = lstSkills.List(var - 1)
j = j + 1
End If
Next var
If j > 20 Then
MsgBox "You have selected " & j - 20 & " more than the maximum number of 20 skills. Please unselect " & j - 20 & ""
Exit Sub

End If
k = 1
For var = 1 To lstSkills.ListCount
If lstSkills.Selected(var - 1) = True Then
ReDim Preserve SkillsArray(j)
SkillsArray(j) = lstSkills.List(var - 1)
j = j + 1
End If
Next
For var2 = 0 To UBound(SkillsArray)
ActiveDocument.FormFields("Skill" & k).Result = _
SkillsArray(var2)
k = k + 1
Next
Unload Me
End Sub

 

[fumei]

I am busy right now, but I will look at this.

BTW: please start using the code window when you post code. To see how, click the Process TGML link below where you type in your post.

First glance though:

For var = 1 To lstSkills.ListCount
  If lstSkills.Selected(var - 1) = True Then
    ReDim Preserve SkillsArray(j)
    SkillsArray(j) = lstSkills.List(var - 1)
    j = j + 1
  End If
Next var
If j > 20 Then
  MsgBox "You have selected " & j - 20 & " more than the " _
       & "maximum number of 20 skills. Please unselect " _
       & j - 20 & ""
  Exit Sub
End If

Do you notice that j always has 1 added to it? Therefore if the user selects 20, then j = 21. So at the very least your checking should be:

If j > 21 Then

Gerry
My paintings and sculpture

 

[naturalsn]

Hi Gerry

Thank you so much in advance
Kind Regards
Susan

 

[fumei]

OK. Just to bring others up to date on this one.

Susan sent her file to me and it floored me. It has LOTS and LOTS of formfields.

I showed her how to use one combobox - rather than the 20 separate one used before.

There are a maximum of 20 skills (each being put into a formfield). The list of available skills to choose is greater than 20.

What to do when the user selects more than 20?

Susan, your error message tells the user to deselect the 21st. I do not think this is correct. You have no way of knowing which ones the user thinks is more important to keep. The choice should be up to them. It is better to state that there is a max of 20, they have selected more than 20. Do you wish to keep the first 20 selected, or reselect? Something like the following. Feel free to amend the text of the message.

Private Sub cmdOK_Click()
Dim StrSkillList As String
Dim j As Long, k As Long
Dim var, var2, var3
Dim SkillsArray()
Dim response
Dim msg As String
msg = "You may include a maximum of 20 skills." & _
      "You have now selected 21.  Do you wish to use " & _
      "the first 20 selected? The last one will be " & _
      "ignored." & vbCrLf & "Click Yes to accept the " & _
      "first 20 choices." & vbCrLf & "Click No to reselect."
j = 0
  For var = 1 To lstSkills.ListCount
    If j = 21 Then
      response = Msgbox(msg, "Limit exceeded")
      If response = vbNo Then
          Exit Sub
      Else
          Exit For
      end If
    End If
    If lstSkills.Selected(var - 1) = True Then
      ReDim Preserve SkillsArray(j)
      SkillsArray(j) = lstSkills.List(var - 1)
      j = j + 1
    End If
  Next var
k = 1
  For var2 = 0 To UBound(SkillsArray)
    ActiveDocument.FormFields("Skill" & k).Result = _
       SkillsArray(var2)
    k = k + 1
  Next
Unload Me
End Sub

Gerry
My paintings and sculpture

 

[naturalsn]

Hi Gerry
Thanks so much again for all your help.

I do however upon selecting okay if i selected more than 20 Skills get an error that reads.

Runtime Error 13
Type Mismatch

If i Select Debug it takes me to the following section.

 response = Msgbox(msg, "Limit exceeded")

Do you possilby have any idea what might be causing the above mentioned.


Thanks again
Susan

 

[PHV]

response = Msgbox(msg, [!]vbYesNo, [/!]"Limit exceeded")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

Doh! PH is of course correct. I forgot to put the button type in. It should work now.

Gerry
My paintings and sculpture

 

[naturalsn]

Good Monring

Thank so much for all the assistance, and PHV for the articles on posting, all good help, Thank you.

The amended is indeed working thank you. I do however seem to not having to much luck.

If I do select more than 20 skills from the listbox.
And the message box appears and I select "Yes" I will be accepting the first 20 I have selected.

I will get
Runtime Error 5941
The Requested member of the collection does not exist
and upon selecting debug. I takes me to

    ActiveDocument.FormFields("Skill" & k).Result = _
       SkillsArray(var2)

If I do select NO, it allows me to Deselect and populate the neccesary text form fields perfectly without any errors.

Isn't it possible that because the Array is maybe already populated and I am now trying removing data from it that it is giving me the error.

Thank you again
SN