Combobox (NON-activeX) value not updating from vba

piers_morgan

New Member
Joined
Sep 3, 2003
Messages
20
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!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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!
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top