Implementing Dynamic DV! into existing workbook

I'm trying to incorporate Dynamic DV! into an existing workbook per EE article:

For the most part, everything works as advertised.

In another post, the author suggested a slimmer approach in integrating their code into an existing workbook which I am interested in learning how to do:

To integrate the utility (after you're satisfied it does what you want), it would only require the copy of a couple of the modules into your workbook, if that's really necessary, to eliminate the "add-in" effect.

In addition, I have a few more questions...
does this work in older versions of Excel (2007, 2003, etc)?
it appears that the resulting font is larger than the surrounding font.  In other words, the drop down font size appears to be 16 whereas my workbook is at the default 12.  Any way to control the font size of the drop down?
the article makes mention the use of ActiveX.  Could the user via their security settings, disable Dynamic DV!'s from functioning if they disable ActiveX on their system?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

>>does this work in older versions of Excel (2007, 2003, etc)?

It has been designed and tested to work in Excel (2002, 2003,2007,2010).

>>it appears that the resulting font is larger than the surrounding font.  In other words, the drop down font size appears to be 16 whereas my workbook is at the default 12.  Any way to control the font size of the drop down?

Interesting question, and the answer is YES.

Add a public module to the DynamicDV! addin:
Sub setCBFont(cbobj As OLEObject, target As Range)

    With cbobj.Object.Font
        .Name = target.Font.Name
        .Size = target.Font.Size
        '.Bold = target.Font.Bold
        '.Italic = target.Font.Italic
        '.Strikethrough = target.Font.Strikethrough
        '.Underline = target.Font.Underline
        '.Weight = target.Font.Weight
    End With
End Sub

Open in new window

And, in the DataValidationAndUniqueHandling module, change the code section for the DataValidationManagement() routine as follows:


            With cboTemp
                'show the combobox with the list
                .Visible = True
                .Left = target.Left 'Cells(1, 1).Left
                .Top = target.Top 'Cells(1, 1).Top
                .Width = target.Width + 5 'Cells(1, 1).Width + 5
                .Height = target.Height + 5 'Cells(1, 1).Height + 5
                .ListFillRange = "'" & Evaluate(str).Parent.Name & "'!" & Evaluate(str).Address ' modded
                .LinkedCell = target.Address

                Call setCBFont(cboTemp, target)
            End With


Open in new window

And you should have matching fonts - the same as the target cell where the DV range resides. (Advise if you need assistance making these changes - these are good and I'll incorporate in the next version!)

>>the article makes mention the use of ActiveX.  Could the user via their security settings, disable Dynamic DV!'s from functioning if they disable ActiveX on their system?

Unless they inadvertently disable macros, I'm not sure they can unintentially keep the add-in from working.

Note, DynamicDV! should install after having downloaded and double-clicked the file, then it should be there from that point forward each time Excel is started.

Let me know if you have additional questions.


>>author suggested a slimmer approach ....

Somewhat "slimmer" (less modules, anyway), but you can dedicate the code to a single workbook, as opposed to an add-in.

Attached, find the skeleton workbook you can use.  I'll help you get up and running with it (passes all my very quick tests) but you'll have to note any enhancements that I may post in the main Article
and adjust your code, accordingly, to add those features, if any.  I think if you vote (hint ;) then any posts made will give you an alert.  The other way is to montior/or just post a comment in the article thread.

I personally feel using the Add-In the best of all worlds as you have the benefit of managed code via any updates that I may publish, you can easily take advantage of ( as opposed to having it all embedded in one to many workbooks ).  However, I did promise and here it is - in 3 modules, plus some code in the ThisWorkbook codepage.  (PS - I put the new setCBFont() routine in the DataValidationAndUniqueHandling module.

PS - if you're ever interested, I've done some work around dynamic DV with dependent lists I was looking for someone interested in benefitting from that plus also giving feedback on a more general-purpose utility.  Until then...


mcnuttlawAuthor Commented:
I ported the three modules, single class and ThisWorkbook code into my existing workbook and get an error when clicking on a Data Validation.

Any ideas?

Error Message
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Ahh - porting.  I should have said ou need to add to the Reference library, the Microsoft Scripting Runtime library.

A better alternative for late binding, I created a constant TextCompare=1.

I think I may add this skeleton to the Article:

As it might be useful in unique circumstances, as you may be finding a unique need with yours.

See attached revised skeleton.


mcnuttlawAuthor Commented:
This is introducing me to a whole new world (dictionary, binding).

Okay, so the second skeleton works with late binding (did I say that correctly?).

Now for a couple questions.

- possible to omit editing within the drop down?  As it is now, the user can type in anything in that field that won't necessarily give them the desired results.

- I can't click or arrow into a DV cell to format it.  Example I have a cell highlighted yellow (it was formatted before implementing Dynamic DV) to signify it being a clickable cell; however, now I cannot access the cell to change its highlighting color.
Ensure data validation warnings etc are on and that you don't have any blank cells in your dv range

Turn dynamic dv off to edit the dv cells
I think I need to give you some DynamicDV! menu options to ease the ability to turn Dynamic DV off..
mcnuttlawAuthor Commented:
That's a good workaround to turn off DV if editing is needed.
Its actually the needed approach, and why I included it with the add-in.

I found one instance that I hadn't caught before with respect to data validation alerts.  If the user types something incorrect and then navigates to another DV cell, it works fine, but if the user navigates off, there was no alert.  I've put in code to handle that.

You have two menu options under the DynamicDV! add-in menu - turn it on/turn it off which helps when you want to edit the cells, as with the add-in.

See attached.  Also, please be sure to give some points to one of my posts with the article, so the article gets credit for this question.

Please advise any difficulties.  Note, we have 4 public modules, a class module, and ThisWorkbook which several have had a few modifications.

Sorry - I thought better about how I did the enable/disable DynamicDV!  Please use this attached approach.

mcnuttlawAuthor Commented:
I'm noticing a few things after porting the code into my worksheet.

Clicking on a sheet that has not been "initialized" will bring up the initializing dialog box but also a mystery drop down (see graphic).  Not a big deal but it is noticable only while the dialog prompt remains unanswered and appears in the cell where I clicked the mouse that fired off the dialog prompt to appear.  Press OK and the drop down disappears.

Clicking on new worksheet
Also, I cannot replicate this into a screenshot but I was working on a worksheet and the Data Validation message came up on every single mouse click.  Turning off DV! helped.  The worksheet did have a bunch of formulas on it which may or may not be related.

Lastly, is pressing the Enter key being captured?  In other words, I can use the keyboard to select up/down but pressing Enter does not respond.

Other than that, things are working the way they are supposed to.
mcnuttlawAuthor Commented:
Also, how do I give points to the article?

I gave it a Yes vote but you made mention above of giving points.
1.  On a new sheet, you are notified that Dynamic DV! is in place and when you hit enter on that Message, then the combo box is made invisible.  That's a design convention (originally) as I wanted to ensure the user knew DynamicDV! was functioning

2.  Navigation with DynamicDV! is done via these commands:


You should be able to hit ENTER to navigate through DV list items.

You can award this post to credit the question as well as the article, because I'm posting a link to the article, now:

On the Data Validation messages (sorry, I just wrote that, so perhaps there is a bug) let me know the conditions that caused that to happen (or cause it in the future) and I'll take a closer look - I will be looking anyway as my newly added code was an unintentional omission.

However, it did occur to me to test and if you've invalidly entered data and selected a new sheet or other workbook, recovery might not work quite right without a couple new lines of code, so attached I've tested that particular bend and made updates in the ThisWorkbook code section (which is where the new, newly tested for this question, code is).  If it continues to give problems, you can actually comment the code out and just use the below substitute for the Worksheet_SelectionChange() routine (to at least get work done and advise me while I repair, if needed):

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal target As Range)

        Call DataValidationManagement(target)
end sub

See attached.


mcnuttlawAuthor Commented:
I found a sample situation.

Enter the simple formula below into any cell, press enter (or click in another cell after entering the formula) and the data validation message appears.

The message appears in your latest skeleton.


Open in new window

Thanks for that, it helped me sort out my error handling was a bit askew.

See attached,


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcnuttlawAuthor Commented:
Very, very helpful.
mcnuttlawAuthor Commented:
Another minor question.  I have this line of code:

wksRnk.Range("A" & (i + 3) & ":U" & (i + 4)).Copy

Open in new window

It is firing off the subroutine in ThisWorkbook:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal target As Range)

Not a big deal but I was wondering if it was a problem that it does and somehow have SheetSelectionChange ignore certain "originations".  Hope that made sense.
It is not a problem, unless you are pasting into a DV range.  With the add-in, these cycles happen as well, but at the Application level (as opposed to ThisWorkbook level) so they are less apparent.

Generally, in any application where you're coding VBA, if you're making sheet changes and have event code that is trapping the change to do some operation, you have the choice to turn events off (therefore the traps are inactive) and that's my practice.

So, my advise is when you write code against this workbook, set Application.EnableEvents = False at the start of your code and then Application.EnableEvents = True at the exit of your code, including fall through based on error handling.

On other alternative (if you only want to block one or more but not all event traps), is to create a public boolean variable and set that to TRUE at the start, FALSE at the end, and code an if statement wrapper around all events that matter to not run when that variable is true.

mcnuttlawAuthor Commented:
Cool.  Thanks for the explanation.
mcnuttlawAuthor Commented:
Another nice feature would be to not remain focused on the DV! after making its selection.

In other words, have the DV! disappear after making the selection and let the underlying cell's formatting (which may be colorized) display the data.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.