Hiding a cell's drop-down menu, dependent on a tick-box selection...

Hi All,

I'm working on putting together a project initiation form at work, which involves registering if established business partners (ie. partners the IT business has worked with on previous projects) will be involved in forthcoming projects.

The problem:

1. I have included a tick-box, which asks 'will this project involve IT business partners?'
2. I have also included a) a drop-down menu which will become visible/usable after the tick-box is selected, and b) a cell for including 'Other' - this will be used in the event of the 'Other' selection in the drop-down being chosen.
3. Drop-down data is on another worksheet within the workbook.
4. I would like to hide/disable the drop-down and 'Other' text cell until the tick-box is selected. The 'Other' text cell will, in-turn, remain hidden/disabled until the ‘Other’ field within the drop-down menu is chosen.

Seems tricky, and I’ve scoured MS Excel help and the Web for answers, with no success. Is the above achievable?

Regards,

Richard

I'm sure there's a better way but here's one way.

Say that your check box is linked to A1 and your drop down menu is in B1, then select B1 go to Data/Validation, select "list" from the "Allow" menu and in the formula box type something like:

=IF(A1=TRUE,list1,list2)

list1 would be your drop down menu data, list2 would be any empty cell, when the check box is checkcked then the drop down menu will show list1, unchecked then list2 (empty).

For the "Other" option cell you could also use Data Validation....select "custom" from the "allow" meu and type:

=B1="other"

data will only be possible if B1 = "Other", I would also use conditional formatting to make the cell invisible unless "other" is selected.

Hope this helps!
Jean-Guy

Put all this stuff onto a Userform - -VB Editor - where you more access to
vba code.

putting controls onto a spreadsheet is asking for disaster.



"mintos" wrote:

>
> Hi All,
>
> I'm working on putting together a project initiation form at work,
> which involves registering if established business partners (ie.
> partners the IT business has worked with on previous projects) will be
> involved in forthcoming projects.
>
> The problem:
>
> 1. I have included a tick-box, which asks 'will this project involve IT
> business partners?'
> 2. I have also included a) a drop-down menu which will become
> visible/usable after the tick-box is selected, and b) a cell for
> including 'Other' - this will be used in the event of the 'Other'
> selection in the drop-down being chosen.
> 3. Drop-down data is on another worksheet within the workbook.
> 4. I would like to hide/disable the drop-down and 'Other' text cell
> until the tick-box is selected. The 'Other' text cell will, in-turn,
> remain hidden/disabled until the ‘Other’ field within the drop-down
> menu is chosen.
>
> Seems tricky, and I’ve scoured MS Excel help and the Web for answers,
> with no success. Is the above achievable?
>
> Regards,
>
> Richard
>
>
> --
> mintos
> ------------------------------------------------------------------------
> mintos's Profile: http://www.excelforum.com/member.php...o&userid=31360
> View this thread: http://www.excelforum.com/showthread...hreadid=510527
>
>