INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"Because of this forum, I continue to WOW! my clients!"
Geography
Where in the world do Tek-Tips members come from?
|
Excel 2010 instr(1,something, NamedRange) or Data Validation solution
|
|
|
PWD (TechnicalUser) |
15 May 12 11:08 |
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:- CODESelect 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:- CODEMyRangeCount = 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€$ |
|
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 (MIS) |
15 May 12 11:35 |
|
|
combo (TechnicalUser) |
15 May 12 11:36 |
If you use dynamic names, try: MsgBox Evaluate(Range("F13").Validation.Formula1).Name.Name = "TAB_Brands" combo |
|
|
PWD (TechnicalUser) |
15 May 12 11:43 |
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 (TechnicalUser) |
16 May 12 5:39 |
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. CODEFor 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 (TechnicalUser) |
16 May 12 9:18 |
Well, I've learnt something today:- CODEPrivate 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:- Quote:
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. Quote:
Sheets("Chart").
Many thanks,
D€$ |
|
|
PWD (TechnicalUser) |
16 May 12 9:31 |
Auto list members is checked - and always has been . Grrr. Many thanks,
D€$ |
|
|
combo (TechnicalUser) |
16 May 12 10:51 |
Quote (PWD)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 |
|
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 (TechnicalUser) |
17 May 12 11:56 |
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:- CODEPrivate 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 CODERange("F13").ClearContents Hence my need for CODEIf 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€$ |
|
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 (TechnicalUser) |
18 May 12 7:32 |
Well, I think I've got something that does what I want:- CODEPrivate 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 CODEIf 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€$ |
|
You could decrease the number of variables & memory required... CODEPrivate Sub Worksheet_Change(ByVal Target As Range)
Dim MyTest As String
Dim MyRange As String
Dim MyRangeCount As Double 'COUNTS ought to be an integer data type like Integer or Long
Dim InstrCount As Double 'COUNTS ought to be an integer data type like Integer or Long
With Target.Parent
'I would RENAME this shape to something like Chart Button
.Shapes("Rounded Rectangle 2").Visible = msoFalse 'Hide 'Chart' Button to start with
InstrCount = 0
'Notice that ALL sheet objects, even ranges, ought to reference the sheet
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 (TechnicalUser) |
18 May 12 12:14 |
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€$ |
|
Just threw that in for grins. Activesheet would work just as well. Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One