Excel 2010 instr(1,something, NamedRange) or Data Validation solution

[PWD]

Good afternoon. I have a workbook where the user can make a selection then produce a chart. My 'problem' is how to ensure that there is a valid value in a cell (F13) dependent on the selection in cell F9 before the user clicks a button to produce a chart. For example if F9 = “By Brand” then F13 will show a list of all the brands, e.g. “Ford”, Mazda” etc. If F9 = “By Type” then F13 will show a list of all the types, e.g. “Saloon”, Hatch” etc.

What I want to error-check is, say, when F9 = “By Brand” & F13 = “Ford” but then the user changes F9 to “By Type” but doesn’t re-select the type in F13 before clicking the ‘Chart’ button. There isn’t a “type” called “Ford” so I want to put some checking in the code to ensure that F13 & F9 are compatible.

Each drop-down/list is selected from a named range on Sheet “Ref”, e.g. “TAB_Brands”.

So I tried putting together some code to test whether F13 is in the list as defined by F9:-

    Select Case Sheets("Chart").Range("F9")
    
    Case "By Brand"

    MyTest = Sheets("Chart").Range("F13")
    MyRange = "TAB_Brands"

If InStr(1, MyTest, MyRange) Then

Always results in 0.

The way round that I’ve found is:-

        MyRangeCount = Sheets("Ref").Range("TAB_Brands").Rows.Count

        For x = 1 To MyRangeCount

        If InStr(1, MyTest, Sheets("Ref").Range(MyRange).Rows(x)) Then GoTo Line10

        Next x
        
        MsgBox "Mis-match in Chart selection"
        Exit Sub
.
.
.
.
.

Line10:
        Select Case Sheets("Chart").Range("F11")
        Case "Monthly"
.
.
.

I also know that “GoTo” is frowned upon in these circles so I’d appreciate a pointer on that. BTW the data isn’t actually about cars so there are actually two possible types under “Brand” – say “UPVC” & “Wood”.


Many thanks,
D€$

 

[SkipVought]

Hi,

You DISABLE the GO BUTTON, until the user makes the proper selections.

That means that if the user 'backs up' to select a different category, then all dependent lists must be cleared until the selections have been made.

So if the user selects By Type, then the dependent selection list must be reassembled, presumably via a query to populate the dependent control with a list of TYPES, and until a type is selected the BUTTON remains disabled.

I would not use a procedure like you have posted. I would use EVENTS on your sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Replace this:
GoTo Line10
with this:
Exit For

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

If you use dynamic names, try:
MsgBox Evaluate(Range("F13").Validation.Formula1).Name.Name = "TAB_Brands"

combo

 

[PWD]

Hi Skip, like most things I do this is something I’ve inherited.

This is the Data Validation for cell F13:-

=IF($F$9="By Brand",IF($F$12="UPVC", UPVC_Brands,TAB_Brands),Measure)

Where “Measure” is also a named range.

Sounds like a good idea – got to go for the day now but will attack this again tomorrow.


Many thanks,
D€$

 

[PWD]

Thanx guys. First thing I've done is just tweak it - until I can figure out how to activate Skip's suggestion - to get rid of the GoTo.

        For x = 1 To MyRangeCount
        
        If InStr(1, MyTest, Sheets("Ref").Range(MyRange).Rows(x)) Then InstrCount = InstrCount + 1
        
        Next x
        
        If InstrCount = 0 Then
        MsgBox "Mis-match in Chart selection"
        Exit Sub
        End If 'If InstrCount = 0 Then

Many thanks,
D€$

 

[PWD]

Well, I've learnt something today:-

Private Sub Worksheet_Change(ByVal Target As Range)

Trouble is, there doesn't seem to be any way of controlling the button. I know what it's called:-

Sheets("Chart").Shapes("Round Rectangle 2")

but, well, that's about it!

BTW, have I done something daft as I don't get any options after the ".", e.g.

Sheets("Chart").

Many thanks,
D€$

 

[PWD]

Auto list members is checked - and always has been . Grrr.

Many thanks,
D€$

 

[combo]

Trouble is, there doesn't seem to be any way of controlling the button.

It's not a button, it's event raised by excel when you change specific worksheet. And the Target is the changed range. You can test its address and ignore it or react.
Basically, you need to ensure that F13 was changed after F9. You can use global flag to test it. Additionally, if you use validation, you have to check if any of those two cells is not empty.

combo

 

[SkipVought]

Process example:

I have an application that summarized data based on whether it is Type A or Type B data. Type A data is summarized from one database and Type B from another.

That is done in a Data > Validation -- LIST cell, and when then user makes a selection in DV1, the worksheet_change event fires and runs a query that is the list for a second Data > Validation --LIST of items related to either Type A or Type B data. This process clears the 'selected' value in DV2, requiring the user to make a selection.

When the user makes a selection in DV2, the worksheet_change event runs another query to summarize the data for those selections. The process could drill down as deep as required.

