drop down list

[Guest]

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

 

[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

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

 

[Norman Jones]

Hi C,

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

Fortunately, Debra was was not similarly inattentive,

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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]

What's in cell R30?

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

:

 

[Guest]

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]

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