UserForm with dependant combobox with several bound columns

Semua Balasan

• 09 Mei 2012 5:55

  Moderator

   

   
  

  0

  Hi,

  I have changed you code, copy the following code to check whether it is what you want.

  Private Sub UserForm_Initialize()

    CboArea.List() = [AreaList].Value

  '  CboSpecies.List() = [AreaSpecies].Value

  '   CboSpecies.BoundColumn = 2

  '   CboSpecies.ColumnCount = 2

  '   CboSpecies.ColumnWidths = "0 cm; 2 cm"

   End Sub

   

  Private Sub CboArea_Change()

     Dim arrFilter() As Variant

     Dim arrInp() As Variant

     Dim i As Long, j As Long

     With [AreaSpecies]

       ReDim arrInp(.Rows.Count, 2)

       arrInp = .Value

     End With

   

    j = 1

     For i = 1 To UBound(arrInp, 1)

       If arrInp(i, 1) = CboArea.Value Then

         j = j + 1

       End If

     Next i

     ReDim arrFilter(j - 1, 2)

    

     j = 1

     For i = 1 To UBound(arrInp, 1)

       If arrInp(i, 1) = CboArea.Value Then

         arrFilter(j, 1) = arrInp(i, 1)

         arrFilter(j, 2) = arrInp(i, 2)

         j = j + 1

       End If

     Next i

    

     Dim temp_array() As Variant

    

     Dim n As Integer

      ReDim temp_array(j - 1)

     For n = 1 To j - 1

      temp_array(n) = arrFilter(n, 2)

     Next

     CboSpecies.List() = temp_array

  '    CboSpecies.List = arrFilter()

  '   CboSpecies.BoundColumn = 1

  '   CboSpecies.ColumnCount = 2

  '   CboSpecies.ColumnWidths = "0 cm; 2 cm"

     

     End Sub

   

  Private Sub CboSpecies_Change()

       TxtFamily.Value = CStr(Sheets("MasterList").Range("C" & CboSpecies.ListIndex + 2))

      TxtCommonName.Value = CStr(Sheets("MasterList").Range("D" & CboSpecies.ListIndex + 2))

      TxtGrid.Value = CStr(Sheets("MasterList").Range("E" & CboSpecies.ListIndex + 2))

      TxtComments.Value = CStr(Sheets("MasterList").Range("F" & CboSpecies.ListIndex + 2))

   End Sub

  ---

  Jaynet Zhang

  TechNet Community Support

  

  • Balas
  • Kutip

   
• 09 Mei 2012 12:28

   

   
  

  0

  Hi Jaynet,

  THanks for your help. I pasted your code into my Excel file, however the same problem still exists. ie. I can select the area from the CboArea combobox OK, and can select the species from the CboSpecies combobox OK. However the contents of the 4 textboxes seem to be locked into returning the adjacent values for species in the first Area, ie. If you pick the first 8 species in Area 1, the adjacent 4 columns are returned correctly in the 4 textboxes, however if you pick the first 8 species in another Area, the corresponding values in the 4 textboxes are not correct and instead are the same as for the first Area! I'm assuming the problem is somewhere in the Subroutine CboSpecies_Change.

  A couple of things occurred to me that may affect the result:

  1. My list of Areas ("AreaList", a single column array) is in a different worksheet to my "AreaSpecies" (a 2 column array), which is part of the Master List (6 column database) which has a variable number of records (because I add or delete records). Would it make any difference having the AreaList array on the same worksheet as AreaSpecies?

  2.Should my Master List be filtered or left as a simple table?

  3. When I name my arrays, should I include the whole column or just the non-blank records? I really need the arrays to be dynamic, ie being able to change in size.

  I've no idea why it is doing this!!

  Any further help gratefully received!

  • Balas
  • Kutip

   
• 11 Mei 2012 7:26

  Moderator

   

   
  

  0

  Hi,

  I didn’t meet the issue using my code. So I think maybe there are some difference between the structure of the database or userform.

  So, would you please show us your database and the userform to make the issue clearly?

  ---

  Jaynet Zhang

  TechNet Community Support

  

  • Balas
  • Kutip

   
