Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Implementing Dynamic DV! into existing workbook

Posted on 2012-03-26
20
Medium Priority
?
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
20 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37769711
>>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 42

Expert Comment

by:dlmille
ID: 37769742
>>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
ID: 37772212
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 42

Expert Comment

by:dlmille
ID: 37772295
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
ID: 37773673
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 42

Expert Comment

by:dlmille
ID: 37773716
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 42

Expert Comment

by:dlmille
ID: 37773862
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
ID: 37774155
That's a good workaround to turn off DV if editing is needed.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37774360
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 42

Expert Comment

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

Dave
skeletonDynamicDV-r4.xls
0
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37779699
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
ID: 37779723
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 42

Expert Comment

by:dlmille
ID: 37779745
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
ID: 37779991
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 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37780175
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
ID: 37780218
Very, very helpful.
0
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37784320
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 42

Expert Comment

by:dlmille
ID: 37784662
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
ID: 37785415
Cool.  Thanks for the explanation.
0
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37821138
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

704 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