• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Set Data Validation Box to top value within search criteria

A

alexsheehan

Member
Hi there


I have a SearchBox (actually just a cell with a named range of "SearchBox") in my spreadsheet which allows the user to type a search term (obviously) which then restricts the results within my DataValidation drop-down cell to those items that meet the search criteria. Now what I want to achieve is to be able to set my validation box to the first item in the list that meets the search criteria UNLESS the user manually selects an item.


To break it down a little easier;-


I can manage to get the Validation Box to only show items that meet the criteria in my search box, however, as soon as someone searches for an item, I want the top item in the search list to display automatically at the top of the DataValidation drop down.


I can produce this effect myself with some code, however the problem I am having is when I manually change my DataValidation drop-down to another value, the code fires again automatically resetting the DataValidation drop-down back to the top value. Now I don't want it to do this, I want the code to fire as soon as the user searches for something, but then to stop until the next time the user searches for something.


Here's my code;-

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'
If Range("SearchBox").Value = "" Then
End If
Exit Sub

If Range("ValidationBox").Value <> Range("ValidationBoxFirstValue") And Range("SearchBox").Value <> "" Then
Range("ValidationBox").Value = Range("ValidationBoxFirstValue")
End If

End Sub
[/pre]

Maybe I need to use the code outside of the Worksheet_Change event? Please let me know if this makes sense or not, as I have been known to ramble!


Thanks folks


Alex
 
Yep, you've got the right idea, just needed to know how to "turn off" the event trigger.

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'
If Range("SearchBox").Value = "" Then
'I think there was a typo here...your posted macro
'would have caused the sub to always exit
Exit Sub
End If

If Range("ValidationBox").Value <> Range("ValidationBoxFirstValue") And Range("SearchBox").Value <> "" Then
'We're going to make a change here, so turn off the event macros
Application.EnableEvents = False
Range("ValidationBox").Value = Range("ValidationBoxFirstValue")
'don't forget to turn them back on!
Application.EnableEvents = True
End If

End Sub
[/pre]
 
I have one more option.. by restricting your code only when change in sheet happens with only "Searchbox", and not when manually change in drop down cell..


this is done as below:

'code to check if it is any change in search cell..

If Not Intersect(Target, Range("Searchbox")) Is Nothing Then


'your normal code.. just put exit sub inside if condition.

If Range("SearchBox").Value = "" Then

Exit Sub

End if


If Range("ValidationBox").Value <> Range("ValidationBoxFirstValue") And Range("SearchBox").Value <> "" Then

Range("ValidationBox").Value = Range("ValidationBoxFirstValue")

End If


End If
 
Ah thanks very much Luke, that's solved one problem and it definitely works better..., but...


...if nothing is entered in the search box (if the search box is cleared with a "Clear Search" button), it will correctly reset the ValidationBox to the ValidationBoxFirstValue AND then correctly allow me to manually select another item from the ValidationBox without firing the code again. Unfortunately though, if I search for something using the SearchBox, although the ValidationBox correctly displays only items that meet the criteria in the SearchBox and correctly resets itself to the top value (the value in ValidationBoxFirstValue), as soon as I select another item from the ValidationBox, the code seems to fire again, changing the ValidationBox back to the ValidationBoxFirstValue again.


To clarify, this problem is only happening when I am searching for something, if the SearchBox is left blank, then everything works correctly.


Where am I going wrong? I imagine it is something to do with the "Range().Value(s) above or I need to input another If/And/Then statement somehwere? Is this correct?


Cheers for your help in advance and please let me know if I need to clarify.


Alex
 
If the code only needs to go off when the Searchbox is used, I'd suggest prasaddn's method:

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Is the change something we care about?
If Intersect(Target,Range("Searchbox")) Is Nothing Or Target.Count >1 Then Exit Sub

If Range("SearchBox").Value = "" Then
'I think there was a typo here...your posted macro
'would have caused the sub to always exit
Exit Sub
End If

