I have a listbox that is supposed to display entries in initialize when a button is clicked from excel sheet.

我有一个列表框，当从Excel表格中单击按钮时，它应该显示初始化中的条目。

The button is calling a module to show the form.

该按钮正在调用一个模块以显示该表单。

Module code:

模块代码：

Option Explicit

Sub Show_Form()
  UserForm1.Show
End Sub

The form shows but the listbox display is blank.I have been setting the property of listbox to different multiselect but still displays blank when the green button above is clicked.

表单显示，但列表框显示为空。我一直在将列表框的属性设置为不同的多选，但当单击上面的绿色按钮时，它仍然显示为空。

But when I click the run button in VBA Developer:

但当我在VBA Developer中单击Run按钮时：

Sometimes it displays, sometimes it does not.

有时它会显示，有时它不会。

Is this a bug?

这是个窃听器吗？

This is the code of my simple form. The code below is for saving entries and displaying the last 10 entries. No problem with that. Just the listbox not displaying but sometimes displaying.

这是我的简单表单的代码。下面的代码用于保存条目并显示最后10个条目。这没问题。只是列表框不显示，但有时会显示。

Private Sub CommandButton1_Click()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("ExcelEntryDB")

Dim n As Long

n = sh.Range("C" & Application.Rows.Count).End(xlUp).Row

    sh.Range("C" & n + 1).Value = Format(Date, "mm/dd/yyyy")

    sh.Range("D" & n + 1).Value = Format(Time, "hh:nn:ss AM/PM")

    sh.Range("E" & n + 1).Value = Me.txtColor.Value

    sh.Range("F" & n + 1).Value = Me.txtName.Value

    sh.Range("G" & n + 1).Value = Me.txtShape.Value

   

Me.txtName.Value = ""

Me.txtColor.Value = ""

Me.txtShape.Value = ""



showListBoxEntries

End Sub

Private Sub UserForm_Initialize()

    showListBoxEntries

End Sub

Sub showListBoxEntries()

'showing 10 entries only in listbox

Dim arr(10, 5), lastRow As Long, i As Integer 'get last column number using index

With ActiveSheet

arr(0, 0) = .Cells(1, 3)

arr(0, 1) = .Cells(1, 4)

arr(0, 2) = .Cells(1, 5)

arr(0, 4) = .Cells(1, 7)

arr(0, 5) = .Cells(1, 8)

lastRow = .Cells(Rows.Count, 3).End(xlUp).Row

    If lastRow > 10 Then

        For i = 1 To 10

            arr(i, 0) = .Cells(lastRow - 10 + i, 3).Text

            arr(i, 1) = .Cells(lastRow - 10 + i, 4).Text

            arr(i, 2) = .Cells(lastRow - 10 + i, 5).Text

            arr(i, 3) = .Cells(lastRow - 10 + i, 6).Text

            arr(i, 4) = .Cells(lastRow - 10 + i, 7).Text

        Next

    Else

        For i = 1 To lastRow - 1

            arr(i, 0) = .Cells(i + 1, 3).Text

            arr(i, 1) = .Cells(i + 1, 4).Text

            arr(i, 2) = .Cells(i + 1, 5).Text

            arr(i, 3) = .Cells(i + 1, 6).Text

            arr(i, 4) = .Cells(lastRow - 10 + i, 7).Text

        Next

    End If

End With

With Me.ListBox1

.ColumnHeads = True

.ColumnCount = 6

.ColumnWidths = "75,75,75,75,75,75"

.List = arr()

End With

End Sub

If ActiveSheet is used must be certain that before starts the code the sheet with the data is the selected (visible in the window) and later while the code is running there is not selected or activate another sheet when the execution gets to the ActiveSheet code part. Just because of this it is easier and require less tracking of the actual cells or sheets to use their names.

如果使用ActiveSheet，则必须确保在开始代码之前，包含数据的工作表是选定的(在窗口中可见)，并且稍后在代码运行时没有选定，或者在执行到ActiveSheet代码部分时激活另一个工作表。正因为如此，使用其名称的实际单元格或工作表更容易，并且需要更少的跟踪。

According to this (test succeded based on comment) replace this line

根据此(基于注释的测试成功)替换此行

With ActiveSheet

with this

有了这个

With Worksheets("the_sheet_name")   'where the data are