Dynamic chart ranges populated from named ranges as selected in nested indirectly sourced validation lists.

[Phill Jones]

Hi all,

Long time reader - first time poster.

As the rather cryptic subject says, I'm trying to dynamically populate ranges to facilitate dynamic charts being generated.

I use excel 2010 at work, and 2011 for mac at home.

I have an example spreadsheet, but I don't know if I can upload it here. It makes far more sense than I can ever hope to make in using words, but I will do my best to put it in a nutshell:

I want any selection made in a dependent validation list which contains a list of named ranges to trigger a worksheet_change event which copies the range the selection points to and pastes it into a dynamic range in another column, beginning as a specified cell.I've tried using this, put together from some code examples from similar, but different issues.

​

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; ">[COLOR=#00008B]Private[/COLOR] [COLOR=#00008B]Sub[/COLOR] Worksheet_Change([COLOR=#00008B]ByVal[/COLOR] Target [COLOR=#00008B]As[/COLOR] Range) 
 [COLOR=#00008B]If[/COLOR] [COLOR=#00008B]Not[/COLOR] Intersect(Target, [COLOR=#00008B]Me[/COLOR].Range([COLOR=#800000]"B2"[/COLOR])) [COLOR=#00008B]Is[/COLOR] [COLOR=#800000]Nothing[/COLOR] [COLOR=#00008B]Then[/COLOR] 
   Range(Range([COLOR=#800000]"B2"[/COLOR]).Value).Copy 
   Range([COLOR=#800000]"P2"[/COLOR]).Paste  
[COLOR=#00008B] End[/COLOR] [COLOR=#00008B]If
[/COLOR][COLOR=#00008B]End[/COLOR] [COLOR=#00008B]Sub
[/COLOR]</code>

Trouble is, I don't really understand this code. It doesn't appear to do anything when I make a worksheet change in "B2", but I don't know exactly what it is that I don't understand to ask for more specific help. I'm a bit stumped, and wish I could better articulate what help I need. I suspect that perhaps the fact that "B2" is validated from an indirect source might cause headaches?

Maybe there's a simpler way to achieve what I'm trying to do.

Thanks to anyone who can help (or understand what I'm attempting to do)

Phill​

 

[Andrew Poulsom]

Where did you put the VBA code? It needs to be in the module for the Worksheet.

 

[Phill Jones]

Sorry - forgot to say that. Yes, it is in the worksheet module.

 

[Andrew Poulsom]

So how do you know it's not firing? If you put this at the beginning:

MsgBox "Change"

do you see a pop up when you change what's in B2?

 

[Phill Jones]

no, no popup. I don't actually understand how this code is monitoring B2 for a change. The syntax is a bit confusing. I don't really enjoy using code that I don't understand the operation of for this reason.

 

[Andrew Poulsom]

Does this make it clearer?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$2" Then
        Range(Range("B2").Value).Copy
        Range("P2").Paste
    End If
End Sub

How are you changing B2 exactly?

 

[Phill Jones]

B2 is a validation list. Its source is an =indirect which results in a named range itself. The first validation box is for "State". B2 is dependent on what value is in B1. B2 is for "Offices". The items in its list are all named ranges themselves. Hence trying to copy the range and paste it into the dynamic range for the chart. Does that make sense?

Target.address makes sense. So that just means it's looking for target as range, that range being B2. Any change to B2 proceeds with the code. I suppose I ought to put an if not change, exit sub procedure too.

 

[Andrew Poulsom]

You don't need an Else for that scenario. Is the event firing now? If not type this in the Immediate Window in the Visual Basic Editor:

Application.EnableEvents = True

and press Enter.

 

[Phill Jones]

I've got it to fire! *Red face* I had the module on General. Sigh. On Worksheet and Change, it works.

Thanks so much for your help. I'm sure that this won't be my last issue requiring some assistance.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Range(Range("b2").Value).Copy ([P2])
End If
End Sub

This copies the named range into the target as planned.