使用工作表中的数据填充列表框

发布于 2024-12-11 21:09:54 字数 527 浏览 0 评论 0原文

目前，当我运行代码时，我收到以下错误（如下所示）。

Type Mismatch

为了提供一些上下文，工作表 CourseSelection 的第 3 行从 A 到 F 填充。我想将 A2:A6 中的条目放入列表框中。但是，我想概括此过程，并使其动态地包含在 F 列之后添加的其他类别。因此，我需要一种自动方法来通过类似于下面的代码来执行此操作。但是，我收到错误消息，但不确定原因。

我在此代码之前将 TaskList 定义为 Range。当我运行代码时将鼠标悬停在 xlToRight 上时，我看到一个非常大的负值 (-4191)。我不确定这是否是问题的一部分。

With Worksheets(CourseSelection).Range("A3")
    Set TaskList = Range(.Offset(0, 1), .End(xlToRight))
End With

frmTaskSelection.lbTasks.RowSource = TaskList

原文

I currently get the following error when I run the code (shown below)

Type Mismatch

To give a bit of context, the worksheet CourseSelection has row 3 populated from A to F. I would like to put the entries from A2:A6 into a listbox. However, I want to generalize this process and make it dynamic to include additional categories if they are added after column F. Therefore I need an automatic way to do this through code similar to what I have below. However, I am getting error messages and I am unsure why.

I defined TaskList as a Range prior to this code. When I hover over xlToRight when I run the code I see a very large negative value (-4191). I am unsure if this is part of the problem.

With Worksheets(CourseSelection).Range("A3")
    Set TaskList = Range(.Offset(0, 1), .End(xlToRight))
End With

frmTaskSelection.lbTasks.RowSource = TaskList

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

热风软妹 2024-12-18 21:09:54

除非您将 CourseSelection 定义为返回现有工作表名称的常量，否则代码将在 With Worksheets(CourseSelection).Range("A3") 上失败。如果您想使用工作表名称 CourseSelection，则可以使用 With Worksheets("CourseSelection").Range("A3")。

鉴于您的错误消息，尽管您似乎已经过了这一点，并且您的代码似乎在 frmTaskSelection.lbTasks.RowSource = TaskList 上失败。这是因为 RowSource 需要地址。

如果您希望将名为 CourseSelection 的工作表中的值从 A3 填充到 Ax，其中 x 是最后使用的单元格，则此代码将在任何活动工作表中运行。

请注意，我不清楚除 A2:A6 之外您还想如何使用 F 列中的其他值。如果您可以提供进一步的指导/图片等，那么可以调整下面的代码以适应

Sub test()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = Worksheets("CourseSelection")
    Set rng1 = ws.Range(ws.[a3], ws.Cells(Rows.Count, "A").End(xlUp))
    frmTaskSelection.lbTasks.RowSource = "'" & ws.Name & "'!" & rng1.Address
    frmTaskSelection.Show
End Sub

Unless you have CourseSelection defined as a constant returning an existing worksheet name then the code will fail on With Worksheets(CourseSelection).Range("A3"). If you want to work with a sheet name CourseSelection you would use With Worksheets("CourseSelection").Range("A3").

Given you error message though you appear to have gotten past this point and your code appears to be failing on frmTaskSelection.lbTasks.RowSource = TaskList. This is because RowSource expects an address

If you were looking to populate the values from a sheet called CourseSelection from A3 to Ax where x is the last used cell, then this code will work from any active sheet.

Please note thate I was unclear as to how you wanted to use further values from column F in addition to A2:A6. If you can provide further guidance/picture etc then the code below can be adapted to suit

Sub test()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = Worksheets("CourseSelection")
    Set rng1 = ws.Range(ws.[a3], ws.Cells(Rows.Count, "A").End(xlUp))
    frmTaskSelection.lbTasks.RowSource = "'" & ws.Name & "'!" & rng1.Address
    frmTaskSelection.Show
End Sub

回复收藏 0 原文

~没有更多了~