  
|
www.ultimatehandyman.co.uk Forum Index
-> Computers |
|
| Author |
Message |
IDontBelieveIt
Senior Member
Joined: 29 Jul 2008
Posts: 934
Location: North Hampshire
|
 Posted: Wed Sep 03, 2008 7:12 pm Post subject: |
  |
|
|
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'
_________________
Never lie - that way you don't have to remember what you said. |
|
| Back to top |
|
 |
|
|
|
Hoovie
Devon DIYer
Joined: 27 Jul 2007
Posts: 7773
Location: East Devon
|
| Posted: Wed Sep 03, 2008 8:11 pm Post subject: |
|
|
|
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 
_________________
If you have a son, DO NOT name him after a verb ....
"Neil, Standup. Neil, Standup"
"Which is it, man?" |
|
| Back to top |
|
|
thescruff
Senior Member
Joined: 10 Mar 2008
Posts: 4658
Location: Bath
|
| Posted: Wed Sep 03, 2008 8:17 pm Post subject: |
|
|
|
But would you put Tomatoes in with the select fruit or veg
_________________
Scruff |
|
| Back to top |
|
|
IDontBelieveIt
Senior Member
Joined: 29 Jul 2008
Posts: 934
Location: North Hampshire
|
| Posted: Wed Sep 03, 2008 8:20 pm Post subject: |
|
|
|
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.
_________________
Never lie - that way you don't have to remember what you said. |
|
| Back to top |
|
|
Hoovie
Devon DIYer
Joined: 27 Jul 2007
Posts: 7773
Location: East Devon
|
| Posted: Wed Sep 03, 2008 8:48 pm Post subject: |
|
|
|
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 
_________________
If you have a son, DO NOT name him after a verb ....
"Neil, Standup. Neil, Standup"
"Which is it, man?" |
|
| Back to top |
|
|
IDontBelieveIt
Senior Member
Joined: 29 Jul 2008
Posts: 934
Location: North Hampshire
|
| Posted: Wed Sep 03, 2008 9:14 pm Post subject: |
|
|
|
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.
_________________
Never lie - that way you don't have to remember what you said. |
|
| Back to top |
|
|
Hoovie
Devon DIYer
Joined: 27 Jul 2007
Posts: 7773
Location: East Devon
|
| Posted: Wed Sep 03, 2008 11:02 pm Post subject: |
|
|
|
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 )
_________________
If you have a son, DO NOT name him after a verb ....
"Neil, Standup. Neil, Standup"
"Which is it, man?" |
|
| Back to top |
|
|
|
FAQ
Search
Register
Profile
Log in to check your private messages
Log in
