Bright01
asked on
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
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
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
Dave
Questions-for-Model-v4-r8a.xlsm
ASKER
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.
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.
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...
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...
ASKER
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.
Thx.
B.
ASKER
Wow...Dave..... Let me do some work adapting this. It's spot on.
B.
B.
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/744 969-dos-an d-donts-so -others-wo nt.html
&/or
http://www.excelforum.com/ excel-prog ramming/71 8395-what- to-avoid-i n-vba.html
Rob
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/
&/or
http://www.excelforum.com/
Rob
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:
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
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great performance by Dave!
B
B
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:
- 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...
- 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
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
- 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
- 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
- Also in the Worksheet_SelectionChange"
as mentioned at the top, feel free to take my comments "with a grain of salt" :-)
Rob
I forgot to hit the refresh button before posting!
Yes, I agree "Great performance by Dave" :-)
Yes, I agree "Great performance by Dave" :-)
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
Glad you found something to capture your imagination in this one!
Dave
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