Excel and VBA

[IDontBelieveIt]

Yes Hoovie, more realistically the list reads

A1 = "Select Fruit"
A2= "Apple"
A2= "Pear"
A3 = "Plum"

etc. etc. The user does not change these indeed he does not even see them other than when he opens a pick list or combo-box. The list has the usual linked cell.

Now, when the application is first loaded, or after a 'reset' (same macro invoked) then the pick list/combo-box shows "Select Fruit".

I cannot see 'shuffling the deck' via a macro would work as we have no idea which item the pick list is 'pointing to'..... ummm .... thinks ....

Oh yes we do (silly me!). So what you saying is pick up the linked cell value and shuffle the strings to suit? I think I see what your getting at... At home now and don't have the same version of Excel here (Mac). Will try in the morning.

Talking with someone who knows certainly helps Hoovie.
And YET again many thanks!! When's your birthday - will send you a nice 'present'

[Hoovie]

Think we are looking at the problem slightly differently to get the same answer

Now, when the application is first loaded, or after a 'reset' (same macro invoked) then the pick list/combo-box shows "Select Fruit".

Yes, indeed

I cannot see 'shuffling the deck' via a macro would work as we have no idea which item the pick list is 'pointing to'..... ummm .... thinks ....

Oh yes we do (silly me!). So what you saying is pick up the linked cell value and shuffle the strings to suit? I think I see what your getting at... At home now and don't have the same version of Excel here (Mac). Will try in the morning.

Because the default option is always the one in Cell A1 "Select Fruit", then a macro that essentially replaces whatever is in the pick list cell with the code "=A1" (or just the the string that Cell A1 contains - either way will work) will mean it (the pick list cell) will then say "Select Fruit" again as far as the user sees it

[thescruff]

But would you put Tomatoes in with the select fruit or veg

[IDontBelieveIt]

Agreed Hoovie - BUT the only 'problem' is that all other procedures that pick up the selected and picked item then have to scan the re-adjusted list to determine which items has been selected if you get my drift

Not a 'big deal' nor difficult just extra bit of coding. One would think that MS would make it easier by being able to force a value, as you can with items such as radio-buttons etc.

But thanks for giving me a "thought kick" - as you rightly say there are many ways to skin a cat.

[Hoovie]

Yup - you may need to either call other subroutines to cycle through and refresh. If relavent, you could read the value in the cell first before resetting it and store it elsewhere and use it as need be

What I quite like to do sometimes is to have the use enter a value in one cell, but have other cells (which are hidden) deal with the data with appropriate formulae.

When you say 'force a value', that is of course possible with the data validation, by checking an option so once the user has gone into into, they cannot leave it blank

lots of ways to skin a DOG (not a cat ) and all depends on the task . Not fully sure what your task at hand is, but no doubt you have sorted it.

I am only on my 4th workbook at the moment - 6 to go before I start phase II tonight

[IDontBelieveIt]

Sorry Hoovie - what I meant by 'force' I mean a macro can directly clear or set a radio button ready for the user to make a selection (from a range of radio buttons, say). But, oddly, is unable to 'force' a pick list to to known and predefined state/selection. I can see the need for me to buy you a few pints to discuss these topics, quite hard using a forum.

[Hoovie]

It is much easier when one person can show the other exactly what they mean :D

I don't often use radio buttons as my 'audience' are not that bright and only just understand cells! it is hard enough getting them to understand clicking a button to run a macro

Had enough for tonight - still have 4 books to do, but did a smart bit of forumula writing on the collation side where all the info feeds to (current suite of workbooks are VBA free and I needed to use some fancy formulas instead )