Combobox (NON-activeX) value not updating from vba

[piers_morgan]

Hi all,

I need both the list and the value of a combobox to update from the macro initiated from another combobox.

So imagine I have a Combobox called "Region". And another combobox called "Countries". Both are defaulted to "All" as the selection until an action is taken.

If a user selects France from Countries combobox, the Region combobox must update with the text "Europe". Or, if the USA was selected, Region box must update with "N.America".

And the texts *do* update just fine appearance-wise. But behind the scenes, in fact the texts of "Europe" are just superficial, and the combobox is stuck on thinking no change happened as a result of the macro. It's actually stuck on "All" (can be determined with a msgbox to see what the value still is).

Here is the key part of the code...
ActiveSheet.Shapes.Range(Array("RegionFormControl")).Select
With Selection
.Text = Sheets("Selections").Range("WYR2").Value ' "WYR2" contains the text "Eurpope" after France was selected. NOW WHY ON EARTH DOES IT NOT ALSO TRULY UPDATE THE VALUE?? ONLY APPEARS TO UPDATE ON THE SCREEN, BUT IS STUCK ON ORIGINAL "ALL" SELECTION
.ListFillRange = "Selections!$a$2:$a$" & LastRowRegion
End With

So I seem to be missing some command like "also update the value for real, not only how the combobox appears on screen".

I am sure I am missing something simple! Thanks guys!

 

[piers_morgan]

After hours of searching, I realise I need to add a ".value", this value being numeric based on where the value appears in the list.

In other words, and apologies if unclear, but Europe held position 2 in the list. So I needed to tell the combobox to select the 2nd from the list by going back to the worksheet that holds the very list:-
Dim RowFind As Range
With Sheets("selections").Range("$a$2:$a$" & LastRowRegion)
Set RowFind = .Find(What:="" & newRegionvalue, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not RowFind Is Nothing Then
RowSearch = RowFind.Row - 1
End If
End With

RowSearch stored the position 2 in memory. Then this gets passed on in the next lines of code...
ActiveSheet.Shapes.Range(Array("RegionFormControl")).Select
With Selection
.Text = newRegionvalue
.ListFillRange = "Selections!$a$2:$a$" & LastRowRegion
.LinkedCell = ""
.Value = RowSearch '<here it is!!
End With

I didn't want to leave the thread unanswered and hope this didn't overly complicate for others. It seems rather weird M.soft have such a different way of doing things between userforms and worksheet objects, which on the face of should be doing the same things!