PDA

View Full Version : ListBox on Userform doesn't refresh



velu_888
01-17-2017, 10:31 PM
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
01-18-2017, 02:18 AM
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
01-18-2017, 11:18 AM
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.:bug:
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........!:thumb

SamT
01-19-2017, 10:21 PM
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