Store values in Combo Box
Hi,
I am just wondering how could I store the values a user has inputed in the combo box so that next time, the user run the macro again, he can click on the combo box and find his last input in the drop-down list. Thanks! |
Store values in Combo Box
You don't say how the ComboBox is populated initially. But suppose
ComboBox1 is initially populated from a list in Sheet1!A1:An. If the user adds a new item to ComboBox1, the following code will add the new item to the list. DO NOT use RowSource in the Properties Window. Private Sub UserForm_Activate() Dim iEnd As Long Dim rng As Range iEnd = Sheets("Sheet1").Range("A1").End(xlDown).Row Set rng = Sheets("Sheet1").Range("A1:A" & iEnd) ComboBox1.RowSource = rng.Worksheet.Name & "!" & rng.Address End Sub Private Sub ComboBox1_Change() Dim rng As Range Set rng = Range(ComboBox1.RowSource) If IsError(Application.Match(ComboBox1.Value, rng, 0)) = True _ And ComboBox1.Value < "" Then Set rng = rng.Resize(rng.Rows.Count + 1, 1) rng(rng.Rows.Count, 1) = ComboBox1.Value ComboBox1.RowSource = rng.Worksheet.Name & "!" & rng.Address End If End Sub Hth, Merjet |
Store values in Combo Box
But what if the combo box is in a user form?! Like this user form will pop up
everytime the user run the macro. "merjet" wrote: You don't say how the ComboBox is populated initially. But suppose ComboBox1 is initially populated from a list in Sheet1!A1:An. If the user adds a new item to ComboBox1, the following code will add the new item to the list. DO NOT use RowSource in the Properties Window. Private Sub UserForm_Activate() Dim iEnd As Long Dim rng As Range iEnd = Sheets("Sheet1").Range("A1").End(xlDown).Row Set rng = Sheets("Sheet1").Range("A1:A" & iEnd) ComboBox1.RowSource = rng.Worksheet.Name & "!" & rng.Address End Sub Private Sub ComboBox1_Change() Dim rng As Range Set rng = Range(ComboBox1.RowSource) If IsError(Application.Match(ComboBox1.Value, rng, 0)) = True _ And ComboBox1.Value < "" Then Set rng = rng.Resize(rng.Rows.Count + 1, 1) rng(rng.Rows.Count, 1) = ComboBox1.Value ComboBox1.RowSource = rng.Worksheet.Name & "!" & rng.Address End If End Sub Hth, Merjet |
Store values in Combo Box
But what if the combo box is in a user form?! Like this user form will pop up
everytime the user run the macro. That is what I assumed. See my 1st line of code. Merjet |
All times are GMT +1. The time now is 03:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com