• 11 Mei 2012 23:41

   

   
  

  0

  Hi Jaynet,

  Do you mean you want me to send the whole Excel file, or just explain the issue more clearly?. I am happy to send the whole file, but would prefer not to have it available for general viewing. Do you have a secure email address that I could send it to?

  I will also try to explain more clearly what my database and userform look like:

  1. I am working with Excel 2010

  2. I have a macro-enable file with 2 worksheets, called "MasterList" and "PlantsOfInterest".

  3. "MasterList" is the database. It consists of 6 columns, and currently has 3356 records. The columns are titled "Area" (there are 4 possible areas), "Species" (about 3,000 species - some species occur in more than 1 area), "Family", "Common Name", "Grid" (location) and "Comments".

  4. "PlantsOfInterest" sheet is for the user to select a record from the MasterList, using the UserForm, and add to a list called "Plants of Interest". The Userform has comboxes for Area and Species (Species is dependant on Area) and text boxes for Family, Common Name, Grid and Comments. The UserForm also has button to send the values in each control of the selected record to the Plants of Interest List (on the same worksheet - this works with no problems).

  5. On the "PlantsOfInterest" worksheet I have a named range called "AreaList" which is a list of the 4 Areas. I thought it would be simpler to have this list away from the main database as it is set and not dynamic perhaps this is wrong?)

  6. On the "MasterList" sheet , I have a 2-column named range called "AreaSpecies" which includes the Area and Species columns - each combination of which is unique. The corresponding rows of the othe columns ("Family", Common Name", "Grid" and "Comments") are also unique to the Area & Species shown in the first 2 columns.

  7. I am not sure if I have "MasterList" set up properly. I want to be able to edit MasterList, ie add or delete records. So the named range "AreaSpecies" needs to be dynamic. Should I have this database set up as a table rather than a range, and/or, should I have a simple filter turned on in the "MasterList" sheet  before I use the UserForm?

  Is this any help? Let me know if you want me to send the whole file to you

  Charles

  • Balas
  • Kutip

   
• 13 Mei 2012 11:32

   

   
  

  0

  Jaynet, I think I figured out what is going wrong, but I still don't know how to fix it

  In the following Sub, each line ends in "CboSpecies.ListIndex + 2", which basically starts the list at the second row from the top, rather than from the row corresponding to the active cell, ie the species value selected using the dependant combobox CboSpecies. For example, if Area2 is selected, records for this area are in rows 754-1521, so I think the code needs to set the listIndex according to what Area is picked. However as  I say, I don't know what code to use to fix this up

  Private Sub CboSpecies_Change()

  RecordNumber = CboSpecies.Value
      TxtFamily.Value = CStr(Sheets("MasterList").Range("C" & CboSpecies.ListIndex + 2))
      TxtCommonName.Value = CStr(Sheets("MasterList").Range("D" & CboSpecies.ListIndex + 2))
      TxtGrid.Value = CStr(Sheets("MasterList").Range("E" & CboSpecies.ListIndex + 2))
      TxtComments.Value = CStr(Sheets("MasterList").Range("F" & CboSpecies.ListIndex + 2))

  • Balas
  • Kutip

   
• 14 Mei 2012 8:50

  Moderator

   

   
  

  0

  Hi,

  Do you mean if “Area2” is selected, records for this area are in rows 754-1521, then copy these records into “PlantsOfInterest” sheet? And the position of the sheet is the same as MasterList, 754-1521?

  ---

  Jaynet Zhang

  TechNet Community Support

  

  • Balas
  • Kutip

   
• 14 Mei 2012 9:38

   

   
  

  0

  No,

  what I mean is that if Area2 is selected, then the dependant combobox CboSpecies is populated only with species from Area2. The user then selects a species from area2 from the dependant combobox. All I was saying was that the records for Area 2 are in rows 754-1521. The records for Area1 are in rows 2-1520 (row 1 has the headers). What is copied to the Plants of Interest sheet is the record that has just been selected using the UserForm.

  • Balas
  • Kutip

   
• 15 Mei 2012 23:39

   

   
  

  0

  Does anyone have an answer to this ??

  I can post the whole file, however I would like to have a secure location to send it to

  • Balas
  • Kutip