Solved

Implementing Dynamic DV! into existing workbook

Posted on 2012-03-26
20
313 Views
Last Modified: 2012-08-14
I'm trying to incorporate Dynamic DV! into an existing workbook per EE article:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_6429-Part-II-Drop-Down-List-with-Unique-Distinct-Values-ComboBox-ListBox-and-Data-Validation-List-Bonus.html

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?
0
Comment
Question by:mcnuttlaw
  • 10
  • 10
20 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
>>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.

Cheers,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
>>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
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_6429-Part-II-Drop-Down-List-with-Unique-Distinct-Values-ComboBox-ListBox-and-Data-Validation-List-Bonus.html
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...

Cheers,

Dave
skeletonDynamicDV-r1.xls
0
 
LVL 2

Author Comment

by:mcnuttlaw
Comment Utility
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
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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:http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_6429-Part-II-Drop-Down-List-with-Unique-Distinct-Values-ComboBox-ListBox-and-Data-Validation-List-Bonus.html

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

See attached revised skeleton.

Cheers,

Dave
skeletonDynamicDV-r2.xls
0
 
LVL 2

Author Comment

by:mcnuttlaw
Comment Utility
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.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I think I need to give you some DynamicDV! menu options to ease the ability to turn Dynamic DV off..
0
 
LVL 2

Author Comment

by:mcnuttlaw
Comment Utility
That's a good workaround to turn off DV if editing is needed.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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.

Dave
skeletonDynamicDV-r3.xls
0
 
LVL 41

Expert Comment

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

Dave
skeletonDynamicDV-r4.xls
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 2

Author Comment

by:mcnuttlaw
Comment Utility
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.
0
 
LVL 2

Author Comment

by:mcnuttlaw
Comment Utility
Also, how do I give points to the article?

I gave it a Yes vote but you made mention above of giving points.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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:

TAB, SHIFT-TAB, ENTER, SHIFT-ENTER, SHIFT-UP arrow, and SHIFT-DOWN

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:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_6429-Part-II-Drop-Down-List-with-Unique-Distinct-Values-ComboBox-ListBox-and-Data-Validation-List-Bonus.html


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.

Cheers,

Dave
skeletonDynamicDV-r5.xls
0
 
LVL 2

Author Comment

by:mcnuttlaw
Comment Utility
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.

=SMALL(C4:C11,COUNTIF($C$4:$C$11,0)+1)

Open in new window

0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
Thanks for that, it helped me sort out my error handling was a bit askew.

See attached,

Dave
skeletonDynamicDV-r6.xls
0
 
LVL 2

Author Closing Comment

by:mcnuttlaw
Comment Utility
Very, very helpful.
0
 
LVL 2

Author Comment

by:mcnuttlaw
Comment Utility
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.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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.

Dave
0
 
LVL 2

Author Comment

by:mcnuttlaw
Comment Utility
Cool.  Thanks for the explanation.
0
 
LVL 2

Author Comment

by:mcnuttlaw
Comment Utility
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now