Error Handleling

[SN]

Hi, I was wondering if someone could please assist. With the below listed
code, i am providing users with a list box to select 20 items from that will
fill the assosciated bookmarked fields in word. I do however only have 20
bookmarked text from fields within my word document

I did indicated to users that they should only select twenty however, the
possiblities of a user selecting more is unstopable.

As soon as they select Done on the userform. My fields will be populated,
but if selected more than 20 an get the following error.

runtime error 5941 " The requested member of the collection does not exist.

I was hopeing that if a user receives this error instead of receiving the
above mentioned, They can get an error say, Please only select 20 items.
they can click okay and it will take them back to reselecting their items.

No my knowledge is limited with VBA and read up quite abit on Error
handeling but no idea how to implent it to correspond to my error.

I was hoping if anyone can maybe point me in a direction or any assistance
would be appreciated.

Thanks in Advance
SN


Code


Option Explicit

Private Sub cmdCancel_Click()
Unload Me
End Sub


Private Sub cmdOK_Click()
Dim StrSkillList As String
Dim j As Long, k As Long
Dim var, var2, var3
Dim SkillsArray()
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

Private Sub lstSkills_Click()

End Sub

Private Sub UserForm_Initialize()
Dim var
Dim myArray()
myArray = Array("Accountant", "Accounts Assistant", "AGS ", "Battle Area
Clearance Supervisor", "CAD Operator", "Dog Handler", "Driver Car", _
"Driver Heavy Goods", "Driver Other", "Driver Tracked", "EOD Ammo Tech",
"EOD Assault Pioneer", "EOD Banksman", _
"EOD Demolitions Safety Officer", "EOD Diver Commercial",etc etc etc etc )

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

End Sub

 

[Doug Robbins - Word MVP]

Modify this section of your code as follows:

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
If j = 20 Then
var = lstSkills.ListCount - 1
End If
End If
Next

But it maybe better to have a routine that counted the number of selected
items and advised the user that it exceeded the allowable number

j = 0
For var = 1 to lstSkills.ListCount
If lstSkills.Selected(var - 1) = True Then
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 & skills."
End if

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

 

[SN]

Hi Doug
Thank you very much for you time and assistance.
I do however Have no idea if i am doing this correctly and if you the code
as listed below get an "compile error, Next without for."

Thanks again for your assistance

Private Sub cmdOK_Click()
Dim StrSkillList As String
Dim j As Long, k As Long
Dim var, var2, var3
Dim SkillsArray()
k = 1
j = 0
For var = 1 To lstSkills.ListCount
If lstSkills.Selected(var - 1) = True Then
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 & "skills"
End If

Next
For var2 = 0 To UBound(SkillsArray)
ActiveDocument.FormFields("Skill" & k).Result = _
SkillsArray(var2)
k = k + 1
Next
Unload Me

 

[Doug Robbins - Word MVP]

You would need:

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
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 & skills."
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


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

 

[SN]

Hi Dough Thanks again for all you help.

It is working absolutely fine. It does however not place the selected items
in the first Text Form field as previously.

If i do change the j=j+1 to j=j+0 it starts placing the select items again
at textform field 1 etc etc
It currently if i select two items will place it three texform field down
and if i select 3 items it will place it three texform fields down.

But if i do change it to j=j+0 my message box does not appear anymore after
selecting more than 20.

If the message box does appear and if i did select more than 20 and click
okay to go back to deselct some items it does not allow me deselct

Do yo maybe have any advise on changing the code so if i would like it to
reappear in the first back text form field and how i will allow the user to
be capable of deselecting the items after they have been notified by the
message.

I really do appreciate all you time you have spend on this code.
Thanks again
Kind regards
SN

 

[Doug Robbins - Word MVP]

After

MsgBox you have selected " & j - 20 & " more than the maximum number of
20 skills. Please unselect " & j - 20 & skills."

insert

Exit Sub

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

 

[SN]

Hi Dough
Thank thank very much, You have been of extreme help. I do have one more
question if you possibly have time assist then it will be working absolutely
perfect.

I now have the option to select from the list box, my messagebox will
appear if i select more than 20, I can go back and unselect if to many has
been selected. . The only thing now if i do then select my Cmdok button
after deselecting it gives me and 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)

Do I need to indicate again where the selected fields need to be placed.

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

 

[Doug Robbins - Word MVP]

Are you sure that the formfield "skill" & k does exist in your document?



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

 

[SN]

Hi, Yes it does. if i select less than twenty it will update all the skill
fields in the doc without any errors. , it is just after i received an
message indicating i need to deselct . If i deselect and then click ok i get
the error.

 

[Doug Robbins - Word MVP]

OK, I think what is happening is that the second time, the array is already
populated and you are now adding additional items to it

Using the following should get around that:

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

That will then throw up the message and exit from the sub before the array
has been populated.
--
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

 

[SN]

Hi Doug. Nope now i am allowed to select, unselect but it does not populate
any fields. If i select 3 skills it will not update the relevant text form
fields in word. If I select more than 20 the message will appear, Unselect
the items but again on select Ok it will not update any text form fields.
Before the amendemend it updated the fields if we selected less than 20, But
gave an error if we selected more than 20
Now it does not update less than 20 or more than 20

I am so sorry for all the question relating to this.

.. I do have 20 text form fields in word, i bookmarked each with name
starting Skill1 thew to Skill20, upon clicking on text form field 1 a macro
calles Show Skills Form runs which brings up my skills list form to select
the skills from. Which then after selecting will populate the 20 text form
fields in word. .

Currenly i am using the amended code as listed below.



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
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
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
For var2 = 0 To UBound(SkillsArray)
ActiveDocument.FormFields("Skill" & k).Result = _
SkillsArray(var2)
k = k + 1
Next
Unload Me
End Sub

 

[Doug Robbins - Word MVP]

If you comment out the following lines of code does it then work if you
select less than 20 items

' j = 0
' For var = 1 To lstSkills.ListCount
' If lstSkills.Selected(var - 1) = True Then
' 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

I have not compared the balance of your code with that with which you
started, but if the above does not work, then there must be some difference
between the balance of your present code and that which used to work.

One thought however, you may want to put a var = 0 after the above.


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