?
Solved

Dynamic Question Identification Version 2

Posted on 2011-05-04
14
Medium Priority
?
449 Views
Last Modified: 2012-06-21
I have this great little macro/app that Dave (dlmille) wrote for me and now that I'm using it I need some slight modifications.

Those modifications would be;

1.) Name change from Scenario to Functional Area
2.) Name change from Questions to Question/Capability Statement
3.) Need to add a column for "Process Scenario" after Functional Area column
4.) Need to add a column for "Performance Indicator" after Functional area

And one minor sorting challenge;

When you choose "industry" without clearing all (i.e. Clear Current), you don't get new "Industry" questions you get the original industry questions. I need a button or choice to "Clear Industry" in the event that you want to collect answers to Cross Industry Questions or retain Industry until (as is the case now) you "Clear All".

That's it!

B. Questions-for-Model-v4-r7.xlsm
0
Comment
Question by:Bright01
  • 6
  • 4
  • 4
14 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35694531
1 - Done
2 - Done
3 - Done
4 - Done

Do you want the new columns in the results list, and does there need to be drop downs on them as well?

Not sure I get the last.  let's get 1-4 first, then check your sorting challenge.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35694567
I'm assuming, yes... Here's the result.  I moved up the criteria range rows to around 10,000 as we were having memory problems at 50,000 - lol

Dave
Questions-for-Model-v4-r8a.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35694593
A bit more testing - use this one.

Dave
Questions-for-Model-v4-r8b.xlsm
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Bright01
ID: 35694720
Dave,

Man you are fast...... Here's a question for you..... can you tell me how I can take your changes and apply them to a modified Workbook?  The challenge I have is that each time I adapt the great code you do, I have to start all over.  And while your changes are "spot on", I have to readapt.   Or I can send you the new file....either way...but I hate (as I'm sure you do...) starting over.

Please advise,

B.
0
 
LVL 10

Expert Comment

by:broro183
ID: 35694884
Dave,
At first glance this looks like very nice code - I appreciate the occasional link to the original sources as well :-)
I'm going to read over it properly tomorrow & see what little tips & tricks I can pick up.

B,
Dave's the best man to answer your question, but if you are trying to learn...

Sometimes the best way to learn what code is doing is to go through it line by line with the [F8] & [F1] keys to help you understand what's happening. It can be a slow road, but give it 6 months & you may find that your questions become fewer & further apart, or, alternatively, they relate to issues of increasing complexity as your knowledge & familiarity of VBA continue to expand.


Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...
0
 

Author Comment

by:Bright01
ID: 35694924
Well, I'm certainly willing to start over.... the code Dave has created works.  I just have a challenge scaling it into the business proposition.

Thx.

B.
0
 

Author Comment

by:Bright01
ID: 35694965
Wow...Dave..... Let me do some work adapting this.  It's spot on.

B.
0
 
LVL 10

Expert Comment

by:broro183
ID: 35695159
Good luck adapting it :-)

Not for points, but for learning.
If you're interested in a range of opinions & general VBA tips you could have a read of:
http://www.excelforum.com/the-water-cooler/744969-dos-and-donts-so-others-wont.html
&/or
http://www.excelforum.com/excel-programming/718395-what-to-avoid-in-vba.html

Rob
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35695388
No need to get bent out of shape thinking you need to rebuild this, as a lot of thought went into this cascading DV approach.

For the interested, the steps to adding a new database column are:

1.  Insert the column, label the database header, the criteria header, and erase the DV temporary area to the right of the criteria.
2.  If there's going to be a DV drop down with that column, insert that column in the output area, and create a yellow DV cell there.
3.  Create a dynamic named range, called "whatEverTheNewFieldNameIs_List" by using existing examples from the existing DV lists.  Test it by pulling it up in the Name Manager, click on the name, then click on the formula - the spreadsheet should be selecting those cells, before you can proceed.
4.  Update the formula in the Criteria Range under the new column created - to address the new yellow DV cell you created.
5.  Minor coding changes - changing reference to "K" range to whatever the last column is in the output (result of DV selection), and finally, incrementing the -7 and +7 references in the FilterRequest subroutine by one for the new field added.  RATHER THAN REPEAT THIS, I HAVE MADE CODE CHANGES, WHERE THIS IS Dynamic.  

Now, no code changes should be required.


Just a few simple steps to cascading DV lists that are dynamic, deal with duplicates, etc.  

