Microsoft Excel - automatic text in the cell of a drop-box before a selection is even made from the list?

Asked By Ali Kops on 03-Jul-12 03:57 PM
How do I create a drop-down list cell so that a certain word automatically appears in the cell before a selection is even made from the list of that cell?
 
I have a series of three drop-down boxes, with the lists that appear being dependent on the selections in the other boxes (the second drop-box list depends on the selection in the first box, and the third drop-box list depends on the selection in the second box). I would like for the cells hosting the second and third drop-box to automatically say ALL before a more specific selection is made from their lists. So, if a user makes a selection in all three drop-boxes, then goes back and changes the selection in the first drop-box, I'd like for the second two drop-boxes to clear what they previously said and automatically display the text ALL. When the user clicks on the arrow to display the list of those second two boxes, the lists will still be the same (2nd depending on the 1st, and 3rd depending on the second) but the text ALL is just the default that is displayed before any selection is made from those last two lists.

Is that possible?

If that isn't clear, please let me know and I will try to rephrase the question.

I'd appreciate any help there is!
Thank you!
wally eye replied to Ali Kops on 03-Jul-12 07:29 PM
You can use the worksheet_change event to detect when your initial drop-down is changed:

public sub Worksheet_Change(byval Target as Excel.Range)

  if not intersect(target, range("B7")) is nothing then
  range("C7,D7").value = "All"
  endif

end sub

This assumes your first drop-down is in B7, and the next two are in C7:D7.  You might need to make put the word All in your 2nd and 3rd drop-down lists, but try it without it first.
Ali Kops replied to wally eye on 05-Jul-12 09:56 AM
Thank you very much! That achieved exactly what I needed.

Thanks again.
Ali Kops replied to wally eye on 05-Jul-12 10:41 AM
Thank you very much for your help, I actually have one more question:

My drop boxes now work so that if a user changes the selection in the first box, the second and third boxes automatically change to "ALL". What would I add to the code to have just the third box change to "ALL" if a selection is changed in the second box but the original selection remains in the first box?

I'd like all three drop boxes to be connected so that after a user makes their three selections: if they change the selection in the first drop box, the second and third boxes go to "ALL", and if they change the selection in only the second drop box, the third drop box goes to "ALL".

Thank you!
wally eye replied to Ali Kops on 05-Jul-12 11:24 AM
Just extend it a bit:

public sub Worksheet_Change(byval Target as Excel.Range)

  if not intersect(target, range("B7")) is nothing then
    range("C7,D7").value = "All"
  elseif not intersect(target, range("C7")) is nothing then
    range("D7").value = "All"
  endif

end sub