ListBox on Userform doesn't refresh

[velu_888]

I have the code below for displaying the worksheet on the list of the user form, if any cell is updated on the worksheet, the listbox never gets updated. The listbox would be the same as when opened. I want to automatically update the listbox, whenever in the worksheet,any changes in the cell/cells happen.

Private Sub UserForm_Initialize()

    Dim lb As msforms.ListBox
    
    ' // alternative method to pick up Worksheet data
    Dim ar As Variant
    ar = Worksheets("database1").Range("A1:M50000").Cells
    
    'Place the array in the listbox
    Set lb = Me.ListBox1
    With lb
        .ColumnCount = 23
        
        ' // pick up & hide the first Column of data
        .ColumnWidths = "50;200;100;100;100;100;150;100;100;100;100;100;100;100;100;100;100;100;100;70;70;70;70"
        .List = ar
    End With
End Sub

[GTO]

Greetings,

Although I don't usually use .RowSource, that is likely what you'd want to use. By simple demo, in a new/blank workbook:

In cells A1 and B1 enter headings.

In cell A2 enter: = RANDBETWEEN(1,100)

Drag cell A2 down to A11, then drag across to column 2.

Col 1	Col 2
74	66
70	33
87	7
92	57
9	65
60	97
8	42
17	35
45	63
90	54

In a new UserForm, add two command buttons and one listbox.

UserForm's Code:

Option Explicit
  
Private Sub CommandButton1_Click()
  Sheet1.Calculate
End Sub
  
Private Sub CommandButton2_Click()
  Unload Me
End Sub
  
Private Sub UserForm_Initialize()
  
  With Me
    With .ListBox1
      .ColumnCount = 2
      .ColumnHeads = True
      .Width = 113
      .ColumnWidths = "50 pt;60 pt"
      .Height = 70
      .Left = 6
      .RowSource = Sheet1.Range("A2:B11").Address(0, 0, xlA1, -1)
      .Top = 6
    End With
    With .CommandButton1
      .Caption = "ReCalc"
      .Default = False
      .Height = 21.75
      .Left = 6
      .Top = .Parent.ListBox1.Top + .Parent.ListBox1.Height + 6
      .Width = 66
    End With
    With .CommandButton2
      .Caption = "Unload"
      .Default = True
      .Height = 21.75
      .Left = 6
      .Top = .Parent.CommandButton1.Top + .Parent.CommandButton1.Height + 6
      .Width = 66
    End With
    .Height = .CommandButton2.Top + .CommandButton2.Height + 24
    .Width = 6 + .ListBox1.Width + 10
  End With
  
End Sub

Standard Module Code:

Option Explicit


Sub example()
  UserForm1.Show vbModeless
End Sub

When you run the form and click the recalc button, you will see the values update.

Hope that helps,

Mark

[velu_888]

Greetings,

Although I don't usually use .RowSource, that is likely what you'd want to use. By simple demo, in a new/blank workbook:

In cells A1 and B1 enter headings.

In cell A2 enter: = RANDBETWEEN(1,100)

Drag cell A2 down to A11, then drag across to column 2.

Col 1	Col 2
74	66
70	33
87	7
92	57
9	65
60	97
8	42
17	35
45	63
90	54

In a new UserForm, add two command buttons and one listbox.

UserForm's Code:

Option Explicit
  
Private Sub CommandButton1_Click()
  Sheet1.Calculate
End Sub
  
Private Sub CommandButton2_Click()
  Unload Me
End Sub
  
Private Sub UserForm_Initialize()
  
  With Me
    With .ListBox1
      .ColumnCount = 2
      .ColumnHeads = True
      .Width = 113
      .ColumnWidths = "50 pt;60 pt"
      .Height = 70
      .Left = 6
      .RowSource = Sheet1.Range("A2:B11").Address(0, 0, xlA1, -1)
      .Top = 6
    End With
    With .CommandButton1
      .Caption = "ReCalc"
      .Default = False
      .Height = 21.75
      .Left = 6
      .Top = .Parent.ListBox1.Top + .Parent.ListBox1.Height + 6
      .Width = 66
    End With
    With .CommandButton2
      .Caption = "Unload"
      .Default = True
      .Height = 21.75
      .Left = 6
      .Top = .Parent.CommandButton1.Top + .Parent.CommandButton1.Height + 6
      .Width = 66
    End With
    .Height = .CommandButton2.Top + .CommandButton2.Height + 24
    .Width = 6 + .ListBox1.Width + 10
  End With
  
End Sub

Standard Module Code:

Option Explicit


Sub example()
  UserForm1.Show vbModeless
End Sub

When you run the form and click the recalc button, you will see the values update.

Hope that helps,

Mark

GTO, the code provided by you, works fine on another userform that I've created seperately for this.
As per my code, I used on only one form, where there is a data entry and also listbox display of the worksheet.Hence I required solution for refresh of listbox.
Now If possible any excelgurus may provide me the solution or there is no problem I've used GTO's code to work on another userform for list display by a command button on my main userform. Thank you GTO for a quicker response........!

[SamT]

In UserForm

Private Sub UserForm_Initialize()
    Dim lb As msforms.ListBox 
     
     ' // alternative method to pick up Worksheet data
    Dim ar As Variant 
    ar = Worksheets("database1").Range("A1:M50000").Cells 
     
     'Place the array in the listbox
    Set lb = Me.ListBox1 
    With lb 
        .ColumnCount = 23 
         
         ' // pick up & hide the first Column of data
        .ColumnWidths = "50;200;100;100;100;100;150;100;100;100;100;100;100;100;100;100;100;100;100;70;70;70;70" 
        .List = ar 
    End With

Public Sub UpdateList()
     Dim ar As Variant 
    ar = Worksheets("database1").Range("A1:M50000").Cells 
 Me.ListBox1.List = ar 
End Sub

In Worksheet database1

Private Sub Worksheet_Change(ByVal Target As Range)
'Edit as needed to prevent spurious updates
UserForm1.UpdateList
End Sub