Combobox, need for VBA for changing fillrange and changing linked cell
Hi,
I am a Belgian, dutch speeking person, so forgive me if I cannot express me clearly in English. Furthermore I am a starter in using VBA in excel, so please don't answer too technically for I might not understand your good work you are doing for me. I appreciate your help with the next topic.
Attached find workbook "code2" / Sheet2 combobox /
property 1. ListFillRange = named tablerange "code" from Sheet code!
property 2. Linkedcell = A4
I am trying to have following return thanks to some VBA code.
1. IF records from column "code" in table are added or deleted,
ListfillRange should adapt adequately
2. EACH TIME I surch for a new value in my combobox (with autofill),
Linkedcell should change to a5, a6, .... after choosing and entering the specific code out of the combobox
Can some one help me adding the right VBA in the viewcode of my combobox or eventually my worksheet/workbook ??
Thanks a lot, my appreciations !!
Best regards.
Luc
| Attachment | Size |
|---|---|
| code2.xlsm | 23.34 KB |
Hi, The named range "code" is
Hi,
The named range "code" is dynamically adjusting ok with the addition of new entries etc but that isn't automatically being reflected in the combobox. What you need is in an event triggered macro to reload the combobox listfillrange.
The code line is Sheet1.ComboBox1.ListFillRange = "code" and since you want the combobox to update on changes to table 1 then i suggest you put the following code into the sheet2 code module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet1.ComboBox1.ListFillRange = "code"
End Sub
I'm not sure about the second part of your question, it appears to work for me (if i understand it correctly)