Re: Error Handleling

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <SN@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:64189DF4-E25C-424A-BE52-1EAD760C3527@xxxxxxxxxxxxxxxx
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" wrote:

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" <SN@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EC18310A-872C-425E-BC8E-4B4A1AFFB202@xxxxxxxxxxxxxxxx
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" wrote:

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

"Doug Robbins - Word MVP" <dkr@xxxxxxxxxxxxxxxxxx> wrote in message
news:u4iXBOajHHA.4624@xxxxxxxxxxxxxxxxxxxxxxx
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" <SN@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:184F719D-8731-408F-BFDF-114E7B0543D2@xxxxxxxxxxxxxxxx
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" wrote:

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" <SN@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8B39C471-CF40-4302-9E51-334881FD0B81@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Error Handleling
    ... Private Sub cmdOK_Click ... Dim StrSkillList As String ... Dim var, var2, var3 ... For var2 = 0 To UBound ...
    (microsoft.public.word.vba.general)
  • Re: Error Handleling
    ... Private Sub cmdOK_Click ... Dim StrSkillList As String ... Dim var, var2, var3 ... For var2 = 0 To UBound ...
    (microsoft.public.word.vba.general)
  • Re: Error Handleling
    ... Dim StrSkillList As String ... Dim var, var2, var3 ... For var2 = 0 To UBound ... Private Sub cmdCancel_Click ...
    (microsoft.public.word.vba.general)
  • Re: Error Handleling
    ... Private Sub cmdOK_Click ... Dim StrSkillList As String ... Dim var, var2, var3 ... For var2 = 0 To UBound ...
    (microsoft.public.word.vba.general)
  • Re: Error Handleling
    ... Dim StrSkillList As String ... Dim var, var2, var3 ... For var2 = 0 To UBound ... Private Sub cmdCancel_Click ...
    (microsoft.public.word.vba.general)