The coding is prety simple, preventing recursive event calls while the code runs.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

OK, I've only really got as far as using the code that I have behind the "Click For Chart" button & put it in the code for the Sheet 'Code' to clear the 2nd selected value when it is incompatible with the 1st selected value:-

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyTest As String
Dim MyRange As String
Dim MyRangeCount As Double
Dim InstrCount As Double

InstrCount = 0

If Range("F13") <> "" Then

    Select Case Sheets("Chart").Range("F9")
    
    Case "By Brand"
    MyTest = Sheets("Chart").Range("F13")
    
    If Sheets("Chart").Range("F12") = "Tablet" Then
        MyRangeCount = Sheets("Ref").Range("TAB_Brands").Rows.Count
        MyRange = "TAB_Brands"
        
        For x = 1 To MyRangeCount

            If InStr(1, MyTest, Sheets("Ref").Range(MyRange).Rows(x)) Then
            InstrCount = InstrCount + 1
            Exit For
            End If
        Next x 'For x = 1 To MyRangeCount
        
        If InstrCount = 0 Then Range("F13").ClearContents
        
    Else: MyRangeCount = Sheets("Ref").Range("SMRT_Brands").Rows.Count
        MyRange = "SMRT_Brands"
        
        For x = 1 To MyRangeCount
        
            If InStr(1, MyTest, Sheets("Ref").Range(MyRange).Rows(x)) Then
            InstrCount = InstrCount + 1
            Exit For
            End If
        
        Next x 'For x = 1 To MyRangeCount
        
        If InstrCount = 0 Then Range("F13").ClearContents

    End If 'If Sheets("Chart").Range("F12") = "Tablet"

    Case "By Measure"
    
    MyTest = Sheets("Chart").Range("F13")
    
        MyRangeCount = Sheets("Ref").Range("SMRT_Measure").Rows.Count
        MyRange = "SMRT_Measure"
        
        For x = 1 To MyRangeCount
        
            If InStr(1, MyTest, Sheets("Ref").Range(MyRange).Rows(x)) Then
            InstrCount = InstrCount + 1
            Exit For
            End If
        
        Next x 'For x = 1 To MyRangeCount
        
        If InstrCount = 0 Then Range("F13").ClearContents

    End Select 'Select Case Sheets("Chart").Range("F9")

End If 'If Range("F13") <> "" Then

ActiveSheet.Calculate

End Sub

Interestingly the code is fired again when it performs

Range("F13").ClearContents

Hence my need for

If Range("F13") <> "" Then

So far, so good - maybe.

What I need to do now is have my button (existing or a new one, I don't mind) grey (gray) out and disabled when F13 is blank. Any ideas, please?

Many thanks,
D€$

 

[SkipVought]

Use the Worksheet_Change event to test the value of F13.

Post your code if you need help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Well, I think I've got something that does what I want:-

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyTest As String
Dim MyRange As String
Dim MyRangeCount As Double
Dim InstrCount As Double
Dim oActive As Worksheet
 
Set oActive = ActiveSheet

oActive.Shapes("Rounded Rectangle 2").Visible = msoFalse 'Hide 'Chart' Button to start with
 
InstrCount = 0

If Range("F13") <> "" Then

oActive.Shapes("Rounded Rectangle 2").Visible = msoTrue
.
.
.

Do the compatibility checking

        If InstrCount = 0 Then
        Range("F13").ClearContents
        oActive.Shapes("Rounded Rectangle 2").Visible = msoFalse
        End If

and hide the button again if incompatible. I think this will get me out of jail (gaol).
Thanx for the help - as always!!

Many thanks,
D€$

 

[SkipVought]

You could decrease the number of variables & memory required...

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim MyTest As String
    Dim MyRange As String
    Dim MyRangeCount As Double  '[b]COUNTS ought to be an integer data type like [highlight]Integer[/highlight] or [highlight]Long[/highlight][/b]
    Dim InstrCount As Double    '[b]COUNTS ought to be an integer data type like [highlight]Integer[/highlight] or [highlight]Long[/highlight][/b]
     
    With Target.Parent
    '[b]I would RENAME this shape to something like [highlight]Chart Button[/highlight][/b]
        .Shapes("Rounded Rectangle 2").Visible = msoFalse 'Hide 'Chart' Button to start with
         
        InstrCount = 0
        '[b]Notice that ALL sheet objects, even ranges, ought to reference the sheet[/b]
        If .Range("F13") <> "" Then
        
            .Shapes("Rounded Rectangle 2").Visible = msoTrue
        
        End If
    
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Doh! Not sure why I did that!!!

Yeah, again it's an inherited workbook thing - but I reckon I will do that. Heaven knows why the original person didn't!

Oh, I hadn't thought about "With Target.Parent"

Many thanks,
D€$

 

[SkipVought]

Just threw that in for grins. Activesheet would work just as well.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}