Data Validation and Conditional Formatting using VBA

I am using Data Validation for my drop down list, using “List” and “source” =$A$132:$A$159 along with Conditional Formatting that changes different fill colors, depending on selection. Is there a way to use VBA with a command button that when selected would give the user a dialog box so that they could enter additional comment that would be added to existing list and would also be able to select a fill color for that new selection.

Below I have a recorded Marco to give an idea of what I would like to achieve, but of course using a input box of some sort to make selections of text and color
Also I'm using Excel 2007

Please Login or Register  to view this content.

Does anyone have any suggestion
it doesnt have to use conditional forrmatting, but I do need it to enter new text by added to existing Data Validation list

Does anyone have any suggestion
It doesn't have to use conditional formatting, but I do need it to enter new text by added to existing Data Validation list

One possible approach:
Use either the Find Method or Countif function to determine if the new entry is in the current list of items. If the new entry does not exist, write it to the list. This action needs to occur the worksheet_Change event with the code targeted to the specific data validation cell(s).

Your Data validation list should be based on a dynamic named range so that it can automatically accommodate new entries.

The approach I suggested will work. See the attached example.

In the sample workbook . . .
A dynamic named range was created for the validation list.
Data Validation, list option, Error Alert set to Inform

This code will check if the new entry is already in the list, if not then it will add it.

Please Login or Register  to view this content.

You cannot use the same approach for deleting items from the list. See attached workbook for example.

There is a button to show a form with listbox of validation list items.
Multiple selections in the lixt box may be made so as to delete several items at once.

Note: a slight change was made to the dynamic named range for the validation list. It was given a header and this revised formula (below). Purpose: to prevent Excel from corrupting the named range formula when the top list item (anchor cell) is deleted. This new formula retains one cell in the dynamic named range, but the header cell is excluded from the list of items.
=OFFSET(Sheet1!$L$1,1,0,COUNTA(Sheet1!$L:$L)-1,1)

Here is the code assigned to the button:

Please Login or Register  to view this content.

Or is it possible to enter new text using a right click event this way I could still enter additional text within the cell using the left double click event that will not save to name list, because this is sometimes needed to add a simple comment to existing populated text. And when I want text to be added to name list I would just right click and then enter new text. What do you think is this possible?

It is possible, though not recommended as it is, IMO, inefficient. Consider . . .
In order to use the BeforeRightClick event, after making an entry into the cell you with have to
press the tab or enter key to exit edit mode, which takes you out of the cell, or
Click the accept button in the formula bar, requiring an extra mouse move/click

That being said, if you want to proceed, then delete the code from the worksheet change event and use this code, which, as before, goes into the sheet module.

Please Login or Register  to view this content.

I believe this is the same workbook as before

It contains amended code, which I showed in my last post. However, just to make certain, I removed the attachment and uploaded it again.

Hi Palmetto
I thank you so very much for your time and wisdom. I shall play around with what you have given me and see how it works in my applacation, I will update my results or possible questions. Also I can not see your attached workbook sample? maybe from my end.
Again thank you

Also I can not see your attached workbook sample? maybe from my end.

I did not upload a workbook for the last post regarding the use of the BeforeRigthClick event. Simply replace the code as I explained.

I see no reason not stick with the original data validation option/code I gave at the beginning. I wouldn't say you are "reaching for straws", but the goal should be, IMO, to use the simplest method possible that gets the job done.

The input box approach, I think, is not the best, but it is possible
Rather than chasing the wind and attempting to code for changing requirements, give some thorough thought to what you really want to achieve.

The workbook in post #5 does not contain the additional code given in post #8 (which was a response to your additional request to delete items). The workbook in both of those posts includes the code given in post #5.

Glad to help.

Don't forget to leave feedback / add to the reputation of those who contributed a response you found helpful. See my signature for how to go about it.

seakitnruth,

a) unless you're offering some profound insight otherwise not covered in a SOLVED thread please do not post into it

b) do not post questions into other people's threads (this has been mentioned to you in your other posts) - if your post is not directly related to the specifics of the question in the OP you should not be posting to it.

c) use CODE tags for VBA not QUOTE tags


On a final note if you have a file you wish to be critiqued...

i) post in the Development Forum

ii) attach the file to your posting such that it is located on the Board directly


Please endeavour to first familiarise yourself with, and subsequently adhere to, the Forum Rules - all of which you agreed to by means of initial registration.