Drop Downs in cells B2 & D2 (more to come, maybe 5 + or so).

Code does what I want except the ClearContents which I have commented out.
(Ebable Events is commented out and I am using .Select for testing)

When a selection is made in the drop down, a list pertaining to that selection is copied to the column to the right of the drop down.

I am having trouble clearing that copied list when the next selection is made.
The list are various rows long and the column needs to be clean prior to the next list copied. Can't quite zero in on it, its just me and the late night I suppose!

The Drop Downs will be in every other column in row 2, for however many there will be.

Thanks.
Howard

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2,D2")) Is Nothing Then Exit Sub

Dim rngFound As Range
Dim aRowCount As Long, _
aColumn As Long, _
tRowCount As Long, _
tColumn As Long

Dim myFnd As String

myFnd = Target

'Application.EnableEvents = False

' tColumn = Target.Offset(, 1).Column
' MsgBox tColumn
' tRowCount = Cells(Rows.Count, tColumn).End(xlUp).Row
' MsgBox tRowCount

' Target.Offset(, 1).Resize(tRowCount, tColumn).Select '.ClearContents
'Exit Sub

Set rngFound = Sheets("Sheet3").Range("AA1:AL1").Find(What:=myFnd , _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngFound Is Nothing Then

aColumn = rngFound.Column
aRowCount = Cells(Rows.Count, aColumn).End(xlUp).Row
aColumn = rngFound.Column

rngFound.Offset(1, 0).Resize(aRowCount).Copy Target.Offset(, 1)

Else
MsgBox "No match found."
End If
Application.EnableEvents = True
End Sub

Hi Howard,

Am Thu, 22 Jan 2015 04:39:26 -0800 (PST) schrieb L. Howard:

' Target.Offset(, 1).Resize(tRowCount, tColumn).Select '.ClearContents

try:

Target.Offset(, 1).Resize(tRowCount - 1, 1).ClearContents


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

try:

Target.Offset(, 1).Resize(tRowCount - 1, 1).ClearContents


Regards
Claus B.


Seems I can't get a tRowCount value, that line errors out.


tRowCount = Target.Offset(, 1).Cells(Rows.Count).End(xlUp).Row

Target.Offset(, 1).Resize(tRowCount - 1, 1).ClearContents

Hi Howard,

Am Thu, 22 Jan 2015 08:39:37 -0800 (PST) schrieb L. Howard:

Seems I can't get a tRowCount value, that line errors out.

for me this works fine:

tColumn = Target.Offset(, 1).Column
MsgBox tColumn
tRowCount = Cells(Rows.Count, tColumn).End(xlUp).Row
MsgBox tRowCount

If tRowCount 1 Then _
Target.Offset(, 1).Resize(tRowCount - 1, 1).Select '.ClearContents


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

This is what I found to work.
I figured it out, the same as you indicate.

This works for me.

The tRowCount 1 seems a good idea which I will try out.

Thanks Claus.


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2,D2,F2")) Is Nothing Then Exit Sub

Dim rngFound As Range
Dim aRowCount As Long, _
aColumn As Long, _
tRowCount As Long, _
tColumn As Long

Dim myFnd As String

myFnd = Target

Application.EnableEvents = False

tColumn = Target.Offset(, 1).Column
tRowCount = Cells(Rows.Count, tColumn).End(xlUp).Row

Target.Offset(, 1).Resize(tRowCount - 1, 1).ClearContents

Set rngFound = Sheets("Sheet3").Range("AA1:AL1").Find(What:=myFnd , _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngFound Is Nothing Then

aColumn = rngFound.Column
aRowCount = Cells(Rows.Count, aColumn).End(xlUp).Row
aColumn = rngFound.Column

rngFound.Offset(1, 0).Resize(aRowCount).Copy Target.Offset(, 1)

Else
MsgBox "No match found."
End If
Application.EnableEvents = True
End Sub

Hi Howard,

Am Thu, 22 Jan 2015 09:04:55 -0800 (PST) schrieb L. Howard:

This is what I found to work.
I figured it out, the same as you indicate.

if you run into an error your events are disabled.
If I disable events I insert an error handler.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Thursday, January 22, 2015 at 9:12:36 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Thu, 22 Jan 2015 09:04:55 -0800 (PST) schrieb L. Howard:

This is what I found to work.
I figured it out, the same as you indicate.

if you run into an error your events are disabled.
If I disable events I insert an error handler.


Regards
Claus B.
--

I understand, I had a little button on the sheet that ran an enable events macro on the sheet. I used it during my tests, because of all the errors I was getting.

Works just fine now, I appreciate the help.

Howard

If your lists are contiguous, why not name them? Then you can store the
name in an array and retrieve it based on Target.Column, replacing it
with the new value!

Option Explicit

'Dim an array large enough for future expansion
Dim msRngNames$(1 To 50) '//adjust as required


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2,D2")) Is Nothing Then Exit Sub

Dim sName$
sName = IIf(msRngNames(Target.Column) = "", Target.Value, _
msRngNames(Target.Column))
With Target.Offset(0, 1)
If sName < "" Then
.Resize(Range(sName).Rows.Count) = ""
End If
If Target < "" Then
.Resize(Range(Target.Value).Rows.Count) = Range(Target.Value)
End If
msRngNames(Target.Column) = Target.Value '//store new value
End With
End Sub

This avoids any errors if the array or Target is empty. The named
ranges can be dynamic!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Optionally with some error handling...

Option Explicit

'Dim an array large enough for future expansion
Dim msRngNames$(1 To 50) '//adjust as required


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2,D2")) Is Nothing Then Exit Sub

Dim sName$, rngList As Range
sName = IIf(msRngNames(Target.Column) = "", Target.Value, _
msRngNames(Target.Column))

On Error GoTo Cleanup
With Target.Offset(0, 1)
If sName < "" Then
.Resize(Range(sName).Rows.Count) = ""
End If
If Target < "" Then
Set rngList = Range(Target.Value)
.Resize(rngList.Rows.Count) = rngList
End If
msRngNames(Target.Column) = Target.Value '//store new value
End With

Cleanup:
Set rngList = Nothing
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Thursday, January 22, 2015 at 11:37:53 AM UTC-8, GS wrote:
If your lists are contiguous, why not name them?

Hi Garry,

I had thought about using an array, (and named ranges) but knew someone would have to take me by the hand and show me left,right,up,down almost all the way.

Also, the lists will (most likely) be on a different sheet, so the hurdle of dynamic named ranges from another sheet is in the fray. I believe you told me once that is not a problem but I also remember the solution was nearly incomprehensible to me.

I'll start with the example you provided and see what I can do.

If you don't mind working with the mindless, standby for a few "what now's...??"

Howard

On Thursday, January 22, 2015 at 11:37:53 AM UTC-8, GS wrote:
If your lists are contiguous, why not name them?

Hi Garry,

I had thought about using an array, (and named ranges) but knew
someone would have to take me by the hand and show me
left,right,up,down almost all the way.

Also, the lists will (most likely) be on a different sheet, so the
hurdle of dynamic named ranges from another sheet is in the fray. I
believe you told me once that is not a problem but I also remember
the solution was nearly incomprehensible to me.

I'll start with the example you provided and see what I can do.

If you don't mind working with the mindless, standby for a few "what
now's...??"

Howard

Ok, Howard! It might be better, though, to send me the file so I can
just give you back a working solution. One concern is how to handle
dropdown values that have spaces if multi-word. Usually you can simply
replace spaces with underscores, but it's important to structure the
names and dropdown items to compliment going this way.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ok, Howard! It might be better, though, to send me the file so I can
just give you back a working solution. One concern is how to handle
dropdown values that have spaces if multi-word. Usually you can simply
replace spaces with underscores, but it's important to structure the
names and dropdown items to compliment going this way.

--
Garry

Thanks, Garry.

Hopefully this won't be too much a task. Maybe a fairly simple example of all this named range stuff will not be lost on me.

Here is a basic workbook with two drop downs and I have run a named range maker code on the lists on sheet 2.

https://www.dropbox.com/s/1fp9b8cb08...ry_v.xlsm?dl=0

Howard

Ok, Howard! It might be better, though, to send me the file so I can
just give you back a working solution. One concern is how to handle
dropdown values that have spaces if multi-word. Usually you can
simply replace spaces with underscores, but it's important to
structure the names and dropdown items to compliment going this
way.

--
Garry

Thanks, Garry.

Hopefully this won't be too much a task. Maybe a fairly simple
example of all this named range stuff will not be lost on me.

Here is a basic workbook with two drop downs and I have run a named
range maker code on the lists on sheet 2.

https://www.dropbox.com/s/1fp9b8cb08...ry_v.xlsm?dl=0

Howard

Um.., clearly there's a flaw in the "CreateNames" routine because
code-selecting any given range selects all rows across all cols. What
gives with the RefersTo for the range names?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Um.., clearly there's a flaw in the "CreateNames" routine because
code-selecting any given range selects all rows across all cols. What
gives with the RefersTo for the range names?

--
Garry


Can't answer that, was just using a code from a seemingly trusted site to make quick work of naming several ranges.

Best to scrap that and go with everything you use and know to work.

Howard

Here's my version...

https://app.box.com/s/23yqum8auvzx17h04u4f

Note that the list sheet is hidden. Also, its fully absolute ranges are
local scope and are used to define the dynamic lists. Since the lists
are on another sheet, their names are global for coding simplicity. (I
prefer they were also local scope, but that's a bit more complex to
manage!)

Have a look in NameManager to see how I've configured things. Note that
I replaced the hard-code address in your 'exit' line with a defined
name, and the list refs for the DVs are also defined name ranges. This
will permit using areas instead of a block of cells should you
want/need to move the lists around. Note that the DD_1/DD_2 headers
must each be contiguous but they don't have to be together. (You could
separate them with a col space between them without breaking anything!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

https://app.box.com/s/23yqum8auvzx17h04u4f

Note that the list sheet is hidden. Also, its fully absolute ranges are
local scope and are used to define the dynamic lists. Since the lists
are on another sheet, their names are global for coding simplicity. (I
prefer they were also local scope, but that's a bit more complex to
manage!)

Have a look in NameManager to see how I've configured things. Note that
I replaced the hard-code address in your 'exit' line with a defined
name, and the list refs for the DVs are also defined name ranges. This
will permit using areas instead of a block of cells should you
want/need to move the lists around. Note that the DD_1/DD_2 headers
must each be contiguous but they don't have to be together. (You could
separate them with a col space between them without breaking anything!)

--
Garry

Question.

DD_2_6 DD_2_7 DD_2_8 DD_2_9 DD_2_10

Am I correct in that the Hdr names are incorrect for these columns?

The offset formula for column DD_2_6 is Hdr_7.

DD_2_6 selected in the D2 drop down returns column G list.
DD_2_10 selected in the D2 drop down returns a blank list.

Howard

https://app.box.com/s/23yqum8auvzx17h04u4f

Note that the list sheet is hidden. Also, its fully absolute ranges
are local scope and are used to define the dynamic lists. Since the
lists are on another sheet, their names are global for coding
simplicity. (I prefer they were also local scope, but that's a bit
more complex to manage!)

Have a look in NameManager to see how I've configured things. Note
that I replaced the hard-code address in your 'exit' line with a
defined name, and the list refs for the DVs are also defined name
ranges. This will permit using areas instead of a block of cells
should you want/need to move the lists around. Note that the
DD_1/DD_2 headers must each be contiguous but they don't have to be
together. (You could separate them with a col space between them
without breaking anything!)

--
Garry

Question.

DD_2_6 DD_2_7 DD_2_8 DD_2_9 DD_2_10

Am I correct in that the Hdr names are incorrect for these columns?

The offset formula for column DD_2_6 is Hdr_7.

DD_2_6 selected in the D2 drop down returns column G list.
DD_2_10 selected in the D2 drop down returns a blank list.

Howard

Nice catch! All DV lists return values, just all wrong for DD_2 lists.
(DD_2_10 returns Hdr2_6 list, the remainders are off by 1 on my copy!)
My bad.., I wasn't paying attention to the sort order in NameManager
when I re-defined your existing names. I apologize!!!

So I corrected the range defs, inserted a narrow col between DD_1/DD_2
lists, and uploaded a revised file.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Question.

DD_2_6 DD_2_7 DD_2_8 DD_2_9 DD_2_10

Am I correct in that the Hdr names are incorrect for these columns?

The offset formula for column DD_2_6 is Hdr_7.

DD_2_6 selected in the D2 drop down returns column G list.
DD_2_10 selected in the D2 drop down returns a blank list.

Howard


I was able fix the typo and drop down in D2 returns stuff correctly.

Where I can't follow your footprints is adding these in the next 5 columns K:O.

I have added cell F2 to the DD_List and I have can select all the Hdr's K thru O in the drop down.

These:
DD_3_11 DD_3_12 DD_3_13 DD_3_14 DD_3_15

Trouble is making the connection to the data in the lists to these selections in the drop down.

Howard

Question.

DD_2_6 DD_2_7 DD_2_8 DD_2_9 DD_2_10

Am I correct in that the Hdr names are incorrect for these columns?

The offset formula for column DD_2_6 is Hdr_7.

DD_2_6 selected in the D2 drop down returns column G list.
DD_2_10 selected in the D2 drop down returns a blank list.

Howard


I was able fix the typo and drop down in D2 returns stuff correctly.

Where I can't follow your footprints is adding these in the next 5
columns K:O.

I have added cell F2 to the DD_List and I have can select all the
Hdr's K thru O in the drop down.

These:
DD_3_11 DD_3_12 DD_3_13 DD_3_14 DD_3_15

Trouble is making the connection to the data in the lists to these
selections in the drop down.

Howard

No problem! Have another look at the latest revision...

I added the DD_3 lists plus revised the list values (with a formula) to
better reflect the list they belong to instead of their col label. This
required adding a col-relative, row-absolute name for the headers
row...

=HdrRow&"-"&ROW()-1

This will make adding new lists easier to populate with values.


I renamed the cols to reflect they ref cols. (eg: DD1_1 to Col1_1)


<more info
I use the NameBox (left of FormulaBar) to enter fully absolute range
names. (Hdrs, cols, DV lists) I use the NameManager addin for
everything else.

The DV lists use fully absolute global names. They're names have been
revised to reflect they ref lists. (eg: DD_2 to DD2_Lists)

I hope you find this more helpful!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

No problem! Have another look at the latest revision...

I added the DD_3 lists plus revised the list values (with a formula) to
better reflect the list they belong to instead of their col label. This
required adding a col-relative, row-absolute name for the headers
row...

=HdrRow&"-"&ROW()-1

This will make adding new lists easier to populate with values.


I renamed the cols to reflect they ref cols. (eg: DD1_1 to Col1_1)


<more info
I use the NameBox (left of FormulaBar) to enter fully absolute range
names. (Hdrs, cols, DV lists) I use the NameManager addin for
everything else.

The DV lists use fully absolute global names. They're names have been
revised to reflect they ref lists. (eg: DD_2 to DD2_Lists)

I hope you find this more helpful!<g

--
Garry


Okay, and thanks for doing the workbook.

You can only dummy it down just so much, the rest is up to me to absorb it.<g

It is a good study piece for me, it will be helpful for sure.

I'll ponder away and thanks again.

May be back with a couple more what-if's, the code looks pretty intense! But at least I know what it is supposed to do, which will be helpful.

Howard

May be back with a couple more what-if's, the code looks pretty
intense! But at least I know what it is supposed to do, which will
be helpful.

I was already thinking how to handle if the lists get edited after
they're stored in the array. If shortened the resize to ClearContents
will leave populated cells. My thought is to store a delimited list...

name:rows

...and use that instead of current size!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ok.., I revised as explained so take another look!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

I was having a problem with adding the third drop down and columns in a earlier post, where you added it for me and reposted to the link.

I was looking for what I was missing in the process, wanting to learn how to do
it myself.

So I am trying to do a fourth drop down and I believe I have everything in place except the DD_4_16 name (for column 19, which is "S") where the Refers To: window should have the formula like this. At least I think so as I look at the others for an example.

=OFFSET(Sheet2!Hdr4_19,1,0,COUNTA(Sheet2!Col4_19)-1,1)

Whenever I type (not copy) that formula in the window I get this:

=OFFSET(Sheet2!Hrd4_19,1,0,COUNTA('Drop Down List Copied Garry_v2 (1).xlsm'!Col4_19)-1,1)

And the drop down does not produce the list on sheet 1.

If you get some time could you A, B, C me through the steps to do column S to work in the fourth drop down. I assume you have to do each column separate..?

I have the drop down and it is included in the DD_Lists, as cell H2 and DD4_Lists includes DD_4_16. If I click on DD4_Lists in the formula window, cell S1 on sheet 2 has the marching-ants on it.

So in plain third grade Canadian/English can you list the steps please. Probably should include the entire process even though I may have some already done a few of the correct steps.

Methinks I got all my DD_ this and that's correct, typos can be very misleading.<g

Thanks.
Howard

I have no idea why you're getting that ref after entering the formula!

First thing I should mention is the list numbering scheme you're using
doesn't sit well with me! I prefer to persist the same logic in terms
of refs to each list...

For DD2_List I'd go with..
DD_2_1. DD_2_2, DD_2_3, DD_2_4, DD_2_5
..so they clearly ref list 2, and their respective index.

...but I didn't bring this up due to the other concerns being prcessed.
That means I'd rather see your DD_4_16 named DD_4_1, and its column
isn't relevent to anything other than sheet layout. I revised my copy
to reflect this change so have another look!

Also, as you've seen, I've been inserting a narrow column between the
list groups and using alternating fill colors for odd/even blocks. That
means the DD_4 block would be the same fill as DD_2 block. (I just
don't feel more colors are needed to visually distinguish the groups!)


The step-by-step you requested:
<Using the NameBox
01: Name each hdr in same fashion as existing hdrs;
'Select each hdr cell before naming
<Example
Select DD_4_1;
click in the NameBox and type Sheet2!Hdr4_1;
Press the Enter key;
Right arrow to DD_4_2 and repeat.

02: Name the hdr group in the same fashion as existing groups;
Select all the DD4 hdrs;
Click in the NameBox and type DD4_Lists
Press the Enter key;

03: Name list cols in the same fashion as existing list cols;
'Select each DD4 list col before naming
<Example
Select DD_4_1;
click in the NameBox and type Sheet2!Col4_1;
Press the Enter key;


<In NameManager
04: Name each DD_4 list in same fashion as existing dynamic lists;
'If 'Sheet2' is the active sheet you won't need to specify
'the sheetname while typing the formula!
<Example
Type DD_4_1 in the Name box;
Tab into the RefersTo box and type...
=offset(hdr4_1,1,0,counta(col4_1)-1,1)
Add the name and repeat for the others in the group.

05: Update DD_Lists.RefersTo to include the absolute address of DD4;

<On Sheet1
06: Update the DV List ref for DD4.

I didn't add anything to the revised file so you could try adding DD4
on your own.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Also, as you've seen, I've been inserting a narrow column between the
list groups and using alternating fill colors for odd/even blocks. That
means the DD_4 block would be the same fill as DD_2 block. (I just
don't feel more colors are needed to visually distinguish the groups!)


The step-by-step you requested:
<Using the NameBox
01: Name each hdr in same fashion as existing hdrs;
'Select each hdr cell before naming
<Example
Select DD_4_1;
click in the NameBox and type Sheet2!Hdr4_1;
Press the Enter key;
Right arrow to DD_4_2 and repeat.

02: Name the hdr group in the same fashion as existing groups;
Select all the DD4 hdrs;
Click in the NameBox and type DD4_Lists
Press the Enter key;

03: Name list cols in the same fashion as existing list cols;
'Select each DD4 list col before naming
<Example
Select DD_4_1;
click in the NameBox and type Sheet2!Col4_1;
Press the Enter key;


<In NameManager
04: Name each DD_4 list in same fashion as existing dynamic lists;
'If 'Sheet2' is the active sheet you won't need to specify
'the sheetname while typing the formula!
<Example
Type DD_4_1 in the Name box;
Tab into the RefersTo box and type...
=offset(hdr4_1,1,0,counta(col4_1)-1,1)
Add the name and repeat for the others in the group.

05: Update DD_Lists.RefersTo to include the absolute address of DD4;

<On Sheet1
06: Update the DV List ref for DD4.

I didn't add anything to the revised file so you could try adding DD4
on your own.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Got the new sheet and these instructions.

Off to try it.

Thanks, again!

Howard

Got the new sheet and these instructions.

Off to try it.

Thanks, again!

Howard

And we have lift off!!

https://www.dropbox.com/s/ozm7b21hln...20it.xlsm?dl=0

Thanks for all the help.

Will keep for reference, however the code is magic stuff to me.

Howard

Got the new sheet and these instructions.

Off to try it.

Thanks, again!

Howard

And we have lift off!!

https://www.dropbox.com/s/ozm7b21hln...20it.xlsm?dl=0

Thanks for all the help.

Will keep for reference, however the code is magic stuff to me.

Howard

Well not exactly! Your DD4 names are global instead of local. (I
suspect you did not prefix with the sheetname where instructed) Have a
look at my version in the NameManager window and note the 'Scope' for
each name. Then fix your file so your names are as per the
instructions...

"..same as the other..."

...and then consider it a passing grade if it all works.

Note that the names are all local scope except for the dynamic ranges
and DD#_Lists. You can fix the RefersTo only in Excel. You can change
the scope in the NameManager addin using multi-select, but to do this
in Excel you have to delete the global names 1st, then recreate them as
local. I reiterate that you start using the NameManager addin!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Howard

Well not exactly! Your DD4 names are global instead of local. (I
suspect you did not prefix with the sheetname where instructed) Have a
look at my version in the NameManager window and note the 'Scope' for
each name. Then fix your file so your names are as per the
instructions...

"..same as the other..."

..and then consider it a passing grade if it all works.

Note that the names are all local scope except for the dynamic ranges
and DD#_Lists. You can fix the RefersTo only in Excel. You can change
the scope in the NameManager addin using multi-select, but to do this
in Excel you have to delete the global names 1st, then recreate them as
local. I reiterate that you start using the NameManager addin!!!

--
Garry


I'll work on that.

H

https://www.dropbox.com/s/ozm7b21hln...20it.xlsm?dl=0

I think I made the corrections you mentioned.

Also added a "Clear" selection in the drop down, which was the blank columns between the Sheet2 list groups. If you enter stuff in those columns, then it will just be another data column, otherwise the previous list is cleared as per the code, with nothing returned.

If this link in this post doesn't work, the one in my previous post does.

Thanks for the help, feel like I made some progress thanks to your demos and critique.

Howard

https://www.dropbox.com/s/ozm7b21hln...20it.xlsm?dl=0

I think I made the corrections you mentioned.

Also added a "Clear" selection in the drop down, which was the blank
columns between the Sheet2 list groups. If you enter stuff in those
columns, then it will just be another data column, otherwise the
previous list is cleared as per the code, with nothing returned.

Please explain your reasoning for adding this complexity! IMO it's
absolute overkill since clearing the DD cell normally also clears its
list!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Saturday, January 24, 2015 at 10:32:07 PM UTC-8, GS wrote:

https://www.dropbox.com/s/ozm7b21hln...20it.xlsm?dl=0

I think I made the corrections you mentioned.

Also added a "Clear" selection in the drop down, which was the blank
columns between the Sheet2 list groups. If you enter stuff in those
columns, then it will just be another data column, otherwise the
previous list is cleared as per the code, with nothing returned.

Please explain your reasoning for adding this complexity! IMO it's
absolute overkill since clearing the DD cell normally also clears its
list!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

First was just to see if I could do it and second I did not realize clearing the dd cell would clear the list.

Howard

On Saturday, January 24, 2015 at 10:32:07 PM UTC-8, GS wrote:

https://www.dropbox.com/s/ozm7b21hln...20it.xlsm?dl=0

I think I made the corrections you mentioned.

Also added a "Clear" selection in the drop down, which was the
blank columns between the Sheet2 list groups. If you enter stuff
in those columns, then it will just be another data column,
otherwise the previous list is cleared as per the code, with
nothing returned.

Please explain your reasoning for adding this complexity! IMO it's
absolute overkill since clearing the DD cell normally also clears
its list!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

First was just to see if I could do it and second I did not realize
clearing the dd cell would clear the list.

Howard

I see! Well congrats on figuring out a way to do it using code.

As for the 'second', reading the code reveals that any existing list is
*always* cleared, and a new list is only display if Target is not
empty! The code was deliberately written this way to persist the logic
of the project...

select a list in the dropdown to display its contents;
thus
clearing the dropdown should display nothing.

...which, as I see it, only makes sense in terms of user productivity.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion