保存组合框值

debugcn 发表于 Dev

价钱

我试图创建组合框值的简单用户窗体，然后允许用户添加新的项目和保存项目（在这个意义上，你键值到文本框和两个值Combobox1，并Combobox2关闭并重新打开后得到更新和遗体Excel文件）。这是我的代码：

Private Sub ComboBox1_Change()
End Sub

Private Sub ComboBox2_Change()
End Sub

Private Sub CommandButton1_Click()
    Me.ComboBox2.AddItem Me.TextBox1.Value
    Me.ComboBox1.AddItem Me.TextBox1.Value
    Me.TextBox1.Value = ""
    MsgBox "Category added to combobox!!"
End Sub

Private Sub UserForm_Activate()
    Me.ComboBox1.AddItem "Chicken"

    Dim sh As Worksheet
    Dim i As Integer
End Sub

'更新列表并保存Excel文件

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim strRowSource As String
Dim lReply As Long, lRows As Long
 If ComboBox1 <> vbNullString Then
 If ComboBox1.ListIndex < 0 Then
 lReply = MsgBox(ComboBox1 & _
 " is not part of the list. Add it", vbYesNo + vbQuestion)
 If lReply = vbYes Then
 With ComboBox1
 strRowSource = .RowSource
 lRows = Range(strRowSource).Rows.Count
 Range(strRowSource).Cells(lRows + 1, 1) = ComboBox1
 .RowSource = vbNullString
  .RowSource = Range(strRowSource).Resize(lRows + 1, 1).Address
 End With
 End If
 End If
 End If
End Sub

悉达思·劳特

建议

正如我在上面的评论中建议的那样，请勿使用该ComboBox1_Exit事件。使用UserForm_QueryClose事件。这样，您不必每次更改时都保存值。退出用户表单之前，请一口气进行操作。
保存工作簿，以便可以保留这些值。
使用文本框和命令按钮接受要添加到组合框的值。

逻辑

如果UserForm_Initialize发生这种情况，请从工作表中加载组合框。
让用户在文本框中输入值。使用命令按钮保存到组合框
在UserForm_QueryClose事件中保存到工作表。在将值放回列之前，请确保清除相关列。

样例代码

这是您要尝试的吗？

Option Explicit

Dim ws As Worksheet
Dim i As Long

'~~> Load values from the worksheet into the combobox
Private Sub UserForm_Initialize()
    '~~> Set this to the relevant sheet
    Set ws = Sheet1

    Dim lRow As Long

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow
            If Len(Trim(.Range("A" & i).Value)) <> 0 Then
                ComboBox1.AddItem .Range("A" & i).Value
            End If
        Next i
    End With
End Sub

'~~> Add item to combobox from textbox
Private Sub CommandButton1_Click()
    If Len(Trim(TextBox1.Text)) <> 0 Then
        ComboBox1.AddItem TextBox1.Text
    Else
        MsgBox "Nothing to add"
    End If
End Sub

'~~> Save to worksheet
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ws.Columns(1).ClearContents

    For i = 0 To ComboBox1.ListCount - 1
        ws.Cells(i + 1, 1).Value = ComboBox1.List(i)
    Next i

    ThisWorkbook.Save
    DoEvents
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。