+ Reply to Thread
Results 1 to 11 of 11

comboboxes

  1. #1
    lgbjr
    Guest

    comboboxes

    Hi All,

    I have a workbook with 2 sheets. On sheet 1 I have two lists:

    List1:
    Column A Column B
    ProjectID ProjectName

    List2:
    Column D Column E Column F
    ProjectID SubProjectID SubProjectName

    On sheet 2, I have 2 combo boxes. what I want to do is fill the first
    combobox with the contents of list 1. the second combobox will be filled
    based on the selection in the first combobox (ProjectID).

    Can someone provide me with a few hints or code snippets? I think I'm
    looking for code to go into the combobox1 change event.

    TIA
    Lee





  2. #2
    Bob Phillips
    Guest

    Re: comboboxes

    Take a look at http://www.xldynamic.com/source/xld.Dropdowns.html

    --
    HTH

    Bob Phillips

    "lgbjr" <[email protected]> wrote in message
    news:[email protected]...
    > Hi All,
    >
    > I have a workbook with 2 sheets. On sheet 1 I have two lists:
    >
    > List1:
    > Column A Column B
    > ProjectID ProjectName
    >
    > List2:
    > Column D Column E Column F
    > ProjectID SubProjectID SubProjectName
    >
    > On sheet 2, I have 2 combo boxes. what I want to do is fill the first
    > combobox with the contents of list 1. the second combobox will be filled
    > based on the selection in the first combobox (ProjectID).
    >
    > Can someone provide me with a few hints or code snippets? I think I'm
    > looking for code to go into the combobox1 change event.
    >
    > TIA
    > Lee
    >
    >
    >
    >




  3. #3
    lgbjr
    Guest

    Re: comboboxes

    Hi Bob,

    Thanks for the link. And I'm sure the site probably has exactly what I need.
    Unfortunately, I'm cyrrently living in China, and apparently the DNS server
    that China Telecom uses can not find the site. I've had this issue before
    with sites that I thought would be in all DNS servers. I also can't get to
    Geocities for the UDQServices download.

    Anyway, if you know of some other helpful links, please let me know.

    regards,
    Lee


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > Take a look at http://www.xldynamic.com/source/xld.Dropdowns.html
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > "lgbjr" <[email protected]> wrote in message
    > news:[email protected]...
    >> Hi All,
    >>
    >> I have a workbook with 2 sheets. On sheet 1 I have two lists:
    >>
    >> List1:
    >> Column A Column B
    >> ProjectID ProjectName
    >>
    >> List2:
    >> Column D Column E Column F
    >> ProjectID SubProjectID SubProjectName
    >>
    >> On sheet 2, I have 2 combo boxes. what I want to do is fill the first
    >> combobox with the contents of list 1. the second combobox will be filled
    >> based on the selection in the first combobox (ProjectID).
    >>
    >> Can someone provide me with a few hints or code snippets? I think I'm
    >> looking for code to go into the combobox1 change event.
    >>
    >> TIA
    >> Lee
    >>
    >>
    >>
    >>

    >
    >




  4. #4
    Bob Phillips
    Guest

    Re: comboboxes

    I can send you the page and a download if you give me your email addy

    --
    HTH

    Bob Phillips

    "lgbjr" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Bob,
    >
    > Thanks for the link. And I'm sure the site probably has exactly what I

    need.
    > Unfortunately, I'm cyrrently living in China, and apparently the DNS

    server
    > that China Telecom uses can not find the site. I've had this issue before
    > with sites that I thought would be in all DNS servers. I also can't get to
    > Geocities for the UDQServices download.
    >
    > Anyway, if you know of some other helpful links, please let me know.
    >
    > regards,
    > Lee
    >
    >
    > "Bob Phillips" <[email protected]> wrote in message
    > news:%[email protected]...
    > > Take a look at http://www.xldynamic.com/source/xld.Dropdowns.html
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > "lgbjr" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> Hi All,
    > >>
    > >> I have a workbook with 2 sheets. On sheet 1 I have two lists:
    > >>
    > >> List1:
    > >> Column A Column B
    > >> ProjectID ProjectName
    > >>
    > >> List2:
    > >> Column D Column E Column F
    > >> ProjectID SubProjectID SubProjectName
    > >>
    > >> On sheet 2, I have 2 combo boxes. what I want to do is fill the first
    > >> combobox with the contents of list 1. the second combobox will be

    filled
    > >> based on the selection in the first combobox (ProjectID).
    > >>
    > >> Can someone provide me with a few hints or code snippets? I think I'm
    > >> looking for code to go into the combobox1 change event.
    > >>
    > >> TIA
    > >> Lee
    > >>
    > >>
    > >>
    > >>

    > >
    > >

    >
    >




  5. #5
    lgbjr
    Guest

    Re: comboboxes

    Thanks Bob,

    Please use [email protected]

    Regards,
    Lee

    "Bob Phillips" <[email protected]> wrote in message
    news:[email protected]...
    >I can send you the page and a download if you give me your email addy
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > "lgbjr" <[email protected]> wrote in message
    > news:[email protected]...
    >> Hi Bob,
    >>
    >> Thanks for the link. And I'm sure the site probably has exactly what I

    > need.
    >> Unfortunately, I'm cyrrently living in China, and apparently the DNS

    > server
    >> that China Telecom uses can not find the site. I've had this issue before
    >> with sites that I thought would be in all DNS servers. I also can't get
    >> to
    >> Geocities for the UDQServices download.
    >>
    >> Anyway, if you know of some other helpful links, please let me know.
    >>
    >> regards,
    >> Lee
    >>
    >>
    >> "Bob Phillips" <[email protected]> wrote in message
    >> news:%[email protected]...
    >> > Take a look at http://www.xldynamic.com/source/xld.Dropdowns.html
    >> >
    >> > --
    >> > HTH
    >> >
    >> > Bob Phillips
    >> >
    >> > "lgbjr" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> >> Hi All,
    >> >>
    >> >> I have a workbook with 2 sheets. On sheet 1 I have two lists:
    >> >>
    >> >> List1:
    >> >> Column A Column B
    >> >> ProjectID ProjectName
    >> >>
    >> >> List2:
    >> >> Column D Column E Column F
    >> >> ProjectID SubProjectID SubProjectName
    >> >>
    >> >> On sheet 2, I have 2 combo boxes. what I want to do is fill the first
    >> >> combobox with the contents of list 1. the second combobox will be

    > filled
    >> >> based on the selection in the first combobox (ProjectID).
    >> >>
    >> >> Can someone provide me with a few hints or code snippets? I think I'm
    >> >> looking for code to go into the combobox1 change event.
    >> >>
    >> >> TIA
    >> >> Lee
    >> >>
    >> >>
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >




  6. #6
    Yan-Hong Huang[MSFT]
    Guest

    Re: comboboxes

    Hi Lee,

    I was reviewing the issue thread. How is everything going now? If there is
    any question, please feel free to post here.

    Thanks very much for participating the community.

    Best regards,
    Yanhong Huang
    Microsoft Community Support

    Get Secure! C www.microsoft.com/security
    Register to Access MSDN Managed Newsgroups!
    -http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.as
    p&SD=msdn

    This posting is provided "AS IS" with no warranties, and confers no rights.


  7. #7
    lgbjr
    Guest

    Re: comboboxes

    ,

    Well, unfortunately I am still unable to gain access to the xldynamic site,
    which I understand has excellent content, and specifically content related
    to my original post.

    Not related to the original post, I have sent email to the xldynamic
    webmaster (no response yet), and have tried various techniques to gain
    access to the site (such as changing DNS servrs, adding a (free) proxy
    server, using the IP address, rather than the name in IE, etc.). I've also
    asked several colleagues in different cities and on different networks in
    china to try to access the site. They all have the same result. no luck. I
    don't understand why a site like xldynamic would be blocked in China.

    Back to the original post, I really don't have an answer yet.

    Best Regards,
    Lee ()

    "Yan-Hong Huang[MSFT]" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Lee,
    >
    > I was reviewing the issue thread. How is everything going now? If there is
    > any question, please feel free to post here.
    >
    > Thanks very much for participating the community.
    >
    > Best regards,
    > Yanhong Huang
    > Microsoft Community Support
    >
    > Get Secure! C www.microsoft.com/security
    > Register to Access MSDN Managed Newsgroups!
    > -http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.as
    > p&SD=msdn
    >
    > This posting is provided "AS IS" with no warranties, and confers no
    > rights.
    >




  8. #8
    Peter Huang [MSFT]
    Guest

    Re: comboboxes

    Hi

    If so here I post the website content here and attach the sample for your
    reference.

    NOTE: the content below is quoted from the website
    http://www.xldynamic.com/source/xld.Dropdowns.html

    The best Excel learning site on the web Another great site with lots of
    downloadable material This one has a different style And now back to the
    original style after our little excursion with another xlDynamic.com
    Excel & VBA Tips
    General | Using Excel | Worksheet Formulae | VBA Development | Tools &
    Utilities | Miscellaneous
    Home Page

    Sitemap

    Contact xld

    Contributions

    Terms of Use

    Disclaimer
    Menu Page

    Techniques
    Coloured Cells

    Conditional Formatting

    Rounding In Excel

    Last Value In Range

    Utilities
    Reference
    Special Characters

    Excel Names
    Menu Page

    Techniques
    RANK

    Utilities
    Reference
    SUMPRODUCT
    Menu Page

    Techniques
    Automation?

    Comon Dialog?

    Conditional Deleting?

    Test File Open

    Utilities
    Dependent Dropdowns

    Document Property

    ReferenceMenu Page

    Techniques
    Utilities
    CFPlus

    Dependent Dropdowns

    League Table

    QDE

    ReferenceMenu Page

    Reference
    FAQs

    Book List

    Downloads

    Glossary

    Web Sites
    Excel Sites?

    Back Issues
    EEE back issues?
    ADO/ADOX

    Read Excel

    Write Excel

    Worksheet Names
    Early v Late Binding
    Win32 APIs
    Common Dialog - Overview

    CDT - Goto Sheet

    CDT - Hide Sheets

    CDT - Sort Sheet(s)
    FileSystemObject
    Conditional Deleting

    Conditional Deleting v1

    Conditional Deleting v2

    Conditional Deleting v3
    Chip Pearson

    John Walkenbach

    Dave McRitchie

    Ole Erlandsen

    Alan Beban's Array UDFs
    Issue 19

    Issue 18

    Issue 17

    Issue 16
    Main > VBA Development > Dependent Dropdowns


    Dynamic Dependent Dropdowns

    This technique shows how to create dynamic dropdown lists in VBA. Dynamic
    dropdowns in this context refers to a dynamically changing secondary list,
    that changes dependent upon a selection from a primary list. An example is
    a primary list of continents, and a secondary list showing the countries
    associated with the selected continent.

    Below is an example of dynamic dropdowns, to demonstrate the technique, As
    this is a web page, this is a JavaScript solution, but the VBA solution
    works in a similar way.

    Continent Country City
    America Europe Africa Far East Indian Sub-Continent [nothing selected]
    [nothing selected]

    This page demonstrates how such a technique might be implemented. In
    addition, at the end of the article, you can download an example workbook
    that provides all of the code and data discussed here. The workbook is a
    fully working example that can be used for your own projects, all that is
    needed is to change the tables/lists on the 'Data' worksheet to your own
    data.

    In this discussion two separate implementations are provided, one using
    comboboxes as the dropdowns, and one using Data Validation lists as the
    dropdowns.The example workbook shows both implementations.

    The article is segmented into the following sections:

    Sample Data
    Naming The Ranges
    Combobox Version
    Data Validation Version
    Adding Further Dropsdowns
    Download
    Sample Data
    The first step is to create a set of tables/lists similar to the table
    below. This is a truncated version of the tables in the example workbook.


    Figure 1.


    Naming The Ranges
    These tables are used in the code to poulate the dropdowns. The values in
    row 1, from column B on, are the groups that are used to populate the
    primary dropdown. This set of values is given a workbook name of
    'List1Values'. The values in row 2 to row n for each of the populated
    columns are defined as workbook named ranges which are used by the VBA code
    to populate the secondary dropdown, based upon the value chosen in the
    primary dropdown. Each column of values is given a separate workbook named
    range, and because they are used in the VBA code, these secondary lists
    have to be named in a very structured manner. In this code, I am using
    named ranges of 'List2_1', 'List2_2', etc.

    In practice, the VBA code should take care of creating the named ranges,
    simply by examining the data, and defining the named ranges accordingly.
    This allows further secondary lists to be added without any other user
    action, and/or extra items or changes in any of the secondary lists. The
    code that creates the named ranges is shown here:-

    Public Const kApp As String = "Dynamic Dropdowns"

    Public Const kList1Hnd As String = "List1Values"
    Public Const kList2Hnd As String = "List2_"

    '---------------------------------------------------------------------
    Public Sub pzLoadList2Lists()
    '---------------------------------------------------------------------
    Dim oWsData As Worksheet
    Dim cRows As Long, cCols As Long, i As Long, j As Long

    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    On Error GoTo load_exit

    Set oWsData = data
    With oWsData
    'create dynamic range names for List1 and List2 lists
    cCols = .Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 2 To cCols
    cRows = .Cells(Rows.Count, i).End(xlUp).Row
    ThisWorkbook.Names.Add Name:=kList2Hnd & i - 1, _
    RefersToR1C1:="='" & data.Name & _
    "'!R2C" & i & ":R" & cRows & "C" & i
    Next i
    End With
    ThisWorkbook.Names.Add Name:=kList1Hnd, _
    RefersToR1C1:="='" & data.Name & "'!R1C2:R1C" &
    cCols

    load_exit:
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

    End Sub
    This code should be called from the Workbook_Open event, in order to
    initially populate the comboboxes upon opening the workbook, and also
    whenever any change is made to the data (in that worksheet's
    Worksheet_Change event).

    The other item that needs to be setup is the codename for the worksheets.
    This is necessary as the worksheet is referred to in the VBA code by its
    codename, in case the worksheet name is changed by a user. This code
    assumes that that 'Data' worksheet has a codename of 'data', that the
    combobox example worksheet has a codename of 'combo', and that the Data
    Validation example worksheet has a codename of 'dv'.



    Combobox Version
    So, how does it all work? As mentioned above, the named ranges are defined
    by VBA code called whenever the workbok is opened, and re-defined whenever
    data on that worksheet changes. Another worksheet is required that will
    hold the dropdowns that will have the dynamic functionality. In my example,
    I called this worksheet 'Combo Example', and added two control toolbox
    comboboxes to it, which are named as 'cboPrimary' and 'cboSecondary'.

    The secondary combobox is loaded as part of the primary combobox change
    event, that is, selecting a value from the primary combobox triggers the
    following code:

    '---------------------------------------------------------------------
    Private Sub cboPrimary_Change()
    '---------------------------------------------------------------------
    Dim idx As Long
    Dim iTargetCol As Long
    Dim oFoundCell As Range

    With data.Range("List1Values")
    Set oFoundCell = .Find(what:=cboPrimary.Value, _
    LookIn:=xlValues)
    If oFoundCell Is Nothing Then
    MsgBox "Critical error", vbCritical, "(xld) Dynamic DropDowns"
    Exit Sub
    End If
    End With

    'load the List2 dropdown and set the default to item 1
    iTargetCol = oFoundCell.Column - 1
    pzPopulateList2 iTargetCol

    End Sub
    In this code, the range 'List1Values' is searched for the value selected
    in the primary combobox. This search returns the ordinal number within
    those values, which in turn is passed as the secondary list range index to
    the pzPopulateList2 procedure to re-populate the secondary combobox.

    The final piece of the jigsaw for this combobox solution is the code for
    populating the primary and secondary comboboxes. The following code shows
    how this is achieved.

    '---------------------------------------------------------------------
    Public Function pzPopulateList1()
    '---------------------------------------------------------------------
    Dim i As Long

    Application.EnableEvents = False
    On Error GoTo pl1_exit

    With combo.cboPrimary
    .Clear
    For i = 2 To Range(kList1Hnd).Count + 1
    .AddItem data.Cells(1, i).Value
    Next i

    Application.EnableEvents = True
    .ListIndex = 0
    End With

    pl1_exit:
    Application.EnableEvents = True

    End Function

    '---------------------------------------------------------------------
    Public Function pzPopulateList2(idx As Long)
    '---------------------------------------------------------------------
    Dim i As Long
    Dim sList As String

    Application.EnableEvents = False
    On Error GoTo pl2_exit

    sList = "=" & kList2Hnd & CStr(idx)

    With combo.cboSecondary
    .Clear
    For i = 1 To Range(sList).Count
    .AddItem Range(sList).Cells(i, 1).Value
    Next i

    Application.EnableEvents = True
    .ListIndex = 0
    End With

    pl2_exit:
    Application.EnableEvents = True
    End Function
    The procedure pzPopulateList1 takes each of the items in the named range
    'List1Values', and loads them into the primary combobox.

    The pzPopulateList2 procedure is just a little more complex, in that it is
    passed an index argument. This index refers to the ordinal number of the
    item selected in the primary combobox, and is used to determine which
    secondary list will be loaded into the secondary combobox. Using the named
    range structure mentioned earlier, this index is used to identify which
    named range is loaded into the secondary combobox.



    Data Validation Version
    The Data Validation version uses very similar techniques to the Combobox
    version , but the population code is specific to Data validation. In this
    implementation, two cells are used that utilise the Data Validation
    functionality, specifically Data Validation using custom lists. These two
    cells cover the primary data list, and the secondary, related, data list.

    This implementation utilises the built-in Excel Data Validation
    functionality, and simply sets the function up with the correct lists.
    Again, the named ranges are used in the VBA code to facilitate the loading,
    for two reasons. Firstly, I find named ranges very flexible and powerful,
    and secondly and most importantly, Data Validation can only refer to ranges
    on another worksheet if named ranges are used. The same VBA code that
    automatically names the ranges in the combobox implementation is used for
    the Data Validation implementation.

    As already mentioned, the Data Validation implementation works in a similar
    manner to the combobox implementation in that both Data Validation cells
    need to be initially populated when the workbook is opened. This is the
    code in the Workbook_Open event that performs that function

    '---------------------------------------------------------------------
    Private Sub Workbook_Open()
    '---------------------------------------------------------------------
    Dim cell As Range

    pzLoadList2Lists

    Application.DisplayAlerts = False

    'this poulates the Data Validation lists
    Set cell = dv.Range(kList1)
    fzCreateValidationList1 cell
    fzCreateValidationList2 cell.Offset(1, 0), 1, cell

    Application.DisplayAlerts = True

    End Sub
    The primary and secondary Data Validation cells also need to be
    re-populated if there is any change to the data tables/lists on the 'Data'
    worksheet. Again, this is achieved using Worksheet_Change event code, which
    is shown below

    '---------------------------------------------------------------------
    Private Sub Worksheet_Change(ByVal Target As Range)
    '---------------------------------------------------------------------
    Dim oFoundCell As Range
    Dim iTargetCol As Long

    If Not Intersect(Range(kList1), Target) Is Nothing Then
    If Target.Count = 1 Then

    With data.Range(kList1Hnd)
    Set oFoundCell = .Find(what:=Target.Value, _
    LookIn:=xlValues)
    If oFoundCell Is Nothing Then
    MsgBox "Critical error"
    Exit Sub
    End If
    End With

    'load the List2 dropdown and set the default to item 1
    iTargetCol = oFoundCell.Column - 1
    fzCreateValidationList2 Target.Offset(1, 0), iTargetCol, Target
    Target.Offset(1, 0).Value = data.Range(kList2Hnd &
    iTargetCol).Value
    End If

    End If

    End Sub
    And finally, here is the code that actually sets up the Data Validation
    in the two cells. The approach taken here is to delete any exising data
    validatioon already defined to that cell, and then re-create anew based
    upon the actual data in the tables/lists. The primary Data Validation cell
    is quite simple as it just assigns the named range 'List1Values' to the
    Data Validation set up in that cell. The secondary Data Validation is just
    a little more complex as it is passed the primary list index as an
    arguement which is used to construct the name of the range to be assigned,
    based upon the value 'List2_' and the index, and the error message reflects
    the value selected from the primary Data Validation cell.

    '---------------------------------------------------------------------
    Public Function fzCreateValidationList1(Target As Range)
    '---------------------------------------------------------------------
    With Target.Validation
    .Delete
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertWarning, _
    Formula1:="=" & kList1Hnd
    .InCellDropdown = True
    .InputTitle = kApp
    .ErrorTitle = kApp & " - Error"
    .InputMessage = ""
    .ErrorMessage = "This is not a valid Value"
    End With
    End Function

    '---------------------------------------------------------------------
    Public Function fzCreateValidationList2(Target As Range, _
    idx As Long, _
    source As Range)
    '---------------------------------------------------------------------
    With Target.Validation
    .Delete
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertWarning, _
    Formula1:="=" & kList2Hnd & idx
    .InCellDropdown = True
    .InputTitle = kApp
    .ErrorTitle = kApp & " - Error"
    .InputMessage = ""
    .ErrorMessage = "This is not a valid Value for " & source.Value
    End With
    End Function
    There is just one other thing to note regarding Data Validation. The
    example given above works perfectly well in Excell 2000 and on, but if you
    have Excel 97, you will find that changing the value in the primary cell
    does not cause an update of the list associated with the secondary cell.
    This is because a change to a data validation cell does not fire the
    Worksheet_Change event in Excel 97. The solution to this is to use another
    event. I have used the Worksheet_Calculate event, and to trigger it I have
    referenced the primary Data Validation cell in an other cell (in which I
    have set the font colour to white, to hide it), with a simple =List1. Thus,
    when a change is made to the primary Data Validation cell, this changes the
    linked cell, which in turn triggers the Worksheet_Calculate event. The
    example workbook uses this technique, so it works with Excel97 on.



    Ading Further Dropdowns
    The code presented is fully-inclusive, and can handle any number of
    dropdowns (although the more you have, the slower it is bound to become,
    and the lists could become unmanageable). If you want to add further
    dropdowns, this is an outline of the process:

    Open the workbook with macros disabled
    Add a fourth control toolbox combobox and name it cboList4
    On the data sheet add a fourth column labelled List4
    Add data in column 4, inserting rows for repetition of Lists3, 2 and 1
    Close the workbook
    Open it with macros enabled

    Try it with a few values at the top to start with, it is straight-forward.



    Download
    2179
    Click here to download an example workbook.




    ----------------------------------------------------------------------------
    ----
    Copyright ?2000-2004 xlDynamic.com
    Page last updated: 22nd December 2003
    Found an error, a bug or just want to
    comment on this page, please tell us
    Copyright ?2000-2004 xlDynamic.com Home
    24742


    Best regards,

    Peter Huang
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    This posting is provided "AS IS" with no warranties, and confers no rights.
    PK\0\0\0\0x0:􋂀\0\0\0Z\0\0\0\0xld.dynamic_dropdowns_v1.2.xls|\Tܻ ,}IYhc
    (*b\ ,Ehl1F1&QӌѼĘX3$h=KlS9s^X痁3swδ3gʝ#μvY#2hq&6<Fx\0hX@  }&}\0rrrrr\0TxJj \0((((H( P8PPg"T@1@]b⁺%J!*@=z\0z7A?P
    P*PP.PP&\0*,*l**@\0
    4hp*|*@@@F 4?~!4H Jʀʁ4@*U@5@ @F h2*j1i4~8HCJ\J}p c%
    *Kؔdn

     3>Ȝ+\0FA]1-GE+sބePwQ7 ãgB*4ЂBd(u&I*)ϑg"Zf3SGѷ}m 1*G;+0Hc!)ҝ(C{zWy=F1XP##җx5Q1*LM?}[TtsvNgh6*a>9N9`oX>&WXo}k) V# |0JS!w#g%CkiY,/Gf<)Y }Vx޺ L; pߒ
    5s&zHN6+ŲWZ :v{|5")ٸz- 0* `b!N**!-PC0%Bn YMQiX\ƅcafĄlJn*E^)t)M-J$Y*ɫVw* {䌌q55iO^E[ ua0vu\
    S(-
    ;C(\H*B>c_}0> K|oGmc ֫8(c|T_?9X*T{fxg3BRT*aFE.m{y@<]) zY-[h&9,b>6|ʵW*ZʉNfA߫Αur9Q]3)O},Et&
    vPt}e^YUViِ]d8>p!M\7J038|T79#&Q?( F?64 3j&\0)¥C3v9Uur+,`aȹ% VT&*iMRrJP2i$!b薉JZ-Sи&bv5T7AOkͫ[T4#KHhS5 Dzs v38c9ϛMPڔ:Wc4%:QtCJ\[B
    8*7V.Pd%&T SKHaboԜnT%Q/-Y*npSPMNfV#hUH]bJ=Zirlsa#Qؐ\0L;G!="@u`+a9:۩yodi
    [,ZnsDIJ*D@
    <1|~.8~rLZ}|tٮ^P*S,r4nLla裏3'.;vk~N=no\ͨv;V9*Q'?۫gn^}s/qˇ<ndֿZ`Q<-cf;M+*{&T{*[3ջyuW_HǷ}|( VV&|J~IςK>KԳ
    {@hfΕ{r}J,+0=}ꩵ (^,?$p_ZsͥWl^9*'OHwC!-ޟ)bӑ!siK릆G~{p\_/{{^y,mז~f*B{<;/l~Y;2xCZ}XtPܷ^t(wBl1u_n\qXZếǂ>*֌ѲEvVfE4:d]QI={E7֧&~zBw;{o-7h]8krFk|Woq56j- yNV<ugQ>34wCܾ s=^p9
    '.k͛}Vq%o9߿:]
    7}~P
    Ӫ[vV~*k45hޯB92_hn >yf?+*Ÿ%]ӍW欎O2e؝+:\%*4ڋ._5fCHWlN1i듷D4qo_>}'< ~fZ&{>tV1ےG<N;}}bxz_nSn*=q}%їO,<^zm
    ޺5 'TNjϝ>BZ#_aMje![={n϶GĎqt|~3~vqerۊ5{{_v_[Nx<9>e>q**}_^lOrBW:h*I]v9-왤Iko<r'<*R﬽|{?a&11CO*ZBmT_8z`2 ٱQk\gc0.> {HwFwlg9to<]zmA 5ׂ/.[kܰ
    zi4]߻#f>1'&X~%&T|]Qnm7s~|~0k̺T珔WƜY4o̫Us>o/Cw *K(giñ>ӧ91nz*kO)R]({3_
    )ceājJn2꽀nOupd<*WOhV
    LR/(E,RGR~'ȰCNwܞ{>
    ӖQ>:i7ۖzi?5)I矜ϝN82_=Ҏ=_=alUG-L*36{Ҽ?vԯo2mCxo۵}_
    ;\05Kv=EQϏ98'| WΚKR_cǂgP<!Kν$;T[ni秖۹mأ`ߖEqK]N*:OlqN_qij߂-_?
    w1.Įv,:?Fҫu9N^x7^:*Wf՝.بා-=}'^&Nu_
    z-w[Quk:}o-l8\CPʥ1Cy>Ӣj
    >={ڇS~R1s³'ٹ
    7.*]n*ho??*׿:3qr2+k^[a?vr*#|$$loG_>tυv:[s^^\uT¯CٮӾkfGٯSt}fhnXά^(:uJ!wﺺaxOm3onrmžӖ1'xkwu×~7*|N3_s<6ڲ;_?1
    ?wKEkZͤ\^~x„&_fo~~S݈k.l)H= *n&>SHhr`7!;ds\0+aA62?A\Ҋ~lfPJ*) TC3it<pGefs:NA j,̩鵚䦗â\rp`xk +k4:&7xFPqQb`]ou.m\oP`YӡNJ-2T*sVS"pj_,\0+=PKUrx:2g^cfL&
    'IfΤjUYfh+kИ < 2M}*mI>P
    X
    ɤݐ4ș8*\ި71)}S\AC{q"ƀ7rS4rP4wBDc6bA_RGK8Ɍ]:.q}5áX|^ǗqZ ՔZ-oZkxSzJ([>4a 9߬tP)+ mRBG˰#:; c9GE!XMe1W<yt(R+B{QPyAij:ѩi%7g*kWUA,Bi L)u

    m[*96"_ia5Do$*HxCBSU
    aʸ*@Z&1oШr9]Jkz]46@0`P*zdm1/d
    o-0?UhLA_QWm!:Fx-g*2eL?GY[ i
    zΤ< T˗K:2xl
    -F^vF,PДdxKm*'a9N3qZ9&28B g3x
    YTnzB䚫y^%q*i]תH8q|L'jUCx0,ёvj.Aga|0t1`CsA|-Z!yTU7
    Q]hLBh+@t<:t*C&AGュ}$*(Ush|Dv*ZG3HV*QXD;?[S
    `NcժA*j}358wL+}|s_
    9LG$#07} Ow $$JQ|DA_?$M፨QWV8pe(:#NxjI^:y//B?RB9
    DfQcs0!7㡽I&N0Lƒ CBzlGT剑TO!9q8IFhkw|O`TzrmXLIr9CFgw6l, }ւ'Pr# {`aj8KQa[?DxMھ@;@x\*qo"Bxt"tD?4wQi~gYi}g= ""**!wlT!@xz% g^zR^!0RL|D:d:E\0}ȱ<di[C0>1gOfIMXcu) w#+mږ8Ɨ)~\H!_<F%\,+2hs@X0Ck3K!_߼>幢[oe+Mv2l{[$QDD2Ba(Z&kPV&>'o_C" m9?_w
    Ċ3*>{78HqLb>'&쨂Uٝ%b2Ђwon/VҘ
    JX4,h@A51V2ĹAIZ;thQ!?%nRڀ6N8Vb}\A]S8h~
    hw0\B 6M
    ]I*XԐ#iɨ]j.9DTYؽt",3Xy {3'Y*7T4}u|"M54wZz瑦c]WclkJׁ٫t.*#5[֚k}v\0&ڨ5x4ih׏&[ Q<ub@ t*rO*%0]0nQoQ(f^݃Nΰ&E
    >ZXwR$Ҥxݏ0)͞-MҤI褉N4Q7qDM4;<Of'shs݌lKgЙ$,n'~EX"lG@F{f֓;N*Xz!pj`XG"W70@C߂ bps ASF(XiT\0[C!,

    ?(P4-
    pN( w~0Em&3rF(iQq*Bq~U2Nx脇,~HH<"h!C GiOh5P : +L4mbm*Xܝø5ZVeJM**OC|Se z\0V
    uzSM*ZE a3w$
    !bo'_u=V
    A<C:gvvB=_j5
    8lg)Ϟ#b:r)$K`:NH5oU0)':Gwxg;'0,5;`0n:&S6$>JgH)]Y̧ǷymksSew2ƒ\E%C}ҿg|vٛ%l!Jgmo!r<s4IA-ߦ( iueN/6</X
    a+-aSaZ:&X%l*,TMTvxvxvxNvx
    ;<g;<;<W;<7;<w;<;<O;<^;;vxvx~vxvxvxvxBB[a߁j]9A&AT{ b㥘b*(`N{KPcG)&gRL1|ۄI,Egܹ[!BJ1!PB(A&IQ ^ĺE R %dJ1P$JKTS
    )H1$ tCL7)J(%8Jv"ʑJpb(![Q$Jh/Jȑb(A-JPHTRP KJ(P/Jp$8S ~"ʙJpb(aC I /\'Jȕb(L*IpD+0_R %$ nTB*rJ10D(]N%tQT2)J1@D *!HDyP Jȓb(a(SI%(O*aC äJ%(% J*!DD)R % b(A+J$xQ "
    !dC R %T% TJDy2xH10B(GC%tQ>Tb)FJ1*%$DT;*I)D % N"=mFQR %L%J|pKC J0$ ~TB2|L=FLGSEڟ,iR Ә4R\0&RD41i44M
    "@Q"EMQA*bDTEU` LQ]DT0EՈ BQ"*jET
    8JQx[i0=_?XCv?UXNבI5@^';p;;vxvxQvxvx1vx]b%\4%%uC{}B*X!\=F)HQET
    FQ3DT' ՉET')%T8E%pQ*Q%Tg.:Sԣ"*RBERC"*f( EQ=DTE=&%T4EQ5[DHJQ15GDuP](BQX KQXzZDI8-(jPGPW ѕ""AB$PD? !%D"E"IB$QDH7}=ͳoaQ<i7*¤?.M_`qp^`ŏ{BqX0ABzh;K^:Bv:ׯ )ľ+ZUoX 3Qp*p{D|V?鞩;X3h}:4wvTM΃{21lNskږqFBˬ(A&Yb
    J{R؄R([&M&*1%%3JgJoųt',yB"BZkM B`QA3Qz,M\03غFN p߀+1y;4i;KH\0nttz5h90U'B-Rlo6TJ{?bªl8Ʋ,E\h3U-^'䟁f-ym s
    򫀟a*G~
    |t6#W@ lwLH^eiun+m䰀G^C#NEy_;\06Spz<uxwӼ 2L]C'(Q L",NZU6K[+-{)_\KٻuJ{>`@I8j<?մuc݃391֤Ttu|1g Wг"E4,\(RzO2*xֺM+n;ra-bZb*<O*C0`/ bXE側8W<{\#mO)?s'VPFcR
    _8Cp+a0`R&SSBrl-"al&Hg6aQl$ g)?%?=%.SެL*_LTh:Vc4/UXQN< 9OMgZ7Oȏ I,K9*7ĚoMX_SSS:5L[vZoƘ&{MKJ@XggDRM&l" *pW DTY032„ I yɉ2AHw"Xo&<!{ӲbNQWq%̒Vn3)bm<^̗h9☣&o+FiUz\0 WVi 4DoLKFRX+ϐ%
    *TVS1Dƥw7<2 T囋Yî¡U.JTJxժR2]*B]RRCeV{@<>\?eT9um1;ԻKgS2xEHjR6hCc\+1)jRφEEǃ]*Lw$WggYhicLYTk)M)y,J=yhFhzxԐG{:DHz}<ZZjMQcU ѱ:#w]tTNFtim<=2;<Irwtө<Sad Zwc/@phv𤾞Y'/nӣEYzG#wy@[BB8nb܍iN)*U:+@-2+`3[ cl+YOGș6{v Y?CG] ~CJ/MȔLpFJL"7Y:㑺dq?ð$d!]9րr@32,x,StwbhW
    w;NdB]0%*Bj<* P`"ՖƗ2,rw\0Vt
    ܽNL@x@_C=ZVZ;ķb(M*QUKۍB|iɉ6J~D\jVm\)  4&+$V}n2,aUTGY*f(t!_Q JG !s\JA
    `9]=p{OD.~/4;ʾ46Adz,l=Swv~xtNj5M۟w[ld)Ƀ|UZrR:ϢXB*CMgVl b
    <k-!
    r;- *!8":ܫ(,Ծ9y; 72g=LJN
    ߸w1mYg+Y(kUÏ\0G3k4ŵC$~=k,}yo4LaQS8czӃ6>A<cZqs82ϗR>ZpcL*V(;z愾* |h@}`j*xXVyOc<V% g#[O?(hpnvvvQ&;}{8*Yg\k![g$ w d`Z|g>2dL1iI 8*!_b|@+
    j Ԥ4&}ofnuړё۷33oǬΧpq* @@#~*=7![iJQ*@`z^.E@8yEJЯpj*&+>#zD| ?xMyF&<* 719=
    S0)FFpKp*KJ9`ZH ӯ
    4M2HH=[! 2Y*Cq!m]j\"gTD<&[1]
    ӄmI[*
    -anGT=i*5Y!zVFڴ>YϨ*g܄P+bS<*Ix@)WB4ߧqA&IˡV~MJa
    ;L05A)0nǽW˅p YzG6*?PB *A6?yޏѯ16<0+$R\/Ֆ=?x_8*w$N{w`{k>ճko]OY\O5Ļͥv7 dLK$p!o#4FN{Vؤ5
    (N1XHٖnh m 4/02-*g'*lw:C!
    b!UB0팆j{\2-wep/V)3|9os@Dzo@8PY.^X[mW}*zUsm5kȻ"сX[O,|ht}.l{#bs<_rpx?M\yH.{qxjy#! jzaw}y}[:فp^&KnA&K{0އuq
    #9?\0\[M
    ao!56"\06£D^aHIO#<\}E;~<#@?DxG@;pI( c@x
    u #Oos$x @wW@X> o| |>D-\0dC2+Ap "@ mWܞ_
    bˏ:*
    18ީ㣗6K\5yet]?~oi&{U{|g{2R2ppܹv!"!T9\]B$ǕQN> "Y9yL*A=\0=@96vj>9<1YڊQN,S(:ț(y>FQ#٫a-tZlJ2sUD1t<uk/
    C L"7H5
    c*uo<VRVqrcj9pхmz>UM\i<SƓex4xEu>7O&ku.Vg*qD1\m%hE*a|<ȥ~%3qgۓ Qې8s =5'ZqT~ƫ~)-sq'S<**%dCD={u{D|LF&ky_{r0ROo|T(`zȡDkI :t c/,`-UU n<*3
    v
    -ߵ5]]҈E,Tb{
    eNjܑަށHphMіa)6k`# ͑Xꌹ_^ZU\)[5u@d86ڥi;.<YS_#7ւ˛"wt)|R`˛z"A*@f]O@4TmfGl-ۻ9Rn9*ȼޓjŞzs6%vd=JHj쉽{6eGV>%?%{ *Ui=7"oq{mPW` 1-֫Bc;ڰ_ hWNoowԌYP\zmW+n* 喟KknDv1޾>Qy<T.ݴj{LCvk#k1İb:cL^4?_hBr}6vƫS?0=\0C)О}3<@|ԅ4rUzƫ\gl°MbS5WQ0ti6dTҡ~iy|8urPU_qM~}r>)xpK$VG8pUG8*L(-B(A 
    !C؃\0aWBx%+ƒz-
    >c\0܇'Nu~+r7Ēz ܩb~hbI ?^9s*Dv_pWӁPG:*SNH|U2Z~6|DG1OKk-Ҏac4DUvIK66rd\ TDOձP)飉IH*('XZtG#a'|K~7*s\pTG:qQS4anEwJuX>]eD*Zt<)6^GW"WL\jřp– _ꄄ6#]4!S6SL*xrk
    y8aNjHƿfs:YOdX`/ HddKx3 LQ:8ַ#.O|J P6W
    y*ad?~BmaE>7K/,O5VKw9!}~YzѼb蓍g}Mhz?ftJ*9!ݓ+!tF!LV~UPqN*d{~Q&<)yg쵡?>6?87.-H!#ѱ$:qD="2b{&d9csBz|yH|\*IKqh:wmı&s_#Ё)$=T`e) OaRk=Kа떁-$<]}ۀYz!X(d Q9KU3MeK88BN,/x~pCs&w?kx7>uBVeEg?Ws>NѕQ25m|Ptre`d?wȃȡHGn=Tkr3?GmS~_1Y_O6/Չl\#&K.b1CuKCgHӸd="6SKh5~|eyjpy̡O)*_1Q|*Ӫ45!ɼӯjZ6G|<ZmͮLM5q7S$-y[Z^)1BVAev3HMl7fV3~HU i@KY>dgY>㶩~0d/(׉w ?" OJxO쁌Y;-h&G~/_# ?G{,4LC2߅*},>7Կ OOYE9 -?֕߷O -x*W-g^OoE~ <=D(M@U\0q]?Mӻ>?-Uwߞ+SN7dݦzq cX98۳^Ҟ x
    *n1eaJfX(Lt\0 'Vu@'gv1ːJ*Md(s91+|g2 _v$C>GX>dnǬ0
    < qs5|%=9v 4&>hp8n2UNڑp\TgO;k<Ofnηf>!*و"]7AV)Oa2hBqW
    цMHY^&sLjUzc&GVr~sO4S&L|%Bx
    n8qD)Oc8C)a&w t}G5|!*|:
    iB{;p *ȵLJ8<{6}9\ 23[#Z C1vE
    **G/[P}PѮ>#M!E܈zk4:P?1"ƙ錾[v@5/sϊK@SP$/J4oMNoMno?:5,qPgx>~*o`[50@,grF5s_\0f;r{GrwB-Z} 9 ]^r
    Cz@OhK}w c>NSn`Ooh/JQZyDi>5>U6+ vRcDZ'$({MZc/D~n+ W£xAj\0!d,ݔ4695 ,8T]wRW=#v٤;cW%;,:w8[drؓ{TX
    
    j}R!wN2;IOg(\~jY,u|l:o48rE`hC-H?E֛'_ M4i롷*WK(ksGsiSx 7x>ao̫)Ew#AOZ YlF3-
    [!iY
    ;QSO83;<3ʝrt}|I|L]'@2/mcTwV$Xť5;%
    W8+2[%uNv*Q<6tYڥͦMyIΩ5iQv%SFլL.šWa6FBPX]V*ԛe9ەjfO3y )З3w],Ao#C3^e\0ٖOb15eLڿD"P'Ig^M'diYgG*ogVYUi6/i'JWѦPgo$Ȫ425]6ڜjAVcلaM#'wC̅"יYL"e#q6LV!smx~*hq|2]_'vu]
    mMTOiu\0wU<
    \+sPИfhe,?ҜMo_G&ˇjaa<ݷX~
    rQD*GJ{\9i=-CNS=|GR+4"eg@
    -ʷ
    QӖGR
    UhKpHcqRjLW"1֞L/Ofg1)6dB+%j%AuWMoI;6e'jpr<
    O;?WcLmG"k㜪$8.q{/$~wK*$o"3Je[)*RR*{^VĚ3T\9dl-4Z՚FBd;/Md+e)Ý{6}E= O;gy @B42#}A *}k}O\0P(1Vdiڀ- !,F*K!h.?6<`l;Nj `8ccl!$8NK<e1- n"'BI 4Ej**۵Vq$rR6"mF*6]Jsgޛɮ*ߝsH+c%*CuD!+l~a5"~#> #܈ Qw.YnFhFhAhE؂Nַ!liO"w*\ד:v#<4^t"<"AE "t!+\@='=1^<9݇^@S\0T+3g䞁|%XCnq/6$&]Sx_#*Mkn2قz8oSd"h"U[u9XipVO ?h۟~9z]q/ydnG~O'&OVΞw;]*oxœh;;.d#f*͡<>$ 5ÏT'ćX#"'yՓv<r喼C'5;Vx︮HzEƈyxmsPWy𥘇s9;C*jE'}gUvXoOE3ls rw r{sb+ruoեŅ)Ny,)&oWXmM&qX,*5=c:cwml3G2]Y|[U4DB;N4\aA 0\s8]X:JS<>x:ާ}w?D?:äi 97XƵm
    5͛s$"Jb*#Pe37cuLB* NX,eޢG
    G'*(նH֯?JtG7-Vx9}Y34TV?U-̿u 2'QJa߿*^+PsK5{_WaGdr w{k7M.*YB{,ni.ps*^\R\fh`Pȗ0[Lai&u6P)O)':/H5唳 ^)>MR,oܗ@)']̳
    MGe?YowѷϔeFwS:mx~%O'*yQS[5^5q25;
    $*^]*pm&若ulYmLbem&beP'*F8 E8- FܰÜOZayhw㦑˘̓BQ0&uE^䎛XiI%5*6$
    ;թf8_X$ϐFr7uC%+~eL*|-$?؈'?]k7p*Dsj'-Hl{C]W"$ME# 㝈y*`-"[֟Q,j[^\0=:ߎpX 0s9:iwMS2
    VÞ(w4Gg̅u
    דCsP+/?pQ̮*.4%Hk׺FYצNr3**x6H$@y
    zTZ]}sHjȪGCE_K#\ϐtT\Rj!)`QȄVDO̊\0) _N=J61hv\ĪEz<*<Ly4B^\0r1hUܬ P5"ø[>^`64C5¬I5g4*Kk%f`nҨAgg)Xo2xo8p@Z#lq$=g>d~sE@"`VtJE@-aKm0khf5F zEc`Fk )a
    16, S W
    Ux!2kT*԰Bk׼b\
    ZokN{Cu#.Ma=gXO"*U ϮeMwpimv9!=g_PEe"ePvh Bm#dSVKQ'D+*`Z'-QDI ~KW~OltZ|k~K/H<O9_t<,~);r^|Uۈ>¬]@U
    TnP|\060/:`yǘҘ4iZR ]"Kpab?֝G?
    kVkoMhv`־ޓO&99R~Z$3[xrc'E/ه\0D@,`?c/z9x몜Gb̥Χn,MHC8FMZwy*Ĩyqf -.Lo8q3Vmi7!6L:zXڪ
    aGuX\# iD
     5)^5,*)%,xgScYjȆ`̸h\0Yc8m '!S Tf\|wkZ7l4Kx,*)/*8׶r]>eqBN;,5̒
    κsY9*_t3[1\/"\@80pa40I!WHkK;W5@jKAb̶ii_?6?Z\ےUƾ>\Xb1(OŒ@]tst\0<prh
    _dG*3[x n~*@8:7DHnhb?[K
    }t";
    [$H"5|o0<yu <4Ud߆IjNcIdvj=EɳMS* ܩaNPzDݮ.QI+{G;Ӹ?`r"QO٤
    L>MdtRE>~^#!gS~Xe7*?RLg+f͕֘~3kV/Y2rY 7_%Rnh%o8svxG8)_Br~m~Dy2՗iV?NW`O7#_?
    'G!$^+Ӣo[?>#*΂zqNzOޯHNV~Eq.p2xAHOr-H2&VIW
    ^i r_
    [;\Hp=b~O)4\Mי/M[\,0+ʝB+Y4f5ļ%g%g֣TaG<d.]h*sIa| >-7*
    -*)28;^鶦ݔWC\0f%d-}0VΤ^6iexF\tcV։įZ65#oS2P΍I<۰
    z%=/ZVz̨7FӋzp?7Z;Ma(.鿑u8#'(~239OͶdD77FT)7oģφ" JnU9+W)Հ@Rۅ<?*^RMӪ$ah*Lg7K6#9psaQ`aȼK]7*λ=.̔un.˦wz lYۉ
    [;<SjVj'2et
    B!8t $Kv3<1cx2Ä}!5Cx_+9Eә%}Anڹm~fgH Dzku:@Sa\Mu*A
    XǘH}qi`iN*{m ["ǔ?5㺔ZQaWRZ
    %YUO0?^сʺfM겦Ph[" =z(1]Z=aFjP){"<#s8<{CJz7OWvڿ؜/&K/4jӛ{^mFBGuW]\Xtzе/^\Y7+[70Kٿiċ^9u~R7've
    +P'O[ ԎH*^|༥mojXql85?wRo
    gf\(bK#WzpmSeyS\0,/?dV-=l5Nqpƣ2)[TqesvYq?qW,Q:>E6_"ғ閃>
    -{=.E\OgCO%g<39]2*?kպt0MvZ.SHnW;[{oԕz|
    M51D90lfs;wRsdj`Irvp* FzWܱ~]-eʵnjX!ܗՉuTO>6UOk] ^Oz#bxK}]}FVGׯcF*:JZN}. s~H;pVh\0uPeâP$l!7 II'b}!<
    ®`q@.S:1
    rP'^ 0bqg=E4^%m C*qأW5hu]Z}=A#ƈxe+) dGYc+7,Oԝ~3C33ߥ7.r<[w_>iLe13j!Xigh'A6K O;)!\3!l?/:⨶n?Kb|_-91`QĂ(r^K8Ȟi *71*t`ҏKi qF;Vh&p/S܆ yRԆ߁8o}6ܮw>|olx;T89\JeӟN=6:DfGqug
    ]%=uJqwѽt|/0x?T)]:/ eՎ
    ⴺ2Rj? <bN~7<K<BN48!ԸԜW,aՙ|g%īs,WweWH+ |otOQ/q"4xb]uYa/E
    $t©/s*\c[r? 68go"~Ԇz,g"~!_>3
    ;C2Lݮ|nMRX/{6FI"ȍcH3_lnB8\!f :iÙtԚ;:0"?i=Bo r6q7*̸
     TLd0)lṿ{F%?.~\E\0BC?١E5S\0B e/H޸a^Jzp
    z`\K~\03."eTI(qc? hmcjF!M*8 a"8'E.~( Wc9K_Vo/nH74"I5{p0tls7%O$
    m'F-f?g3c.#l\0T`ZoCQtv" G>q8ne\0VwL:a[t~sA}
    ԫ%,{,.HWm6I9B1NP.P9{JU{Ќ),aTذ~~)`
    S,ø~됢Z[?] |չfvl† Tf&%@I hslv'κ!gM@AAk`PV^oWڧ T۪굽snBSzŸs̙3|;98Z*7ꑍ9-T]vm8I*cq/gpr:P~zc>z{(B |19@* zAںF%53JKM%wrk7Ww0Sl L[ LXOn,#hSkxoʬi!Pi!"\W-.:@Yz-ObJhQzԾ ,%d
    quuFdj}5Jh,3ЖYrejͽM|n_Nh*Vu
    &=|"1(?2sE~$xw|;xc|/`/sC_W7.ģ-}*8=63yi`(fNp
    A֦Z1,ݟBk톩C~SX ,X KmeMQb E}8/MtZnu9j^XThnx{ _y/U7
    .&\ZH ՈųO**/,8<epݳQ}l~
    brs鸼z]+\2%<huۖܶCK_LȋBÅsYK6&tNszHxݝд
    1q Dx{ihҌRMXhQHHXX&<z*nA^ ϩ \0q!\0|*
    Yfξ^"׀!(X?3dϏM_hA_:U'ڗĨ~]ٓ5߮D,PJA_Za\0xKi^я7 6"G܀i*a &V68viao휦r[;w!v# F "A܋ψAܯ !F<6;G9m{#CC\08 ēE<7O#A< Foq\0 y K> ?Bxckv>O?E_ ~ I˯o DxÜ6{;
    Ļ"~=}?@?"S!F#'W)3=scF쿲k)Dz?#˷14~bwߩç*h?߬GqpFpN <CGSJ5EˏWHAìN\p8)+=۪S.](C9! -iɋ&܆>*.yyVtڬ$0etf:lI%PfUޱlSLh70SL٣^u4d*N;I*hSMS)ҼHjI)2}exIKJ<ѣJ+{"V7~u8CqG ;zNJ-7cGn4'Qy^gM;:tO&c
    אld\&
    t@)f!VV4qn@L165AMqG'q?;u>PCc?*0~WN.lǫ*Mz
    |Wy7" nw؆zxw nE|qhD܎ zۏN$ֻ{w# MDy-~??qԮ?DRT0y9"B #
    Lq[ pWqk}NI¾ubD9&<bp,T'BK*,PYLQq-?nQ٩ìm֜";T&5%S`γ/1~Bߑ
    əKLC]3fB4zU{Fj*fp3E|NE|/7ْ Rf/[ 2˗g' 2:20]8,Su'w[
    W\7`-20wzb74ʿqQ >F;>5q?忚Br/ba"8F?jw|[*"ҏ*5]!IX\097!l<FrgZdYe:a)#@4Jw//~|TXK0ceF%uՅiZԆkbzf;]5kT%v1u1UX `BIi-j@C
    *G=*(ʚ6Pg^U(Yv)w#AhͺXVѾ5ɤuˋVuR(Du 0ɜ2Ң7{5jM"kYww!g;BCdĿ)LXv>Iۮ7<gv[h?Mt>×z~s[UĞV\0'[F?6;._p@߾
    SYw!S\ö^7/,#T<Cr\0<YqٟU&L)^u2dMƆwJ UBOu޵_ 1/8S.*S^W*g*6
    R ѳnD
    ~,`p0U 0͙x.E%1'R|*=4fzn6:
    9})4f_n(M6z˯J=|͚Na+.8_xMBJaCiL!8 /Ǘ:[Սzz'?urc?km0ߍpz#6tD!]L9;h~tK8ֲmw> \06ezIWIFG3/Wa<T-zx9(HRa(_
    n/2Yn@o&ET0P *S*=\F`z]T.=Lnt{
    G鳘%l_F
    $FW1r+Mhr8*|Qbή*C$ۑKU)J QXtBb#=*ʁ AlR²EFPe3G_HUD8Y^ U}݈s_N{װ|@UeB@cr+P(FNE濴Eۿ Y+9Q_,{9ЬzL_d\_l'؃*ݚ- ,=A]`0**ߺb Cl_M=ɵ-6FR%w)=r4ߍY_28x=1,b1}6&SA㗕[-]iwLRn=G)1 2%ЍCg/lԩ#np/+hl#i1 CX)<YtjWzӮq:DX4T"v׼_is[$g9[)*5.oy9Ȯ*TZqY䰹+*+*{*vO-]E&UVې**UU-@awk.rObQ3rsSs
    [tk|0|^H3mqM$5D_\nf&o.pv0ɟDHCtb;~i  D!o (/q7J: D-H ;hA0E2Зw]ew^iL"s?.dwfm-85b-m.9MH`ILo9>>ԓE癡աM/pi 74szxTL^1n} :AEF:!#?:]tyt%xo(?=b.*b&LXN2/ wL0{Hw>X90c_a@$ 8nnpdφ=N<7"37qŕ`h3G{6yC4 O dmRVXK\kD4˕xHᔋ
    U_p^X =|27HT~I*MpE"=SƦۿNr$'[nƺh\0*af0}&\0g L&;
    8NPoY<MFR4-3>
    ɬ3Nj;!s&uJtŸa=.ռ$eH$=~,#%IK7XУԇ&QWiM$4CO\i}h&czWۻBс Ȑc܍Q_WY$cz# wu#]Ɛ7ԪvEYf\0 88${3b ćVfll~X\2n]답]R7j{b
    / LZWF' 5u)=L55İl9lT1"!ѧiW$$NThP>xtDuv9dV<v\VNd4TS5P/0]gx4
    {<b+LiA'<<mbi!z
    OJJ gYigp¿!Ⳗil<?_Ua= FK l}c\0.-l:&4'3Lk4$'st辤Ǝ^786V9tQ\0azyPoH&٩gGZ.,Ӌk -A$#>V1w&%sTdrƨڱmɢ̌cZ_#!KJ%=O% qHs@nYt9,oniZe!\h1%qϩ]*(eb[,oCG{o߻=1u+yl]%ms{k$ ] wY"ñ3 @j˲*?TT#.[8iYS}U\7'WJ$W8Y S\0g)oX
    &r`i:y}܉*8jj춚Rem**_@(*=eWr~[qee:w1W.?yAgy:.%,ϑAk{Hpo";o/oeUmm*
    Z[Ymle l(sUU9v^[Y j ⬫ r8RӋPeԲD^X⟲p0GK*&w%Nm ^͛Ŀ=itTU^Be\0aTIBի%UbNmY$! tzTRf#mQTn7۶>.QGQd񴱵gO{PQѶk.Z ?}W~w./L{Prs!8Z*V؀ԓYdnZ]D֍zȦdxѩ*0
    =J2? Lϕn"`^01BUPA h+AӺk{}̾$b:Pzvn.-"wΉn_7
    ǞI>G:[z`nn#x*Ƚ<V%s;ogߕ?&z
    g*,g'c&uFɸh^Z3>R'xEÍ52y* f[TE-x *7Ү8ٵq/:r9Gc9Pᒲ(+_o8
    b*2:}tICŦ}]drޜ}gK:dM:h9*Y0Ih߱(&Hd
    oY>4-_ST񩟞0Zt`ՕS{Jcfʓ3*g_}Lr%Bijp{9Z{E%%5֓ab8Os;ut韂0lZV+U[;_?v5ri&\0E6a͟T
     \0_x\0Uf0@*>BV*i a2k&6*#/ )1d9D>:S
    `*)x! lTdA yi ud#S&rdzj#M5Dj}hJxnpE2¦E M𻀦:Lx~#aW'~tB]#Ut3 >,~c;9\VSaBK7Q|IFҔ)^* \ta*\d:쁔;40wCjHUԡnMZV\0XN5*)VЙ\0s͘VH@0~:?-ӹi;ΞVLe\y=\0.jT ZEP,V Z.؋|Zt+XL3?)W\0z. Cpχ;ilRܙ[*;zR'/*'s؀KTaA24h3כf㇁xh:i'Mc%ks*d݁7ϝCaIϥ%dG2ۂ/iH3H&I$*L%P&IOUM"E sᰔLgD*N"}}
    y\>f$'x2@lE ig]SL'c&_GHI+ D>v608Y;&I{2>:,GX.t18m!Q>seOly#3Xv߳ٸVꫲ;/_?_q]{s*5۽;.[vyWg =+oָ[G<9_Er7-]v(u檆Ç?Ocmoݓe>BʀjxS!F[pU:mw?{9tSG~m!99޿\0?.&VS;k+ɕ]2Ōx_
    ܶ@M Y׆7G"ʬc@^5_mI({qk(NOc>^h!7ݮ|~4{ŃBDWd_33e*b K1jW~o
    O㐯409n!3:hggRZ E>b>-M]q|?OA*Ua)-Ƣ1ҥ+!~ދ+}n`nkٹqPPG'3Ό媰\fTaYRa
    30
    30
    Qa9*̬*,O'8!/
    \F/~z\#DZMg8d<r߿RMj}A+{|g\0E8ɰ?1kF`#v*A{HܪbR1)9Wʛ*7K{ݘ]LSpl-M)iZOYFa(B9%ڳܡs hHs ABg)o\ȱ
    X[OMyd l"?1EᱜKMfxqr7y@ C_?滮BH$~%d b|Ff[Rgt9^@2aeA.
    ׾=ܲ*-m\0^n CMMJ0RBO;%AyfN#>*W_ 6{v~ -M! t#Gc<A9Iaei\ZKMya\ߵA` *")2$t1+م>B#oz//cy/~S+N&!">$b`d=C,>r-ߑP51iZVi7!`x.$1ⴏhtoNfOW/t>JDf y1f}e>je0|ܗ#|AV4@،߸;x*rRmmC_rCu[q<>¦Rc_HRQ!D8@)e_`MmV{j(
    udA\0,

    u2Y0g(^4Dfˬ,@Cf!^{tY]ha'v[],BźH]"K ׍ˬL;W[&q)8Zt"#SUt10iq$%L–j޲P
    QbM1I+gTX&$ڑL60V6*2*m*PJPG"v.e[HWP<xp
    ;]*O2'|%x)X x*8Tu0nZo7`7fZ`bb\0m+8B[I'ajJ֯j+ﴟ^{V$Xm/[/UG&{^mD?(SmbҰP
    > ixͥ)ԸB%s*ad^.qF]Z,sI [TLj@Nv*CT6٬,l*\4]a/[H_y=
    K)<}&7T"q̱%[^'VcQy}/79TY+:_ӵ=*qZRd+nq*Ԣlb_WJG0x8CGg ZRI) EX&5*(8w*p N(KTX93~VXd=`B6)*Sv',dPǸgM>U7r YC~oHl-: s.4B;3!7;})}b 720L`+և| n~)^Ents)MZ4Omg ?zBYY"Aa1cyg64HcA6%! ^9mc_86TS6
    ?՞8qF=>^6psPf+X*LB>-Ќ*p/ޅzrb!a $wedCO<jv)wQ +
    Q\F +`Rf
    6;1IGO|Wg*KN#B><HH`'tVB,a *G1 $$&؃"}l:Eg H]`h#b:6D7ՆhTZٲGS/M'43_ 0Y'D9j1ŸtVaa ess seL=uf 097ҏr69
    q鴿hG*
    5,mrI:t~VewUv{0\
    {=U6oj~gV?j-ʷZ*;*J8\v*+/a +t7(sl&tB#{*Ibq8l:ձh]*?QbEO_vuaP\Zt+oPBiYDD;qȰ924z橮H惛Fu #.aPͦik ՌpG!I.\S5+OU<lYW\pHa}_"a3>Sb_)k6\s <s&ޡ"# zb9C1v.YpP߄j!76
    Fg@
    O_ꖜ`N<_ttQM^VYYjHi#W2 oP!]G\0? |$"u]CU8Y~ }7tׄv9_(_r=tA'>Ql5t=A7^

    =}`z嫢r;mU:
    [*OVВ.]/ X}}kة"OP<1to]*)dAjLD+^a|vWZ6Jh}N'<6 x8?PdEu0D>ܲO#Å\r0x
    /ผW2 C}Ytۉ:0p#dNzQ@R*z&lDvoƮɖ
    v⅏6a8@¾
     L.,WvvBP4IK`g;ʣG]-D߇Bj6Z
    t[gG7ҏ_9]!'
    ̔s >"lԌ2YAplh-O&A2B !x&[Ջ$U6 Ճ085Aݍ8a~UɧElO&@U]N@g9)
    Liq+<˾Ϥ/}{M6tW0}%nH<ѥP* &}9u)/TfXEL*wau+q^mYߪl)[C뻔Ɛp<j`b UDCc%9+
    ׏a !$:NLyY7Gzct,G?4Dz(ngm:K(h @K-:~?}~ϛJS bf[݆>՞IPK \0\0\0\0x0:􋂀\0\0\0Z\0\0\0\0\0\0\0\0\0\0 \0\0\0\0\0\0\0xld.dynamic_dropdowns_v1.2.xlsPK\0\0\0\0\0\0L\0\0\0\0\0\0\0

  9. #9
    lgbjr
    Guest

    Re: comboboxes

    Hi All,

    thanks to Bob and Peter, I have a copy of the xldynamic page with the
    information I was looking for, and I have the sample workbook.

    The sample workbook contains two formats for accomplishing what I am looking
    for. The first, which uses combobox controls works fine in the sample and in
    a test workbook that I created.

    The second, which is what I would prefer to use, uses the data validation
    functionality in Excel. I receive a '1004' error in the sample and in a test
    workbook when trying to change the value in the first combobox:

    Method 'Range' of object '_Worksheet' failed

    In the code, the following line is highlighted:

    If Not Intersect(Range(kList1), Target) Is Nothing Then

    I imagine this must be something to do with my computer setup. I'm running
    Excel 2003 under WinXP w/ SP2. I'm assuming that this shouldn't matter to
    VBA, but I also have the .NET 2.0 Framework installed for testing VS 2005.

    Is there a particular add-in or reference that I should be using in order to
    execute this code? I'm using what I think are the standard references in
    Excel (VBA), which are VBA, Excel 11.0 Object Library, OLE Automation,
    Office 11.0 Object Library, Calander Control 11.0, and Forms 2.0 Object
    Library.

    do I need anything else?

    TIA,
    Lee

    ""Peter Huang" [MSFT]" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi
    >
    > If so here I post the website content here and attach the sample for your
    > reference.
    >
    > NOTE: the content below is quoted from the website
    > http://www.xldynamic.com/source/xld.Dropdowns.html
    >
    > The best Excel learning site on the web Another great site with lots of
    > downloadable material This one has a different style And now back to the
    > original style after our little excursion with another xlDynamic.com
    > Excel & VBA Tips
    > General | Using Excel | Worksheet Formulae | VBA Development | Tools &
    > Utilities | Miscellaneous
    > Home Page
    >
    > Sitemap
    >
    > Contact xld
    >
    > Contributions
    >
    > Terms of Use
    >
    > Disclaimer
    > Menu Page
    >
    > Techniques
    > Coloured Cells
    >
    > Conditional Formatting
    >
    > Rounding In Excel
    >
    > Last Value In Range
    >
    > Utilities
    > Reference
    > Special Characters
    >
    > Excel Names
    > Menu Page
    >
    > Techniques
    > RANK
    >
    > Utilities
    > Reference
    > SUMPRODUCT
    > Menu Page
    >
    > Techniques
    > Automation?
    >
    > Comon Dialog?
    >
    > Conditional Deleting?
    >
    > Test File Open
    >
    > Utilities
    > Dependent Dropdowns
    >
    > Document Property
    >
    > ReferenceMenu Page
    >
    > Techniques
    > Utilities
    > CFPlus
    >
    > Dependent Dropdowns
    >
    > League Table
    >
    > QDE
    >
    > ReferenceMenu Page
    >
    > Reference
    > FAQs
    >
    > Book List
    >
    > Downloads
    >
    > Glossary
    >
    > Web Sites
    > Excel Sites?
    >
    > Back Issues
    > EEE back issues?
    > ADO/ADOX
    >
    > Read Excel
    >
    > Write Excel
    >
    > Worksheet Names
    > Early v Late Binding
    > Win32 APIs
    > Common Dialog - Overview
    >
    > CDT - Goto Sheet
    >
    > CDT - Hide Sheets
    >
    > CDT - Sort Sheet(s)
    > FileSystemObject
    > Conditional Deleting
    >
    > Conditional Deleting v1
    >
    > Conditional Deleting v2
    >
    > Conditional Deleting v3
    > Chip Pearson
    >
    > John Walkenbach
    >
    > Dave McRitchie
    >
    > Ole Erlandsen
    >
    > Alan Beban's Array UDFs
    > Issue 19
    >
    > Issue 18
    >
    > Issue 17
    >
    > Issue 16
    > Main > VBA Development > Dependent Dropdowns
    >
    >
    > Dynamic Dependent Dropdowns
    >
    > This technique shows how to create dynamic dropdown lists in VBA. Dynamic
    > dropdowns in this context refers to a dynamically changing secondary list,
    > that changes dependent upon a selection from a primary list. An example is
    > a primary list of continents, and a secondary list showing the countries
    > associated with the selected continent.
    >
    > Below is an example of dynamic dropdowns, to demonstrate the technique, As
    > this is a web page, this is a JavaScript solution, but the VBA solution
    > works in a similar way.
    >
    > Continent Country City
    > America Europe Africa Far East Indian Sub-Continent [nothing selected]
    > [nothing selected]
    >
    > This page demonstrates how such a technique might be implemented. In
    > addition, at the end of the article, you can download an example workbook
    > that provides all of the code and data discussed here. The workbook is a
    > fully working example that can be used for your own projects, all that is
    > needed is to change the tables/lists on the 'Data' worksheet to your own
    > data.
    >
    > In this discussion two separate implementations are provided, one using
    > comboboxes as the dropdowns, and one using Data Validation lists as the
    > dropdowns.The example workbook shows both implementations.
    >
    > The article is segmented into the following sections:
    >
    > Sample Data
    > Naming The Ranges
    > Combobox Version
    > Data Validation Version
    > Adding Further Dropsdowns
    > Download
    > Sample Data
    > The first step is to create a set of tables/lists similar to the table
    > below. This is a truncated version of the tables in the example workbook.
    >
    >
    > Figure 1.
    >
    >
    > Naming The Ranges
    > These tables are used in the code to poulate the dropdowns. The values in
    > row 1, from column B on, are the groups that are used to populate the
    > primary dropdown. This set of values is given a workbook name of
    > 'List1Values'. The values in row 2 to row n for each of the populated
    > columns are defined as workbook named ranges which are used by the VBA
    > code
    > to populate the secondary dropdown, based upon the value chosen in the
    > primary dropdown. Each column of values is given a separate workbook named
    > range, and because they are used in the VBA code, these secondary lists
    > have to be named in a very structured manner. In this code, I am using
    > named ranges of 'List2_1', 'List2_2', etc.
    >
    > In practice, the VBA code should take care of creating the named ranges,
    > simply by examining the data, and defining the named ranges accordingly.
    > This allows further secondary lists to be added without any other user
    > action, and/or extra items or changes in any of the secondary lists. The
    > code that creates the named ranges is shown here:-
    >
    > Public Const kApp As String = "Dynamic Dropdowns"
    >
    > Public Const kList1Hnd As String = "List1Values"
    > Public Const kList2Hnd As String = "List2_"
    >
    > '---------------------------------------------------------------------
    > Public Sub pzLoadList2Lists()
    > '---------------------------------------------------------------------
    > Dim oWsData As Worksheet
    > Dim cRows As Long, cCols As Long, i As Long, j As Long
    >
    > Application.EnableEvents = False
    > Application.Calculation = xlCalculationManual
    > On Error GoTo load_exit
    >
    > Set oWsData = data
    > With oWsData
    > 'create dynamic range names for List1 and List2 lists
    > cCols = .Cells(1, Columns.Count).End(xlToLeft).Column
    > For i = 2 To cCols
    > cRows = .Cells(Rows.Count, i).End(xlUp).Row
    > ThisWorkbook.Names.Add Name:=kList2Hnd & i - 1, _
    > RefersToR1C1:="='" & data.Name & _
    > "'!R2C" & i & ":R" & cRows & "C" & i
    > Next i
    > End With
    > ThisWorkbook.Names.Add Name:=kList1Hnd, _
    > RefersToR1C1:="='" & data.Name & "'!R1C2:R1C" &
    > cCols
    >
    > load_exit:
    > Application.Calculation = xlCalculationAutomatic
    > Application.EnableEvents = True
    >
    > End Sub
    > This code should be called from the Workbook_Open event, in order to
    > initially populate the comboboxes upon opening the workbook, and also
    > whenever any change is made to the data (in that worksheet's
    > Worksheet_Change event).
    >
    > The other item that needs to be setup is the codename for the worksheets.
    > This is necessary as the worksheet is referred to in the VBA code by its
    > codename, in case the worksheet name is changed by a user. This code
    > assumes that that 'Data' worksheet has a codename of 'data', that the
    > combobox example worksheet has a codename of 'combo', and that the Data
    > Validation example worksheet has a codename of 'dv'.
    >
    >
    >
    > Combobox Version
    > So, how does it all work? As mentioned above, the named ranges are defined
    > by VBA code called whenever the workbok is opened, and re-defined whenever
    > data on that worksheet changes. Another worksheet is required that will
    > hold the dropdowns that will have the dynamic functionality. In my
    > example,
    > I called this worksheet 'Combo Example', and added two control toolbox
    > comboboxes to it, which are named as 'cboPrimary' and 'cboSecondary'.
    >
    > The secondary combobox is loaded as part of the primary combobox change
    > event, that is, selecting a value from the primary combobox triggers the
    > following code:
    >
    > '---------------------------------------------------------------------
    > Private Sub cboPrimary_Change()
    > '---------------------------------------------------------------------
    > Dim idx As Long
    > Dim iTargetCol As Long
    > Dim oFoundCell As Range
    >
    > With data.Range("List1Values")
    > Set oFoundCell = .Find(what:=cboPrimary.Value, _
    > LookIn:=xlValues)
    > If oFoundCell Is Nothing Then
    > MsgBox "Critical error", vbCritical, "(xld) Dynamic DropDowns"
    > Exit Sub
    > End If
    > End With
    >
    > 'load the List2 dropdown and set the default to item 1
    > iTargetCol = oFoundCell.Column - 1
    > pzPopulateList2 iTargetCol
    >
    > End Sub
    > In this code, the range 'List1Values' is searched for the value selected
    > in the primary combobox. This search returns the ordinal number within
    > those values, which in turn is passed as the secondary list range index to
    > the pzPopulateList2 procedure to re-populate the secondary combobox.
    >
    > The final piece of the jigsaw for this combobox solution is the code for
    > populating the primary and secondary comboboxes. The following code shows
    > how this is achieved.
    >
    > '---------------------------------------------------------------------
    > Public Function pzPopulateList1()
    > '---------------------------------------------------------------------
    > Dim i As Long
    >
    > Application.EnableEvents = False
    > On Error GoTo pl1_exit
    >
    > With combo.cboPrimary
    > .Clear
    > For i = 2 To Range(kList1Hnd).Count + 1
    > .AddItem data.Cells(1, i).Value
    > Next i
    >
    > Application.EnableEvents = True
    > .ListIndex = 0
    > End With
    >
    > pl1_exit:
    > Application.EnableEvents = True
    >
    > End Function
    >
    > '---------------------------------------------------------------------
    > Public Function pzPopulateList2(idx As Long)
    > '---------------------------------------------------------------------
    > Dim i As Long
    > Dim sList As String
    >
    > Application.EnableEvents = False
    > On Error GoTo pl2_exit
    >
    > sList = "=" & kList2Hnd & CStr(idx)
    >
    > With combo.cboSecondary
    > .Clear
    > For i = 1 To Range(sList).Count
    > .AddItem Range(sList).Cells(i, 1).Value
    > Next i
    >
    > Application.EnableEvents = True
    > .ListIndex = 0
    > End With
    >
    > pl2_exit:
    > Application.EnableEvents = True
    > End Function
    > The procedure pzPopulateList1 takes each of the items in the named range
    > 'List1Values', and loads them into the primary combobox.
    >
    > The pzPopulateList2 procedure is just a little more complex, in that it is
    > passed an index argument. This index refers to the ordinal number of the
    > item selected in the primary combobox, and is used to determine which
    > secondary list will be loaded into the secondary combobox. Using the named
    > range structure mentioned earlier, this index is used to identify which
    > named range is loaded into the secondary combobox.
    >
    >
    >
    > Data Validation Version
    > The Data Validation version uses very similar techniques to the Combobox
    > version , but the population code is specific to Data validation. In this
    > implementation, two cells are used that utilise the Data Validation
    > functionality, specifically Data Validation using custom lists. These two
    > cells cover the primary data list, and the secondary, related, data list.
    >
    > This implementation utilises the built-in Excel Data Validation
    > functionality, and simply sets the function up with the correct lists.
    > Again, the named ranges are used in the VBA code to facilitate the
    > loading,
    > for two reasons. Firstly, I find named ranges very flexible and powerful,
    > and secondly and most importantly, Data Validation can only refer to
    > ranges
    > on another worksheet if named ranges are used. The same VBA code that
    > automatically names the ranges in the combobox implementation is used for
    > the Data Validation implementation.
    >
    > As already mentioned, the Data Validation implementation works in a
    > similar
    > manner to the combobox implementation in that both Data Validation cells
    > need to be initially populated when the workbook is opened. This is the
    > code in the Workbook_Open event that performs that function
    >
    > '---------------------------------------------------------------------
    > Private Sub Workbook_Open()
    > '---------------------------------------------------------------------
    > Dim cell As Range
    >
    > pzLoadList2Lists
    >
    > Application.DisplayAlerts = False
    >
    > 'this poulates the Data Validation lists
    > Set cell = dv.Range(kList1)
    > fzCreateValidationList1 cell
    > fzCreateValidationList2 cell.Offset(1, 0), 1, cell
    >
    > Application.DisplayAlerts = True
    >
    > End Sub
    > The primary and secondary Data Validation cells also need to be
    > re-populated if there is any change to the data tables/lists on the 'Data'
    > worksheet. Again, this is achieved using Worksheet_Change event code,
    > which
    > is shown below
    >
    > '---------------------------------------------------------------------
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > '---------------------------------------------------------------------
    > Dim oFoundCell As Range
    > Dim iTargetCol As Long
    >
    > If Not Intersect(Range(kList1), Target) Is Nothing Then
    > If Target.Count = 1 Then
    >
    > With data.Range(kList1Hnd)
    > Set oFoundCell = .Find(what:=Target.Value, _
    > LookIn:=xlValues)
    > If oFoundCell Is Nothing Then
    > MsgBox "Critical error"
    > Exit Sub
    > End If
    > End With
    >
    > 'load the List2 dropdown and set the default to item 1
    > iTargetCol = oFoundCell.Column - 1
    > fzCreateValidationList2 Target.Offset(1, 0), iTargetCol, Target
    > Target.Offset(1, 0).Value = data.Range(kList2Hnd &
    > iTargetCol).Value
    > End If
    >
    > End If
    >
    > End Sub
    > And finally, here is the code that actually sets up the Data Validation
    > in the two cells. The approach taken here is to delete any exising data
    > validatioon already defined to that cell, and then re-create anew based
    > upon the actual data in the tables/lists. The primary Data Validation cell
    > is quite simple as it just assigns the named range 'List1Values' to the
    > Data Validation set up in that cell. The secondary Data Validation is just
    > a little more complex as it is passed the primary list index as an
    > arguement which is used to construct the name of the range to be assigned,
    > based upon the value 'List2_' and the index, and the error message
    > reflects
    > the value selected from the primary Data Validation cell.
    >
    > '---------------------------------------------------------------------
    > Public Function fzCreateValidationList1(Target As Range)
    > '---------------------------------------------------------------------
    > With Target.Validation
    > .Delete
    > .Add Type:=xlValidateList, _
    > AlertStyle:=xlValidAlertWarning, _
    > Formula1:="=" & kList1Hnd
    > .InCellDropdown = True
    > .InputTitle = kApp
    > .ErrorTitle = kApp & " - Error"
    > .InputMessage = ""
    > .ErrorMessage = "This is not a valid Value"
    > End With
    > End Function
    >
    > '---------------------------------------------------------------------
    > Public Function fzCreateValidationList2(Target As Range, _
    > idx As Long, _
    > source As Range)
    > '---------------------------------------------------------------------
    > With Target.Validation
    > .Delete
    > .Add Type:=xlValidateList, _
    > AlertStyle:=xlValidAlertWarning, _
    > Formula1:="=" & kList2Hnd & idx
    > .InCellDropdown = True
    > .InputTitle = kApp
    > .ErrorTitle = kApp & " - Error"
    > .InputMessage = ""
    > .ErrorMessage = "This is not a valid Value for " & source.Value
    > End With
    > End Function
    > There is just one other thing to note regarding Data Validation. The
    > example given above works perfectly well in Excell 2000 and on, but if you
    > have Excel 97, you will find that changing the value in the primary cell
    > does not cause an update of the list associated with the secondary cell.
    > This is because a change to a data validation cell does not fire the
    > Worksheet_Change event in Excel 97. The solution to this is to use another
    > event. I have used the Worksheet_Calculate event, and to trigger it I have
    > referenced the primary Data Validation cell in an other cell (in which I
    > have set the font colour to white, to hide it), with a simple =List1.
    > Thus,
    > when a change is made to the primary Data Validation cell, this changes
    > the
    > linked cell, which in turn triggers the Worksheet_Calculate event. The
    > example workbook uses this technique, so it works with Excel97 on.
    >
    >
    >
    > Ading Further Dropdowns
    > The code presented is fully-inclusive, and can handle any number of
    > dropdowns (although the more you have, the slower it is bound to become,
    > and the lists could become unmanageable). If you want to add further
    > dropdowns, this is an outline of the process:
    >
    > Open the workbook with macros disabled
    > Add a fourth control toolbox combobox and name it cboList4
    > On the data sheet add a fourth column labelled List4
    > Add data in column 4, inserting rows for repetition of Lists3, 2 and 1
    > Close the workbook
    > Open it with macros enabled
    >
    > Try it with a few values at the top to start with, it is straight-forward.
    >
    >
    >
    > Download
    > 2179
    > Click here to download an example workbook.
    >
    >
    >
    >
    > ----------------------------------------------------------------------------
    > ----
    > Copyright ?2000-2004 xlDynamic.com
    > Page last updated: 22nd December 2003
    > Found an error, a bug or just want to
    > comment on this page, please tell us
    > Copyright ?2000-2004 xlDynamic.com Home
    > 24742
    >
    >
    > Best regards,
    >
    > Peter Huang
    > Microsoft Online Partner Support
    >
    > Get Secure! - www.microsoft.com/security
    > This posting is provided "AS IS" with no warranties, and confers no
    > rights.




  10. #10
    Peter Huang [MSFT]
    Guest

    Re: comboboxes

    Hi

    I think it has nothing to do with the .NET Framework 2.0.
    But I think you may try to create a new workbook and copy the code and data
    into the new workbook to see if that works.
    BTW: based on my test, the code will run on my side.

    Best regards,

    Peter Huang
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    This posting is provided "AS IS" with no warranties, and confers no rights.


  11. #11
    Yan-Hong Huang[MSFT]
    Guest

    Re: comboboxes

    Hi Lee (),

    Yes, please test as Peter mentioned. This problem seems like machine
    specific. The code runs fine on Peter's machine. If you have any more
    concerns, please feel free to reply here.

    Thanks very much.

    Best regards,
    Yanhong Huang
    Microsoft Community Support

    Get Secure! C www.microsoft.com/security
    Register to Access MSDN Managed Newsgroups!
    -http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.as
    p&SD=msdn

    This posting is provided "AS IS" with no warranties, and confers no rights.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1