列表框选择以编辑工作表 [英] Listbox selection to edit worksheet

问题描述

此问题与其他问题相关:列表框中填充了特定行

This question refers to this other: Listbox populate with specifc rows

在这种情况下，列表框会动态填充工作表数据库的数据.

In that case the listbox is dynamically filled with data of a database of a worksheet.

问题是: 如何在列表框中选择一行(名称)并编辑其对工作表的引用?生病的解释.我想到了选择列表框的一行，然后选择组合框的一个项目并单击一个按钮进行确认的方法，以便组合框的项目可以填充工作表上的某个单元格.

The question is: How could I select a row (name) in the listbox and edit its reference of the worksheet? Ill explain. I thought of selecting a row of the listbox, then selecting an item of a combobox and confirm with a click on a buttom, so that the item of the combobox would fill a certain cell on the worksheet.

这是用户表单

userform2

推荐答案

您可以通过在用户窗体代码窗格中添加以下代码来实现:

you can go by adding to your userform code pane the following code:

Private Sub CommandButton1_Click()
    Dim Data() As Variant '<--| use an array to store data to eventually fill destination worksheet cells
    Dim destSht As Worksheet '<--| this will be set to the "destination" worksheet
    Dim i As Long

    With Me
        With .ComboBox1 '<--| refer to combobox (change "ComboBox1" with your actual name)
            If .ListIndex = -1 Then Exit Sub '<--| exit sub if no value selected
            Set destSht = Worksheets(.value) '<--| set the "destination" worksheet to the one with the name selected in Combobox
        End With

        With .ListBox1 '<--| refer to listbox (change "ListBox1" with your actual name)
            If .ListIndex = 0 Then Exit Sub '<--| exit sub if selected the listbox header row
            ReDim Data(1 To 1, 1 To .ColumnCount) '<--resize data array to match listbox columns number
            For i = 1 To .ColumnCount '<--| loop through listbox columns
                Data(1, i) = .List(.ListIndex, i - 1) '<--| fill data array with selected row data (Listbox.List is zero-based)
            Next i
        End With

        With destSht '<--| refer to "destination" sheet
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 5).value = Data '<--| paste listbox selected row values to selected worksheet after its column "A" last non blank cell
        End With
    End With
End Sub

您应该添加对目标"工作表设置的最小处理，以防止无效名称处理

you should add a minimal handling of the "destination" worksheet setting to prevent invalid names processing

这篇关于列表框选择以编辑工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！