• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

Dynamic Question Identification Version 2

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
Bright01
Asked:
Bright01
  • 6
  • 4
  • 4
1 Solution
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
A bit more testing - use this one.

Dave
Questions-for-Model-v4-r8b.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Bright01Author Commented:
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
 
broro183Commented:
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
 
Bright01Author Commented:
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
 
Bright01Author Commented:
Wow...Dave..... Let me do some work adapting this.  It's spot on.

B.
0
 
broro183Commented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
@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
 
Bright01Author Commented:
Great performance by Dave!

B
0
 
broro183Commented:
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
 
broro183Commented:
I forgot to hit the refresh button before posting!

Yes, I agree "Great performance by Dave" :-)
0
 
dlmilleCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now