I saw something like this described in an EF thread.
I'm not sure if it was working or not.
Anyway, I thought I'd have a go at reproducing it ...
The code is self contained in this WS Change Event
Basically, it has a DV List with an option to add new entries
Please Login or Register to view this content.
Seems to cope well with alphabetic and numeric entries, although "dates" go in as text looking like a date.
Enjoy. If you break it, let me know.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Slightly improved version with worked example.
Please Login or Register to view this content.
Regards, TMS
Last edited by TMS; 09-11-2012 at 07:09 AM.
nice idea. just as an alternative here is a class-based version. haven't done a lot of testing but it seems ok. you could use a collection to store the monitors and monitor several ranges in the workbook.
Josie
if at first you don't succeed try doing it the way your wife told you to
Thanks to JP for his alternative approach (and his advice on content)
Version 3 ... well, version 2 of my code but JP has bagged V2
This sets the Target value to the newly input DV entry rather than make it a two stage process.
If [New Entry] is selected and then an existing value input, nothing changes.
Please Login or Register to view this content.
Regards, TMS
@JP: thanks for the alternative approach. It's good to have a practical working example of a class module
Can I ask, why do you allow a target range and loop through it? I can't see how you can make use of that feature. I tend to limit a change event to a single cell and exit if a multi cell range is selected.
Cheers, TMS
why not? :-)
I try to never limit change events to one cell handling because there are numerous situations where a user might reasonably alter more than one cell at a time and I want it handled (copy/paste or ctrl+enter for instance) and not just ignored
I admit it's unlikely for this scenario but you never know
@JP: fair enough
I can't get this to work, I am fairly new to this sort of thing though.
I've copied and pasted the code.
Renamed the sheet to Input
Created the headings
made the list from D2 1-9.
Then in A2 when I do Data validation then put =DV_List it comes up with Unknown source.
I was looking forward to using this on my project, then was thinking would it be just as good if there was an auto V-Lookup when something is added in the columns also!
Where have you pasted the code? It's a worksheet change event so it has to go in the sheet where you want to have the Data Validation.
In this example, both the Data Validation and the Data Validation List are on the same sheet ... that was really just for the convenience of the demonstration.
Did you set up the dynamic named range?
DV_List: =Input!$D$2:INDEX(Input!$D:$D,COUNTA(Input!$D:$D))
Regards, TMS
Yes its on the same work sheet (named Input) But Dynamic named range??????????? have not got a clue! I'm down the library tomorrow to get a book on excel and get up to speed!
Yes both the drop down list and data list was on the same page just like the layout you had, as the sheet I have made HAS to have them both on the same sheet as it runs on the older version of excel in work.
Select the Formula Ribbon, click on Name Manager, click on New
Name: DV_List:
Refers to: =Input!$D$2:INDEX(Input!$D:$D,COUNTA(Input!$D:$D))
PM me your email address and/or a sample workbook (.xls) if you want it to be compatible.
Regards, TMS
I'll have a play when I'm back on the PC tomorrow, I have 3 groups of drop downs repeated 3-4 times across 4 sheets.
Do you think it would be possible for an auto update for a vlookup?
Do you think it would be possible for an auto update for a vlookup?
I have no idea what that means
Regards, TMS
Sorry, what I'm using is a validation list which i'm hoping to include what you have here.
But also in my book, there is a sheet with the same list of products with prices next to it which uses a V lookup to find the product and then bring back the cost of that product.
When I add products to the validation list and to the price list, I have to change the lists accordingly. So seeing what you have done here, would it be possible to create a macro that detects this list and extends automatically when more products/prices are added to the cells that contain the lookup formula?
You should just make the product/price list a dynamic named range.
You're really going to have to post a sample workbook to get a definitive answer. It's not difficult once you know how but it's going to take forever if we keep playing bounce.
Regards, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks