Prev: re label
Next: Sorting UDTs
From: johnnykunst on 28 Jun 2010 06:34
I am using the VBA to populate a drop down list ("Parklist") based on what a user selects from a first drop down list (AtoZ)- code is set to run on exit from ("Parklist"). However, if the user makes a selection in ("A-Z") and then opens the drop down list ("Parklist") but doesn't make a selection & clicks elsewhere in the document, the following error message pops up "Runtime error 5825 Object has been deleted".

Sub Fillparklist()
Dim myArray1() As String
Dim myArray2() As String
Dim myArray3() As String
Dim myArray4() As String
Dim myArray5() As String
Dim myArray6() As String
Dim myArray7() As String
Dim myArray8() As String
Dim i As Long
myArray1 = Split("ADDINGTON SQ OPEN SPACE,ALBION CHANNEL,ALEXIS ST OPEN SPACE,ALL HALLOWS' CHURCHYARD,ANGEL PUBLIC HSE OPEN SPACE,BARNARDS WHARF OPEN SPACE,BELAIR PK,BERMONDSEY SPA GDNS,BERMONDSEY SQ OPEN SPACE,BIRD IN BUSH PK,BOG GDNS,BRENCHLEY GDNS,BRIMMINGTON PK,BRUNSWICK PK,BURGESS PK", ",")
myArray2 = Split("CAMBERWELL GREEN,CAMBERWELL NEW CEMETERY,CAMBERWELL OLD CEMETERY,CANADA WATER,CATESBY + MASON ST OPEN SPACE,CHERRY GDNS,CHRISTCHURCH GDNS,CONSORT PK,COSSALL PK,CUMBERLAND WHARF,COX 'S WALK OPEN SPACE,DAVID COPPERFIELD GDNS,DAWSON 'S HILL + DAWSON HEIGHTS,DEAL PORTERS WALK,DICKENS SQ OPEN SPACE,DOG KENNEL HILL OPEN SPACE,DR HAROLD MOODY PK,DULWICH LIBRARY GARDEN,DULWICH PK,DULWICH UPPER WOOD,DURAND 'S WHARF PK,ELEPHANT RD PLAYGROUND,FALMOUTH RD PK,FARADAY GDNS,FLAXYARD OPEN SPACE", ",")
myArray3 = Split("GERALDINE MARY HARMSWORTH PK,GOOSE GREEN,GOOSE GREEN PLAYGROUND,GREENDALE PLAYING FIELD,GREENLAND DOCK,GUY ST PK,HANKEY PLACE GDNS,HIGHSHORE RD OPEN SPACE,HOLLY GROVE SHRUBBERY,HOLY TRINITY CHURCHYARD,HOMESTALL RD PLAYING FIELD,HONOR OAK CREMATORIUM,HONOR OAK SPORTS GROUND,HOPE SUFFERENCE WHARF,KENNINGTON OPEN SPACE,KING EDWARD III CATHAY ST,KING GEORGE 'S FIELD,KING 'S STAIRS GDNS,KIRKWOOD RD NATURE GARDEN", ",")
myArray4 = Split("LAVENDER POND,LEATHERMARKET GDNS,LEYTON SQ OPEN SPACE,LITTLE DORRIT PK,LONG LANE PK,LONG MEADOW,LUCAS GDNS,LYNDHURST SQ OPEN SPACE,MARLBOROUGH PLAYGROUND,MARY DATCHELOR PLAYING FIELD,MINT ST PK,MONTAGUE CLOSE OPEN SPACE,NELSON SQ GDNS,NEPTUNE ST PK,NEWINGTON GDNS,NILE TERRACE,NUNHEAD CEMETERY,NUNHEAD GREEN,NURSERY ROW PK,ONE TREE HILL OPEN SPACE", ",")
myArray5 = Split("PARAGON GDNS,PASLEY PK,PATERSON PK,PEARSON 'S PK,PECKHAM RYE PK + COMMON,PECKHAM SQ,PELIER PK,PIERMONT GREEN,POTTER 'S FIELD PK,PULLENS GDNS,PYNNERS CLOSE PLAYING FIELD,REDCROSS GDNS,ROCKINGHAM OPEN SPACE,RUSSIA DOCK WOODLAND", ",")
myArray6 = Split("SALISBURY ROW EXT,SALISBURY ROW PK,SHUTTLEWORTH PK,SOUTHWARK PK,SOUTHWARK SPORTS GROUND,ST GEORGE 'S CHURCHYARD + GDNS,ST GILES ' CHURCHYARD,ST JAMES 'S CHURCHYARD,ST JOHN 'S CHURCHYARD,ST MARY MAGDALEN CHURCHYARD,ST MARY 'S CHURCHYARD NEWINGTON,ST MARY 'S CHURCHYARD ROTHERHITHE,ST MARY 'S FROBISHER PK,ST MARY 'S GDNS ROTHERHITHE,ST PAULS SPORTS GROUND,ST PETER 'S CHURCHYARD,STAVE HILL ECOLOGICAL PK,STAVE HILL OPEN SPACE", ",")
myArray7 = Split("SUMNER PK,SUNRAY GDNS,SURREY CANAL WALK,SURREY CANAL WALK JOWETT ST,SURREY DOCKS SPORTS GROUNDS,SURREY SQ OPEN SPACE,SURREY WATER,SUTHERLAND SQ OPEN SPACE,SYDENHAM HILL WOOD,TABARD GDNS,TANNER ST PK,VICTORY COMMUNITY PK,WARWICK GDNS,WEST SQ GARDEN", ",")
myArray8 = Split("THEN SELECT PARK NAME HERE,THEN SELECT PARK NAME HERE", ",") _


