vba - 从用户窗体变量填充组合框

我在用户窗体中有两个简单的代码段，我想做什么，我相信这很简单，但是我被卡住了！

Private Sub UserForm_Initialize()

Dim wkb As Workbook

    With Me.CB_Excel_File
        For Each wkb In Application.Workbooks
            .AddItem wkb.Name
        Next wkb
    End With

End Sub

Private Sub CB_Excel_File_Change()

Dim wks As Worksheet

    With Me.CB_Worksheet
        For Each wks In ***Me.CB_Excel_File.Value.Worksheets***
            .AddItem wks.Name
        Next wks
    End With

End Sub

由于我想列出用户从Userform_Initialize代码中选择的工作簿中的所有工作表，因此我坚持使用***。

提前致谢！

最佳答案

您需要将选定的工作簿名称作为变量传递到Workbooks集合中，如下所示：

Private Sub CB_Excel_File_Change()

Dim wks As Worksheet

With Me.CB_Worksheet
    For Each wks In Workbooks(CB_Excel_File.Value).Worksheets
        .AddItem wks.Name
    Next wks
End With

End Sub