How to update a Combobox when changing the source data?

[geiz]

Hello,

I am working for years with Excel, but for the first time I've started a project using VBA.
I've read a lot (often on this forum), watched tutorials, and played a lot.
Most created functions are working well (after a lot evenings of debugging), but I am struggling now already a whole day on one simple function which doesn't work like I wanted it. I hope you can help me with this.

I have used some comboboxes on the excel sheet (no form), where you can select some information, coming out of another sheet (using ListFillRange function). This works great. But when I select with e.g. combobox1 a value, I want another combobox to be grayed out with the message "not applicable". I use a small sub with the code "ComboBox5.Enabled = False" to make it grayed out. I also change the source data (in some cells on a worksheet), with on the first row "not applicable" (using: If InStr(1, Worksheets("Data").Range("F19").Value, "0", vbTextCompare) = 1 Then
Sheets("Data").Range("D2") = "Not Applicable"). But unfortunately it doesn't update untill I press the combobox drop-down button (in active state).
Does someone knows a trick to get the right message (grayed-out not neccessary) in the right cell or in that particular combobox? I prefer a command to update the combobox.

Thank you very much!

[Leith Ross]

Hello geiz,

Welcome to the forum! It would help if you posted your workbook.

To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

File Manger Pic

[geiz]

Thank you for the reply.
Herewith I have stripped version of my sheet (confidentional information in the original document, but I hope it is still clear).
When i select a 'Test' in the Selection 1 combobox, a small macro is running. This one is replacing the list input for the combobox at Selection 4, on worksheet "Data" and enable/disable the combobox.
Unfortunately he is not automatically updating the list information in the combobox.
(It is not neccessary to retreive the information out of a worksheet, direct input in the combobox is also possible.)
I hope that someone have some good hints for me.