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
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.