Geek Answers Handbook

How to Fill Combobox

I am new to VBA and I am struggling with filling out combobox. I am trying to populate a combobox with the contents of the first column in a spreadsheet so that I can delete the related row of data based on the selection of combobox.

I looked at several questions both here and elsewhere when I asked this question, but I did not find anything that worked.

Below is the code I tried. I'm a little lost since I tried to collect different answers from other questions to get this to work, but to no avail. I expect the combobox to populate with the values ​​from column 1, but it remains empty.

Attempt # 1 This involves 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 number 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 tried to insert the code suggested by Gary Student into my UserForm_Initialize () Sub, but when I try to open the user form, I get the following error message:

Runtime Error '9': Subscript

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

, ... , , , . Private Sub UserForm_Initialize() .

'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

:

'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
+4
2

:

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

:

enter image description here

# 1

UserForm Demo, MyBox

enter image description here

:

Sub DisplayIt()
    Demo.Show
End Sub

UserForm:

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

DisplayIt() :

enter image description here

+3

, , , , , , - " " , , no , , , , .

, . .

Private Sub DeletePCButton_Click()

'Assigns variables for delete sequence.
Dim PCNumberEntry As String
Dim Confirm As Integer
Dim r As Range
Dim c As Range
Dim cellsToDelete As Range
Dim m As Integer

'Asks for PC entry to be deleted.
PCNumberEntry = InputBox("Enter the number of the PC you wish to remove:", "Delete PC Entry", "PC 1", vbOKCancel)

'Closes inputbox when cancel is pressed.
If PCNumberEntry = "" Then
    Exit Sub
End If

'Searches worksheet column "A" and finds any existing PC entries.
Set r = Sheet1.Range("A:A")
For Each c In r

    'Checks for matching entry in worksheet to begin delete sequence.
    If (c.Value) = PCNumberEntry Then
        m = True

        'Asks for confirmation from user before deleting entry.
        Confirm = MsgBox("Warning! Once deleted, an entry cannot be restored! Only proceed if you are sure you wish to delete a row.", vbYesNo Or vbExclamation)

        'Cancels delete operation when "No" button is pressed.
        If Confirm = vbNo Then
            Exit Sub
        End If

        'Deletes entry and informs user of successful operation.
        If cellsToDelete Is Nothing Then
            Set cellsToDelete = c
            Call cellsToDelete.EntireRow.delete
            MsgBox ("The entry was deleted.")
        End If

    End If

Next c

    'Displays error message if no matching entry is found.
    If m = False Then
        MsgBox ("No entry with that number was found!")
    End If

On Error Resume Next

Sub

0

More articles:


All Articles