This will be a topic of part III of my series (two has yet to be published which shares the DV list technique, and part III would be the cascading approach) - with different options as to data source - I have implemented this approach on > 4 question (not counting the several of Bright01's) and the technique is sharpening.  I've done it with ACTIVEFILTER - this approach, and with ACCESS DB, and also the simple indirect lookup following Contexture's Dynamic lookup - all using the DV utility to eliminate duplicates on the fly - rather than having the user have to manage separate ranges for DV lists, etc.  Each iteration is getting simpler to implement as I adjust the technique, so by the time I post the article, it should be straight-forward with little user interaction, and simple to use/less intimidating.  

@broro - Glad you have interest.

If either one of you like the article http:/A_5062.html, please vote it "Helpful" as this gives me encouragement to develop these techniques more, and sharpen my skills at the same time.

Attached, please find the latest with the coding changes in step 5 to make adding the NEXT column, easier :)


Questions-for-Model-v4-r8c.xlsm
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35695389
@broro - Bright01's a quick study and he's not had difficulties adapting the last few iterations, so its with "good skill" he/she will prevail - doesn't need luck, lol

:)
0
 

Author Closing Comment

by:Bright01
ID: 35716274
Great performance by Dave!

B
0
 
LVL 10

Expert Comment

by:broro183
ID: 35716799
hi guys,

Not for points, but for possibly for general discussion.

Cool, its always nice to see & hear of people who are willing to learn & make the changes themselves :)

Dave,
I've finally had a look through the file attached in this thread & the article (& am posting there too). I like sharing ideas & understanding other's coding styles while developing my own style so please feel free to take the below thoughts "with a grain of salt"!
Here are some thoughts/questions:

- I'm a fan of separating inputs from calculations & outputs, so personally, I would separate the file into 2 separate sheets (but I'm not sure if it is possible with Advanced Filter).

- This may be slightly less readable but here's a toggling alternative to one of the If statements:
    If Err.Number <> 0 Then
        listObjectLinkExists = False
    Else
        listObjectLinkExists = True
    End If
'the above could be written as
listObjectLinkExists = Not CBool(Err.Number)
'which I believe could also be written as
listObjectLinkExists = Not Err.Number

Open in new window


- I'm a big fan of With statements to group actions relating to the same object so I would use them more in some sections of the code. However, I can't say that I have tested any changes for impacts on speed etc... my usual reason for using them is to show how many actions are happening to a specific object/variable. I find it useful when refactoring* because it helps me spot duplicated or contradictory actions etc when I try to perform as many related actions as possible in one go.
*esp when looking at code that I have generated via the macro recorder (on the first run through) and this principle has spilled over into my general coding style.

- I'm also a fan of limiting any changes to the user's setup or changing the normal/expected responses so I would expand Ted's code to the below version. Mind you, I haven't tested it for speed (or in any versions other than excel 2007) and you may consider it un-necessary code-bloat...
 'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
Private Sub TempCombo_KeyDown(ByVal _
                              KeyCode As MSForms.ReturnInteger, _
                              ByVal Shift As Integer)
    With ActiveCell
        Select Case KeyCode
            Case 9    'Tab
                .Offset(0, 1).Activate
            Case 13    'Enter
                If Application.MoveAfterReturn Then
                    Select Case Application.MoveAfterReturnDirection
                        Case xlDown
                            .Offset(1, 0).Activate
                        Case xlToRight
                            .Offset(0, 1).Activate
                        Case xlUp
                            .Offset(-1, 0).Activate
                        Case xlToLeft
                            .Offset(0, -1).Activate
                    End Select
                End If
            Case Else
                'do nothing
        End Select
    End With
End Sub

Open in new window


- To increase transparency of the sheet's functionality I would probably try to modify the code so that the dropdown arrows are always visible.

- In the "Worksheet_SelectionChange" macro, ws is set as the activesheet and "activesheet" is also used later in the code. As the event macro is in the Sheet module I would use "set ws = me" & ensure "ws"  is used in all relevant places where there are intended references to the sheet.

- Also in the Worksheet_SelectionChange" macro, there is a declaration of "Dim str As String". I would change this because "Str" is a VBA function.

as mentioned at the top, feel free to take my comments "with a grain of salt" :-)

Rob
0
 
LVL 10

Expert Comment

by:broro183
ID: 35716807
I forgot to hit the refresh button before posting!

Yes, I agree "Great performance by Dave" :-)
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35717053
Thanks broro - its possible with multiple sheets - and I have done so with other questions.  Bright requested all to be done in same sheet, thus this solution.

Glad you found something to capture your imagination in this one!

Dave
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

809 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