Update vba code through user input.

I think I am just hoping for an easy way out but is it possible to have self updating code based on user input?

I have a form that includes a combo box. As an example lets say I had this...

Sub frm_Mode_Initialize()
    'Clear previous values.
    frm_Mode.cmbBox.Clear
    With frm_Mode.cmbBox
        .AddItem "blue"
        .AddItem "red"
        .AddItem "yellow"
    End With
    frm_Mode.Show
End Sub

where frm_Mode would allow the user to pick blue, red, yellow, or enter a new value. If the user entered a new value could I add add this to the initialize list for future use? OR will I have to keep track of this in a hidden spreadsheet or something similar?

TL:DR -> I am pretty sure I am expected to much out of VBA but it would be reassuring to know self updating code existed.

Hi,

Does John Walker's web site http://j-walk.com/ss/excel/tips/tip61.htm help?

I can't help thinking it will be easier/simpler to write the new values to a dynamic range name on a hidden and password protected sheet if necessary, and then using the range name in the row source property.

Regards

The 112th step..

Sub Userform_Initialize()
  cmbBox.list=sheets(1).cells(1,10).currentregion.columns(1).value
End Sub

The initialize event will be triggered when:

frm_Mode.show

If you want a connection between the combobox and userinput in the worksheet:

Private Sub cmbBox_click()
  cmbBox.list=sheets(1).cells(1,10).currentregion.columns(1).value
end sub

This is how I would do it, store the list on Sheet1, which can be very hidden.

The code in the combobox exit event checks if the entry is listed, if not there's an option to add it to the list

Hi,

Yes that's indeed the case, your combobox will contain as many columns as there is in the Row Source range.

If you only want to see one column in the combobox then you can of course either limit the rowsource range or use .Resize to shrink a named range down.

However having all columns is often very useful. The usual way to 'supress' columns is to use the ColumnWidths property to hide them. So for instance if there are 3 columns in the combobox and you want to see only the 2nd column set the ColumnWidth property to say 0;30;0. To see the 3rd column 0;0;30. Obviously change the 30 to widen or narrow what you see.

Don't also forget that you have the BoundColumn property which determines which column of the combobox gives the value. You can therefore have the combo box present say the first column by using the ColumnWidths property as described above and set the BoundColumn property to say 2, so that the combobox uses the 2nd column for its Value.

HTH

Why are using a horizontal list & why 3 Columns in the ComboBox?

This is possibly a case of the x-y problem. http://www.encyclo.co.uk/define/XY%20problem

I wanted to create a sheet where Row 1 would be the different modes.
The cells under each mode (or each column) would represent the different sheets to display. (The other sheets would be hidden.)

So if a user selects a mode the certain sheets appear. This way I can combine different applications used at my office into one excel sheet that is user friendly.

In the example workbook I posted you see Row1 has modes 'blue', 'red', and 'green'. Each mode has 3 sheets associated with it. A user form lets you pick the mode and then those sheets appear while the other 6 would stay hidden. If the mode was switched the other 3 sheets would appear.

Although in my example I suppose I could make 4 columns. The first for the mode and a column then for each mode.
However, it turns out I am having trouble with simply initializing my combo box on the first user form even when using a vertical list.
Is it true that when I say frm_Mode.show that frm_Mode_Initialize automatically takes place? Because my code apparently skips over it.
I call frm_Mode.Show from Sheet1 where frm_Mode_Initialize is in the user form code.

EDIT: I got it to work with vertical lists. Sort of an indirect way (putting list into an array and adding each array item to combobox) but as of right now I need to move on. I spent too much time this morning figuring this out. Thanks for all your help.

Where's the example workbook?

Thanks for the rep, but your comment mentioned implementing a search to prevent duplicates. Check the code it's already in there.