If Range("ValidationBox").Value <> Range("ValidationBoxFirstValue") And Range("SearchBox").Value <> "" Then
'We're going to make a change here, so turn off the event macros
Application.EnableEvents = False
Range("ValidationBox").Value = Range("ValidationBoxFirstValue")
'don't forget to turn them back on!
Application.EnableEvents = True
End If

End Sub
[/pre]
 
Sorry, only just spotted Prasad's reply above (I must have posted my message just after Prasad and not bothered to check if anyone had replied whilst I was writing my message!).


Thanks for your effort Prasad, unfortunately your code does not work at all for me, in that neither when the search box is blank or if it contains data, does the code work. The code fires, but nothing happens with the ValidationBox when it should. By that I mean that the ValidationBox still changes it's values according to the search criteria, but the top value of the ValidationBox (the value which should be pulled through from ValidationBoxFirstValue) remains as the last value selected, even when using different search criteria. That is an awful explanation, after reading this back, I have just realised how useless I am at trying to explain things!!!!


But hoepfully you get the idea?
 
I am so sorry, that it did not work.. can you paste your code again with changes made, so that we debug it..
 
Sorry Prasad, I didn't mean to sound so rude! I am very grateful for your help and your efforts, it's just that on this occasion I could not get your code to work for me (maybe it's just me being stupid though?). Here is my code;-

[pre]
Code: 
If Not Intersect(Target, Range("Searchbox")) Is Nothing Then

If Range("SearchBox").Value = "" Then
Exit Sub
End If

If Range("ValidationBox").Value <> Range("ValidationBoxFirstValue") And Range("SearchBox").Value <> "" Then
Range("ValidationBox").Value = Range("ValidationBoxFirstValue")
End If

End If

End Sub
[/pre]

To reiterate what I want;-


I want to be able to search for something in "SearchBox" and have the list in "ValidationBox" be restricted to those search criteria (this I can do) and then display the topmost value in my "ValidationBox" range, however if the user then selects something from the (search restricted) "ValidationBox", I want the "ValidationBox" to then display the value the user has selected without triggering the "ValidationBoxFirstValue" event (need help with this). Also, if the "SearchBox" is cleared, I want the "ValidationBox" to automatically reset itself to display ALL values in the "ValidationBox" range (this I can do) and then display the topmost value in "ValidationBox" range, however as above, if the user then subsequently selects anything from the "ValidationBox" drop-down, the code will not fire and the value in the "ValidationBox" drop-down will stay on what they have selected (need help with this).


I hope I haven't confused you!


Thanks a lot.
 
Alex,


In the code I posted before, you'll notice the line about setting

Application.EnableEvents = False


This is a critical line when you are dealing with event macros.
 
Hi again Luke,


I did try your code above;-

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'
If Range("SearchBox").Value = "" Then
'I think there was a typo here...your posted macro
'would have caused the sub to always exit
Exit Sub
End If

If Range("ValidationBox").Value <> Range("ValidationBoxFirstValue") And Range("SearchBox").Value <> "" Then
'We're going to make a change here, so turn off the event macros
Application.EnableEvents = False
Range("ValidationBox").Value = Range("ValidationBoxFirstValue")
'don't forget to turn them back on!
Application.EnableEvents = True
End If

End Sub
[/pre]

But this is where I experience the problem where, if there is something entered in the "SearchBox", my "ValidationBox" correctly shows the 'search restricted' values, however, whenever the user selects anything from the "ValidationBox" that is not equal to the value in "ValidationBoxFirstValue" then "ValidationBox" resets itself to "ValidationBoxFirstValue" all the time. However I want the "ValidationBox" to stay as whatever the user had selected (until of course they clear the "SearchBox" again)


I hope I'm making sense?
 
Does the user clear the SearchBox before changing the value? If not, we should maybe include that in the code...

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'
If Range("SearchBox").Value = "" Then
'I think there was a typo here...your posted macro
'would have caused the sub to always exit
Exit Sub
End If

If Range("ValidationBox").Value <> Range("ValidationBoxFirstValue") And Range("SearchBox").Value <> "" Then
'We're going to make a change here, so turn off the event macros
Application.EnableEvents = False
Range("ValidationBox").Value = Range("ValidationBoxFirstValue")
Range("SearchBox").Value = ""
'don't forget to turn them back on!
Application.EnableEvents = True
End If

End Sub
[/pre]
 
Ah I see what you've done there, but would this not mean that the "search restricted" values would reset themselves in my "ValidationBox" (making all values available to user) and also because therefore the "SearchBox" is being cleared AND "ValidationBox" is NOT equal to "ValidationBoxFirstValue" that it would go around in a big loop? Triggering the second IF statement? Or is it because the "Range("ValidationBox").Value = Range("ValidationBoxFirstValue")" and "Range("SearchBox").Value = """ events are firing BEFORE "Application.EnableEvents = True" that this will be where the code terminates? I think that's where my brain starts to get fried!!!


I will have to try this tomorrow now as I am back at home at the moment (19.55 local time) and my spreadsheet is at work.


Thank you for your time once again & I'll let you know how I get on.
 
Hmm. Somehow, my code changed slightly from when I first posted it. There should be this line at the beginning:

[pre]
Code: 
If Intersect(Target,Range("Searchbox")) Is Nothing Or Target.Count >1 Then Exit Sub
To make sure that the go only goes off when search box is changed. All together, if we use that line, there's no longer a reason to "reset" the search box:

Private Sub Worksheet_Change(ByVal Target As Range)
'
If Intersect(Target,Range("Searchbox")) Is Nothing Or Target.Count >1 Then Exit Sub
If Range("SearchBox").Value = "" Then
'I think there was a typo here...your posted macro
'would have caused the sub to always exit
Exit Sub
End If

If Range("ValidationBox").Value <> Range("ValidationBoxFirstValue") Then
'We're going to make a change here, so turn off the event macros
Application.EnableEvents = False
Range("ValidationBox").Value = Range("ValidationBoxFirstValue")
'don't forget to turn them back on!
Application.EnableEvents = True
End If

End Sub
[/pre]
 
Thanks Luke, I think we are getting there now - the code works a little better now in that when I search for something, the ValidationBox has it's values restricted as normal and the top value is displayed automatically AND now when I select another value from the "search restricted" ValidationBox (and this value is not currently the ValidationBoxFirstValue), the ValidationBox now stays on the option I have selected. GREAT STUFF!


The only slight problem now is that when I now clear the "SearchBox", the ValidationBox does not automatically jump/reset to the ValidationBoxFirstValue, basically displaying whatever value in the ValidationBox was last selected by the user.


This is now only a very minor (mainly cosmetic) error now, in that I would LIKE to have the ValidationBox jump to the ValidationBoxFirstValue when the "SearchBox" is cleared (and then still allow the user to select any value they want from the ValidationBox without resetting to ValidationBoxFirstValue) but it is not a major problem and I can work around it for now - it would just "feel" better having it display the ValidationBoxFirstValue.


Once again, I hope this makes sense?


Thanks very much for your patience!
 
It sounds like then that no matter what change is done to SearchBox (clearing it, putting new text in), the validation box needs to change? If so, remove the second If block from the code.

[pre]
Code: 
'Remove this section
If Range("SearchBox").Value = "" Then
'I think there was a typo here...your posted macro
'would have caused the sub to always exit
Exit Sub
End If
[/pre]
 
DOH! That makes perfect sense actually and I don't know why I didn't spot that myself! ("can't see the wood for the trees" springs to mind).


I will try this tomorrow as I am back home again at the moment. Thanks for your help. Sorry for being a thicko!
 
You must log in or register to reply here.
Back
Top