drop down list

[c]

Greetings;
I am curious if any one out there knows how to the hide the values in a
dropdown list. What I want to do is choose an entry from a list and based on
the choice a different drop down list will show up in the same cell. I know
how to create drop down lists that are dependant upon a previous selection
(INDIRECT) but is it possible to do this all in the same cell. Or will I
have to use two cells and format the first so the data is hidden?

[Norman Jones]

Hi C,

See Debra Dalgleish's Dependent List, Data Validation page at:

http://www.contextures.com/xlDataVal02.html



---
Regards,
Norman



"c" <[email protected]> wrote in message
news:[email protected]...
> Greetings;
> I am curious if any one out there knows how to the hide the values in a
> dropdown list. What I want to do is choose an entry from a list and based
> on
> the choice a different drop down list will show up in the same cell. I
> know
> how to create drop down lists that are dependant upon a previous selection
> (INDIRECT) but is it possible to do this all in the same cell. Or will I
> have to use two cells and format the first so the data is hidden?

[Debra Dalgleish]

You could use a worksheet_Change event to display the selected list, or
show the original list if the cell is cleared. For example:

'=============================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$2" Then
If Application.WorksheetFunction _
.CountIf(Range("MonthList"), Target.Value) Then
With Target.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & Target.Value
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Else
If Target.Value = "" Then
With Target.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=MonthList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End If
End If
End Sub

'=============================

c wrote:
> Greetings;
> I am curious if any one out there knows how to the hide the values in a
> dropdown list. What I want to do is choose an entry from a list and based on
> the choice a different drop down list will show up in the same cell. I know
> how to create drop down lists that are dependant upon a previous selection
> (INDIRECT) but is it possible to do this all in the same cell. Or will I
> have to use two cells and format the first so the data is hidden?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Norman Jones]

Hi C,

My apologies, I responded without absorbing your same cell scenario.

Fortunately, Debra was was not similarly inattentive,

---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:uV%231nwO%[email protected]...
> Hi C,
>
> See Debra Dalgleish's Dependent List, Data Validation page at:
>
> http://www.contextures.com/xlDataVal02.html
>
>
>
> ---
> Regards,
> Norman
>
>
>
> "c" <[email protected]> wrote in message
> news:[email protected]...
>> Greetings;
>> I am curious if any one out there knows how to the hide the values in a
>> dropdown list. What I want to do is choose an entry from a list and
>> based on
>> the choice a different drop down list will show up in the same cell. I
>> know
>> how to create drop down lists that are dependant upon a previous
>> selection
>> (INDIRECT) but is it possible to do this all in the same cell. Or will I
>> have to use two cells and format the first so the data is hidden?
>
>

[c]

Thank you both for the help.

I think this macro will work well for me but it may conflict with another
oneI am already using that creates a second sheet based on the first. So
before I go through the long process of updating the second macro is there
any other methods that could accomplish this?

Thanks again,
c

"Debra Dalgleish" wrote:

> You could use a worksheet_Change event to display the selected list, or
> show the original list if the cell is cleared. For example:
>
> '=============================
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$D$2" Then
> If Application.WorksheetFunction _
> .CountIf(Range("MonthList"), Target.Value) Then
> With Target.Validation
> .Delete
> .Add Type:=xlValidateList, _
> AlertStyle:=xlValidAlertStop, _
> Operator:=xlBetween, _
> Formula1:="=" & Target.Value
> .IgnoreBlank = True
> .InCellDropdown = True
> .InputTitle = ""
> .ErrorTitle = ""
> .InputMessage = ""
> .ErrorMessage = ""
> .ShowInput = True
> .ShowError = True
> End With
> Else
> If Target.Value = "" Then
> With Target.Validation
> .Delete
> .Add Type:=xlValidateList, _
> AlertStyle:=xlValidAlertStop, _
> Operator:=xlBetween, _
> Formula1:="=MonthList"
> .IgnoreBlank = True
> .InCellDropdown = True
> .InputTitle = ""
> .ErrorTitle = ""
> .InputMessage = ""
> .ErrorMessage = ""
> .ShowInput = True
> .ShowError = True
> End With
> End If
> End If
> End If
> End Sub
>
> '============================

[c]

Thanks Debra,

The code works great except when I attempt to use the additional macro I
have already created. It copies the sheet for the next day. It then gives
me a type mismatch error on the day2 sheet at
If Application.WorksheetFunction _
.CountIf("OFFICE", Target.Value).

Any help with this error would be greatly appreciated!

Thanks in advance
c

[Debra Dalgleish]

It depends what your other macro is doing, but perhaps you could disable
events at the start of your macro, then enable them at the end. For example:

Application.EnableEvents = False
'your macro here
Application.EnableEvents = True

c wrote:
> Thanks Debra,
>
> The code works great except when I attempt to use the additional macro I
> have already created. It copies the sheet for the next day. It then gives
> me a type mismatch error on the day2 sheet at
> If Application.WorksheetFunction _
> .CountIf("OFFICE", Target.Value).
>
> Any help with this error would be greatly appreciated!
>
> Thanks in advance
> c
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[c]

Debra,
Thanks again for your input it has worked perfectly and the macro that
copies works excellent. One more question if you don't mind. When I try to
delete the entries in the first list or the second list I get a type 13
mismatch error at:

If Application.WorksheetFunction _
.CountIf(Range("R30"), Target.Value) Then

Is there a way to exit the macro if the delete key is entered or is there
any way to remove this error.

Thanks again for your time,
c

"Debra Dalgleish" wrote:

> It depends what your other macro is doing, but perhaps you could disable
> events at the start of your macro, then enable them at the end. For example:
>
> Application.EnableEvents = False
> 'your macro here
> Application.EnableEvents = True

[Debra Dalgleish]

What's in cell R30?

c wrote:
> Debra,
> Thanks again for your input it has worked perfectly and the macro that
> copies works excellent. One more question if you don't mind. When I try to
> delete the entries in the first list or the second list I get a type 13
> mismatch error at:
>
> If Application.WorksheetFunction _
> .CountIf(Range("R30"), Target.Value) Then
>
> Is there a way to exit the macro if the delete key is entered or is there
> any way to remove this error.
>
> Thanks again for your time,
> c
>
> "Debra Dalgleish" wrote:
>
>
>>It depends what your other macro is doing, but perhaps you could disable
>>events at the start of your macro, then enable them at the end. For example:
>>
>>Application.EnableEvents = False
>> 'your macro here
>>Application.EnableEvents = True
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[c]

Hello again,
R30 contains a reference to the name of the second list. I think I have
solved the problem for now by just inserting an:

on Error goto errhandler
errhandler:
if err.number = 13 then
<Code>
Exit Sub

So far it has solved the problem, it isn't pretty but it is giving the
results I want.

Thanks again for your time and knowledge. I truly appreciate your help.

Sincerely,
c

"Debra Dalgleish" wrote:

> What's in cell R30?

[Debra Dalgleish]

You're welcome, and thanks for letting me know how you worked around the
problem.

c wrote:
> Hello again,
> R30 contains a reference to the name of the second list. I think I have
> solved the problem for now by just inserting an:
>
> on Error goto errhandler
> errhandler:
> if err.number = 13 then
> <Code>
> Exit Sub
>
> So far it has solved the problem, it isn't pretty but it is giving the
> results I want.
>
> Thanks again for your time and knowledge. I truly appreciate your help.
>
> Sincerely,
> c
>
> "Debra Dalgleish" wrote:
>
>
>>What's in cell R30?
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html