CODEWITHSUNDEEP
excelvbaoffice365
chuck22
chuck22

Reputation: 45

Permanently add value in combobox

I have this part of code that, after I run a macro that create a new column, add the last column letter in a combobox

ColumnLetter = Split(Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(5, 0), "$")(0)

Worksheets("Sheet1").CB1.AddItem ColumnLetter

Works great but once I close the file the record disappear, exist a solution to keep this data in a permanent way?

Upvotes: 1

Views: 64

Answers (3)

Naresh
Naresh

Reputation: 3034

As you mentioned that after you run a macro that create a new column and add the last column letter in a combobox, I am assuming that the combobox is already populated with present columns before the macro run.

Also, as you mentioned the once you close the file the combobox records disappear, I assume that you want the list for the present columns letters to be populated in the combobox when you open the file.

Following is the code. As suggested by Peh, paste it as Thisworkbook object code

Private Sub Workbook_Open()
LCol = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To LCol
Cols = Cols & Split(Cells(1, i).Cells.Address(5, 0), "$")(0) & ","
Next
Cols = Left(Cols, Len(Cols) - 1)

ColsArr = Split(Cols, ",")

'Aa your combobox object is named CB1
Worksheets("Male").CB1.List = ColsArr

End Sub

Whenever the file is opened the combobox will be populated with used column letters

Upvotes: 0

chuck22
chuck22

Reputation: 45

Thanks for the advice, i solved in this way

Private Sub Workbook_Open()

        Dim lastCol As Integer
        Dim ColName As String
        Set male = Worksheets("Male")
        lastCol = male.Cells(5, male.Columns.Count).End(xlToLeft).column
        male.CB1.Clear
        For i = 7 To lastCol
            ColName = Replace(Split(Columns(i).Address, ":")(0), "$", "")
            male.CB1.AddItem ColName
        Next i
End Sub

Upvotes: 2

Pᴇʜ
Pᴇʜ

Reputation: 57683

Either you have to fill the combobox each time you open the file, or you have to store the values in a worksheet and link the combobox Rowsource to that data range (then it will keep the data):

Example: Store your data in a worksheet combodata in range A1:A50 and then set the rowsource of that combobox to combodata!A1:A50

Upvotes: 1

Related Questions