Worksheet_BeforeRightClick

[ABabeNChrist]

I am using a right click event with code that allows me to populate a name list. (“mylist”) that is used for a Data Validation drop down. This code below works great but I need for it to be used with other Target cells and there assigned name list that are within the same worksheet. When I try to repeat the code and change the range, I get an error. Do I need to assign a reference to cells selection for code or ?
Here are the other target cells with there assigned name list
Target Cell Name list
Q14 Front
E18 Back
H20 Side

Please Login or Register  to view this content.

[shg]

Not sure what you're trying to do; something like this?

Please Login or Register  to view this content.

[ABabeNChrist]

Hi shg
After further review it seems to work
Thank you

[ABabeNChrist]

Hi shg
I was able to get the code you provided to work with my sample workbook great. but when I tried to apply to my workbook I got and error on Cells. Here are the changes I made.
Also if you notice one of my dynamic name list is located in a different sheet. any help will be greatly appreciated

Please Login or Register  to view this content.

[ABabeNChrist]

I went back to my working sample that seemed to work correctly and I then tried to apply using the code method (Case "Q14,Q28,Q42,Q56,Q70,Q84,Q96") in my previous post. I was still unable to get it to work. So I separated each cell even thought they used the same dynamic name list. I then was able to get them to function. I was hoping to use a shorter coding method. Also I have a dynamic name list that is located on another worksheet (Named “Comments”) do I need any additional coding or will it find the dynamic name list.
Any suggestions
Below I attached a sample worksheet.

[shg]

Stopping at the first thing I saw,

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

You're testing to see if the address is one of those strings, right?

[ABabeNChrist]

Hi shg

You're testing to see if the address is one of those strings, right

Yes, I wasn’t sure how to write it, I was just guessing. Because when I used my previous approach it wouldn’t work, so that’s why I separated them. I shall give it a try.
What about if I have a dynamic name list in a different sheet, is there anything I would need to add to code, or just using the dynamic name enough?
Once again I thank you

[shg]

I still haven't divined what your trying to do.

How about posting a workbook and explaining lucidly in context?

[ABabeNChrist]

Hi shg
I have a workbook I use to do reports with. Each sheet within this workbook covers a different area that may or may not be needed to complete that specific report. I use data validation as a form of drop downs with dynamic name list as the source. This method helps save time with retyping. I use the right click event method so that if I wish to add / populate a comment to that existing dynamic name list I can. Most of the dynamic name lists used, are located on the same sheet, I do this because they only pertain to that area of the report. But there are couple dynamic name list that are used on each and every Report (sheet). I have no problem with setting up the dynamic name list to be used on other sheets, I’m just not able to get them to populate like the others that are within the same sheet
When I use a dynamic name list I use code similar to this to refer to

Please Login or Register  to view this content.

With your last reply I was able to correct that concern using the method you suggested on my sample workbook. My original worksheet is much more complex. I figure get the bugs out first…..


Below is a sample workbook

[ABabeNChrist]

I hope I was able to explain in a way for you to understand

[shg]

So your data validation has no stop message, and if you make a non-vailid entry, then right click, you want to add that entry to the validation range for the cell?

[ABabeNChrist]

So your data validation has no stop message, and if you make a non-vailid entry, then right click, you want to add that entry to the validation range for the cell?

Yes I believe that sound right.
I attached a sample workbook to post #9. That shows the code and method similar to what I'm trying to achieve. I was unable to get the dynamic name list "mood" that is located on sheet named "Comments" to add a additional entry to existing list using right click from sheet1 Q14 data validation

[shg]

Try this. I tested it very minimally (one validation range on the sheet, one on another sheet).

The code could use some cleanup, methinks.

EDIT: Also, look at how I defined the validation ranges, using INDEX rather than OFFSET.

[ABabeNChrist]

When Attempting to use the right click event I recieved an error

Compile Error
Can't find Project or library

and the word "Left" is highlighted in the code

Please Login or Register  to view this content.

does this refer to my VBA Project References that I may need to check a certian library
if so ?

[shg]

In the VBE, do Tools > References, and look for one that's ticked (the ticked ones are all at top) that starts with MISSING. Untick it.

[ABabeNChrist]

Hi shg
That is quite impressive, after making correction by Un-ticking the file labeled MISSING, It seemed to work very nicely. I added a few more name list on sheet2 while modifying the formula code for each of the new list. Then added the data validation list in a few random areas of sheet1, using some of the new list names “val3 and val4” along with the ones you provided. It seemed to work great.

I did notice if I were to accidently doubled right clicked it would add an additional copy to list. Can this be stopped?
I thank you for your kindness and patience with me, I’m just a newbie with this kind of stuff, just trying to learn a new trade, I originally work in the construction industry.

[shg]

Maybe like this. If the value is already in the validation list, you'll get the normal right-click menu.

Please Login or Register  to view this content.

[ABabeNChrist]

Hi shg
Wow that works really cool, it even alphabetizes, let not forget the little beep.
Tomorrow I shall put it to the test on my main workbook, so far it’s only been a sample workbook.
I thank you so very much, this is really awesome.

[ABabeNChrist]

Hi shg
I tried to apply to my main workbook, everything seems to work great except for cells that were merged / wrap. Any thoughts? I need the cells merged for layout reasons

[shg]

I have a long and ever-growing list of reasons not to merge cells, and took a solemn oath not to invent solutions to work around them, ABNC, sorry. Perhaps you can change them to Center across selection.

[ABabeNChrist]

I thank you very much for all that you have assisted me with, you have been more than gracious. I know I can use Center across selections for some but not all, I’ll try some different things, again thank you