[Excel - 2016] Refresh a comboBox after data entry
DESTIN8TE
Posted messages
40
Status
Member
-
cs_Le Pivert Posted messages 8437 Status Contributor -
cs_Le Pivert Posted messages 8437 Status Contributor -
Bonjour,
Simply put, I have a form with 4 tabs (Insert, Modify, ...).
My problem comes from Modify where I have a comboBox that retrieves the rows already listed in my table; if there are none, the comboBox obviously has no data (just for info).
If I enter a new item using the Insert tab and keep the form open, the comboBox in Modify does not retrieve the last entered row.
Therefore, I would like the comboBox to refresh automatically after inserting a row without having to reopen the form or even add a refresh button; the button would really be a last resort.
Here is the code to retrieve the rows:
PS: I know that the comboBox "Pg2cmbBoxSelectRow" does not refresh automatically since it is in the Initialize.
Info: I tried to put this code in the Insert button code in the Insert tab, it works but I get duplicates, so if I can remove these duplicates, it would normally be good too.
Thank you and feel free to ask for more details.
Simply put, I have a form with 4 tabs (Insert, Modify, ...).
My problem comes from Modify where I have a comboBox that retrieves the rows already listed in my table; if there are none, the comboBox obviously has no data (just for info).
If I enter a new item using the Insert tab and keep the form open, the comboBox in Modify does not retrieve the last entered row.
Therefore, I would like the comboBox to refresh automatically after inserting a row without having to reopen the form or even add a refresh button; the button would really be a last resort.
Here is the code to retrieve the rows:
Private Sub UserForm_Initialize() .......... MultiPgPOSTIT.Value = 0 'Code for Pg2Mod Dim Line As Integer Line = 9 Do While Sheets("DATA").Cells(Line, 3).Value <> "" Me.Pg2cmbBoxSelectRow.AddItem Sheets("DATA").Cells(Line, 3).Value Line = Line + 1 Loop .......... PS: I know that the comboBox "Pg2cmbBoxSelectRow" does not refresh automatically since it is in the Initialize.
Info: I tried to put this code in the Insert button code in the Insert tab, it works but I get duplicates, so if I can remove these duplicates, it would normally be good too.
Thank you and feel free to ask for more details.
1 answer
-
Hello,
see here: fill a Combobox without duplicates
https://silkyroad.developpez.com/VBA/ControlesUserForm/#LII-F
--
@+ The Woodpecker-
-
I tried this:
Private Sub UserForm_Initialize() Dim I As Integer For I = 9 To Range("C65536").End(xlUp).Row Me.Pg2cmbBoxSelectRow = Range("C" & I) If Me.Pg2cmbBoxSelectRow.ListIndex = -1 Then Me.Pg2cmbBoxSelectRow.AddItem Sheets("DATA").Range("C" & I).Value Next I
Which is the same as what I did.
But I still don't have the new line written down. -
It's normal to place this code when opening the UserForm. It only works once.
Put it in a Button like this:
Option Explicit Private Sub CommandButton1_Click() Dim I As Integer For I = 9 To Range("C65536").End(xlUp).Row Me.Pg2cmbBoxSelectRow = Range("C" & I) If Me.Pg2cmbBoxSelectRow.ListIndex = -1 Then Me.Pg2cmbBoxSelectRow.AddItem Sheets("DATA").Range("C" & I).Value Next I End Sub
You need to set the UserForm property ShowModal to False in order to work on the sheet
@+ The Woodpecker
-