ActiveDocument.FormFields("Parklist").DropDown.ListEntries.Clear
Select Case ActiveDocument.FormFields("AtoZ").Result

Case "Parks A-B"
For i = 0 To UBound(myArray1)
ActiveDocument.FormFields("Parklist").DropDown.ListEntries.Add myArray1(i)
Next i

Case "Parks C-F"
For i = 0 To UBound(myArray2)
ActiveDocument.FormFields("Parklist").DropDown.ListEntries.Add myArray2(i)
Next i

Case "Parks G-K"
For i = 0 To UBound(myArray3)
ActiveDocument.FormFields("Parklist").DropDown.ListEntries.Add myArray3(i)
Next i

Case "Parks L-O"
For i = 0 To UBound(myArray4)
ActiveDocument.FormFields("Parklist").DropDown.ListEntries.Add myArray4(i)
Next i

Case "Parks P-R"
For i = 0 To UBound(myArray5)
ActiveDocument.FormFields("Parklist").DropDown.ListEntries.Add myArray5(i)
Next i

Case "Parks P-R"
For i = 0 To UBound(myArray5)
ActiveDocument.FormFields("Parklist").DropDown.ListEntries.Add myArray5(i)
Next i

Case "Parks Sa-St"
For i = 0 To UBound(myArray6)
ActiveDocument.FormFields("Parklist").DropDown.ListEntries.Add myArray6(i)
Next i

Case "Parks Su-W"
For i = 0 To UBound(myArray7)
ActiveDocument.FormFields("Parklist").DropDown.ListEntries.Add myArray7(i)
Next i

Case "FIRST SELECT PARKS A-Z LIST HERE"
For i = 0 To UBound(myArray8)
ActiveDocument.FormFields("Parklist").DropDown.ListEntries.Add myArray8(i)
Next i

End Select
End Sub




---
frmsrcurl: http://msgroups.net/microsoft.public.vb.general.discussion
From: Phill W. on 28 Jun 2010 07:08
On 28/06/2010 11:34, johnnykunst wrote:

> I am using the VBA to populate a drop down list ("Parklist") based on what a user selects from a first drop down list (AtoZ)- code is set to run on exit from ("Parklist"). However, if the user makes a selection in ("A-Z") and then opens the drop down list ("Parklist") but doesn't make a selection& clicks elsewhere in the document, the following error message pops up "Runtime error 5825 Object has been deleted".

<snip>

> ActiveDocument.FormFields("Parklist").DropDown.ListEntries.Clear
> Select Case ActiveDocument.FormFields("AtoZ").Result
>
> Case "Parks A-B"
> For i = 0 To UBound(myArray1)
> ActiveDocument.FormFields("Parklist").DropDown.ListEntries.Add myArray1(i)
> Next i
>
> Case "Parks C-F"
> For i = 0 To UBound(myArray2)
> ActiveDocument.FormFields("Parklist").DropDown.ListEntries.Add myArray2(i)
> Next i

It might be a bit painful (for the user) but you /could/ add a "Case
Else" and load /every/ list into the second drop-down; it would be
horrible (for them) to work with but then it serves them right for not
using the first list. ;-)

Alternatively, you might disable the second list until a value is
selected in the first (but that might confuse the poor User even more
when they can't go straight to the second list).
Hey ho; you can only do /so/ much to help the Poor Souls.

HTH,
Phill W.
From: johnnykunst on 28 Jun 2010 17:34
I managed to sort it out by adding to the macro for on exit from 'AtoZ' a go to Parklist.
Thanks for the advice though.

---
frmsrcurl: http://msgroups.net/microsoft.public.vb.general.discussion/Dependent-drop-down-lists-runtime-error-5825
 | 
Pages: 1
Prev: re label
Next: Sorting UDTs