如何填充组合框 [英] How to Populate a Combobox

问题描述

我对VBA还是很陌生，我一直在努力填充组合框。
我试图用电子表格中第一列的内容填充组合框，以便我可以根据组合框选择删除关联的数据行。

在提出这个问题时，我已经在这里和其他地方仔细研究了几个问题，但是我没有发现任何有效的方法。

• 
  

  I'm quite new to VBA and I've been struggling with populating a combobox. I'm trying to fill a combobox with the contents of the first column in a spreadsheet so I can delete the associated row of data based on the combobox selection.

  I've looked through several questions both here and elsewhere when making this question, but I haven't found anything that worked.

  • populate combobox in VBA with array elements
  • How do I populate a combo box from a column in my excel spread sheet?
  • http://www.techrepublic.com/blog/microsoft-office/populate-a-userform-combo-box-in-excel-with-a-dynamic-list/

  Below is the code I've tried. I'm somewhat lost as I've been trying to cobble together the different answers from other questions in order to get this to work, but to no avail. I expect the combobox to populate with the values from column 1, but it remains blank.

  Attempt #1 This involved creating a dynamic range:

  =OFFSET(PC_DataSheet!$A$2,0,0, COUNTA(PC_DataSheet!$A$1:$A$65536)-1,1)

  Private Sub UserForm1_Initialize()
  
      Dim rngPCNumber As Range
      Dim ws As Worksheet
  
      Set ws = Worksheets("Sheet1")
  
      For Each rngPCNumber In ws.Range("PCNumber")
          Me.PC_ListComboBox.AddItem rngPCNumber.Value
      Next rngPCNumber
  
  End Sub
  

  Attempt #2

  Private Sub UserForm1_Initialize()
  
      Dim arr() As Variant
  
      arr = Worksheets("Sheet1").Range("C2:" & lrow).Value
      PC_ListComboBox.List = arr
  
  End Sub
  

  Attempt #3

  Private Sub UserForm1_Initialize()
  
      Dim vArr As Variant
      Dim i As Integer
  
      vArr = Sheet1.Range("A:1").Value
  
      With PC_ListComboBox.Clear
           For i = LBound(vArr) To UBound(vArr)
              .AddItem vArr(i)
           Next i
      End With
  
  End Sub
  

  Any help on this would be really appreciated!

  ---

  EDIT: I've tried inserting the code suggested by Gary's Student into my UserForm_Initialize() Sub, but when I try to open the userform I get the following error message:

  Run-time error '9': Subscript out of range

  When I click debug, it highlights this code:

  'Opens PC UserForm when pressed.
  Private Sub AddPCButton_Click()
  
      UserForm.Show 'This line is the line highlighted by the debugger.
  
  End Sub
  

  I have no idea what's causing this...when I use the suggested code, I get an error message, but when I remove the code the userform functions flawlessly. Here's Private Sub UserForm_Initialize() with and without the suggested code.

  'Clears and Initializes the form when first loaded.
  Private Sub UserForm_Initialize()
  
      'Empties combo boxes.
      PC_OSTypeComboBox = ""
      PC_HDTypeComboBox = ""
  
      'Populates combo boxes.
      With PC_OSTypeComboBox
          .Clear
          .AddItem "Windows 8"
          .AddItem "Windows 7"
          .AddItem "Windows Vista"
          .AddItem "Windows XP"
          .AddItem "Windows 2000"
          .AddItem "Windows 98"
          .AddItem "Windows NT"
          .AddItem "Windows 95"
      End With
      With PC_HDTypeComboBox
          .Clear
          .AddItem "SATA"
          .AddItem "IDE"
          .AddItem "SCSI"
      End With
  
  End Sub
  

  This is including the suggested code:

  'Clears and Initializes the form when first loaded.
  Private Sub UserForm_Initialize()
  
      Dim N As Long, i As Long
      With Sheets("Sheet1")
          N = .Cells(Rows.Count, 1).End(xlUp).Row
      End With
  
      With PC_NumberComboBox
          .Clear
          For i = 1 To N
              .AddItem Sheets("Sheet1").Cells(i, 1).Value
          Next i
      End With
  
      'Empties combo boxes.
      PC_OSTypeComboBox = ""
      PC_HDTypeComboBox = ""
  
      'Populates combo boxes.
      With PC_OSTypeComboBox
          .Clear
          .AddItem "Windows 8"
          .AddItem "Windows 7"
          .AddItem "Windows Vista"
          .AddItem "Windows XP"
          .AddItem "Windows 2000"
          .AddItem "Windows 98"
          .AddItem "Windows NT"
          .AddItem "Windows 95"
      End With
      With PC_HDTypeComboBox
          .Clear
          .AddItem "SATA"
          .AddItem "IDE"
          .AddItem "SCSI"
      End With
  
  End Sub
  

  解决方案

  Here is a super simple example of creating and filling a Forms style combo-box:

  Sub FormsStyleComboBox()
      ActiveSheet.DropDowns.Add(411, 14.25, 124.5, 188.25).Select
      N = Cells(Rows.Count, "A").End(xlUp).Row
      strng = Range("A1:A" & N).Address
      Selection.ListFillRange = strng
  End Sub
  

  For example:
  
  

  EDIT#1

  I created a UserForm called Demo containing a combo-box called MyBox

  In a Standard Module I put:

  Sub DisplayIt()
      Demo.Show
  End Sub
  

  and in the UserForm code area:

  Private Sub UserForm_Initialize()
      Dim N As Long, i As Long
      With Sheets("Sheet1")
          N = .Cells(Rows.Count, 1).End(xlUp).Row
      End With
  
      With MyBox
          .Clear
          For i = 1 To N
              .AddItem Sheets("Sheet1").Cells(i, 1).Value
          Next i
      End With
  End Sub
  

  Running DisplayIt() produces:

  This is based on this tutorial

  这篇关于如何填充组合框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！