Solved

Macro for separating out a specific range

Posted on 2014-04-08
20
128 Views
Last Modified: 2014-05-15
EE Pros,

I have a simple model/table that I'm constantly adding new rows that are range defined.  In my attached file, I have three separate sections that are identified and expanded by an Outline format using a macro that selects/provides details.  

What I need is a Macro that also allows me to quickly get to and isolate a single FA (range).  I would see it as a form, that includes a drop down box that is linked to a "table" on another WS; where I can add a section name (that will be related to a new FA range name).  

The result is that I should be able to either/both "Select Detail" (already works) and "Select FA" (what the new macro will do) and as I add more and more rows, I can quickly get to the correct FA and the detail, without simply selecting the detail and having to scroll down a huge set of rows.....  

I have attached a file for your review.  The Select FA is the button that has no macro yet.

B.
Navigation-Macro.xlsm
0
Comment
Question by:Bright01
  • 10
  • 10
20 Comments
 
LVL 35

Expert Comment

by:Kimputer
ID: 39990399
I think this is what you mean?
Added Form, Added code to fill combobox, added find code to activate found cell
Navigation-Macro.xlsm
0
 

Author Comment

by:Bright01
ID: 39995482
Close....but here's the trick.   I need to be able to open the dialog box you just produced, select from a table of options (the FAs) and have it go to that FA.  Then when I select the level of detail, it shows the detail only in that FA.  Right now, it goes back to the top.

Thanks for jumping in here.....

B.
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 39995494
Adjusted. I think it works now as you want, except you shouldn't click anywhere except the buttons to get the list you want.
Navigation-Macro.xlsm
0
 

Author Comment

by:Bright01
ID: 39995516
Kimputer,

I continue to get an error (it hangs) when I try to use both functions in conjunction.

Here's the line that is in the debug:  

Set acell = ActiveSheet.Columns(6).FindNext(After:=acell)

Perhaps it's looking through the entire spreadsheet?  I will have over 4000 records eventually (that's why I need the navigation macros)..... but as you have shown, you can build in a "go to" capability based on FA identification in a single Column.

May I suggest some traps to catch errors?

B.
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40009594
I think you press escape (while I always pressed the x). So here's to intercept that (put code in Userform1 code)

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyEscape Then
        Unload UserForm1
    End If
End Sub

Open in new window

0
 

Author Comment

by:Bright01
ID: 40061352
Kimputer,

So sorry for taking so long; I've been around the world on business and just got back.  I have however, been trying to understand your solution and have spent hours trying to figure out how this works in order to adapt it to what I need.  So here are my questions;  Does it use range names to "go to" the location?  That's a problem in my production version since I have so many line items.  Is there a simple way to compare the columns in the Use_Cases sheet (forget the Table Sheet) and for every "FA" list it in a drop down box that you can select as a "go to" location?  Then from that go to location, you can then use the other macro to display the details?  That would be a much cleaner way to do this then to depend on range names.

Thank you so much for your help with this.

B.
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40061437
Code adjusted to FA being read from file, not from the table in the other sheet (it was my impression that you wanted it that way, reading from your original post).
Please note, if you add more FA items, the dropdown box to select FA's won't reflect this, until you save the Excel file and start again.
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40061440
Keep forgetting to press Upload File (because Gmail automatically attached it after you chose your file).
Navigation-Macro--2-.xlsm
0
 

Author Comment

by:Bright01
ID: 40061649
Kimputer,   Thank you so much!  I'm still getting an error in the code so I'm uploading the actual workbook I have.  You will see when I fire your macro, I get a debug error.

Thank you again,

B.
Use-Case-Worksheet.xlsm
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40061676
This code only works if you really fill in something behind FA (ALL of them!). Also, it doesn't work until you actually save the file after you make a change to that sheet.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Bright01
ID: 40063151
So I have my production model filled out (had to erase the content for security and confidentiality).  But even when I do, and after I have saved it, I still get this same error;  Method Open as Object_Recordset Failed

On this line:  objrs1.Open "Select * FROM [Use_Cases$] WHERE F6=""FA""", objExcel, adOpenStatic
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40063922
Do this in the excel file > ALT + F11 > Tools > References > scroll down to Microsoft ActiveX Data Objects (take the latest version you see, probably 2.8 or 6.1). Check the box  > OK > Save > run file again.
0
 

Author Comment

by:Bright01
ID: 40064173
So did it.  Checked 6.1; saved the file. Shut down Excel.  Restarted, brought file up, clicked on the macro..... same error.

Sorry.  Other options?

b.
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40064420
Yes, change the whole sub:

Sub ShowForm2()

With UserForm1.ComboBox1
 .Clear
Dim counter, i As Integer
counter = Sheets("Use_cases").UsedRange.Rows.Count
For i = 5 To counter Step 1
    If Sheets("Use_cases").Cells(i, 6).Value = "FA" Then
        .AddItem Sheets("Use_cases").Cells(i, 7).Value
    End If
Next

 .ListIndex = -1
 End With
 
UserForm1.Show
 
End Sub

Open in new window

0
 

Author Comment

by:Bright01
ID: 40064472
Kimputer,

Thank you so much!  IT works.  Except for one little problem.  When you select the FA, and then you select the Level of detail, it should not go back to the top.  The reason for the navigation buttons you have programmed, is to drill down on the detail.  If it takes you back to the top, it negates the reason for the macros.  Is that an easy fix?  Like with a "activate" command to stay with the location that you have navigated to?

Thank you again,

B.
0
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 40064513
You have written this code yourself?
Try to remove it the Range("A3").Activate command (from all the button actions)
Sub Expand_DetailsofUseCases()
'    ActiveSheet.Unprotect Password:="jam"
    ActiveSheet.Outline.ShowLevels RowLevels:=4
    '    ActiveSheet.Protect Password:="jam"
'   ActiveSheet.EnableSelection = xlUnlockedCells
    Range("A3").Activate
End Sub

Open in new window

0
 

Author Closing Comment

by:Bright01
ID: 40064545
GREAT ASSISTANCE!  Delay was my fault but Kimputer did an outstanding job sticking with me the whole way.  Really appreciate the support and help.  Several minor things need adjusting but I'll be authoring another question to get those things completed.  

Great job Kimputer.

B.
0
 

Author Comment

by:Bright01
ID: 40065126
Kimputer, quick question for you.  I deleted the Range("A3").Activate statements, but here is my question:  I think there is one line that will insure that when the macro fires, it will stay on that line?
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40066683
For every button where you deleted the Range("A3").Activate code, use this:

Sub Expand_UseCases()
    Dim temp
    temp = ActiveCell.Address
'    ActiveSheet.Unprotect Password:="jam"
    ActiveSheet.Outline.ShowLevels RowLevels:=3
    '    ActiveSheet.Protect Password:="jam"
'   ActiveSheet.EnableSelection = xlUnlockedCells
    Range(temp).Activate
End Sub

Open in new window


Because selecting FA will activate that cell, when you start this routine, it will save this cell, then expand how you want it, then jumps back to the selected FA.
0
 

Author Comment

by:Bright01
ID: 40067008
Perfect!  Thank you again!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

707 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

18 Experts available now in Live!

Get 1:1 Help Now