Listbox not showing entries when macro-enabled button is clicked

Question

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

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:

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.

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

Answer 1

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.

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

With ActiveSheet

with this

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