用户表单列表框行源未填充 [英] Userform listbox rowsource not populating

问题描述

我有一个带有 OK 和 Cancel 按钮和一个列表框的用户表单.它应该使用以下代码填充:

I have a userform with an OK and Cancel button and a listbox. It is supposed to populate using this code:

Private Sub UserForm_Initialize()

Me.StartUpPosition = 0
Me.Top = Application.Top + (Application.Height / 2) - (Me.Height / 2)
Me.Left = Application.Left + (Application.Width / 2) - (Me.Width / 2)

With Me.ListBox1
    .RowSource = ""
    .ColumnCount = 7
    .ColumnWidths = "80;100;20;1;20;1;1000"
    .RowSource = Sheets("BOH Database").Range("H9:N14").Address
  '  .RowSource = Sheets("BOH Database").Range("H9:N" & Sheets("BOH Database").Range("a65536").End(xlUp).Row - 1).Address
End With

End Sub

RowSource 语句都不起作用.我曾尝试在再次填充之前清除 RowSource.我做错了什么?

Neither RowSource statements work. I have tried clearing RowSource before filling it again. What am I doing wrong?

我已经添加了我目前在这里拥有的代码，因为它在评论中没有正确显示:我正在根据您的代码使用此代码，它使工作表崩溃:

I've added the code i currently have here as it doesn't show properly in the comment: I'm using this code based on yours and it's crashing the sheet:

With Me.ListBox1
 .ColumnCount = 7
 .ColumnWidths = "80;100;20;1;20;1;1000"
 .RowSource = "'" & Sheets("BOH Database").Name & "'!" & Sheets("BOH Database") _
    .Range("H9:N" & Sheets("BOH Database").Range("a65536").End(xlUp).Row - 1).Address
End With

推荐答案

这是一种不正确的做法.语法是

That is an incorrect way to do it. The syntax is

ListBox1.RowSource = "SheetName!RangeAddress"

因此，如果您的工作表名称是 Sheet1，则上述内容变为

So if your sheet name is say Sheet1 then the above becomes

ListBox1.RowSource = "Sheet1!H9:N14"

此外，由于您的工作表名称有空格，因此您必须在工作表名称前后添加 '.

Also since your sheet name has a space, you will have to add ' before and after the sheet name.

试试这个

 ListBox1.RowSource = "'BOH Database'!H9:N14"

或者以你的方式...

With Sheets("BOH Database")
    ListBox1.RowSource = "'" & .Name & "'!" & .Range("H9:N14").Address
End With

这篇关于用户表单列表框行源未填充的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！