Link to home
Start Free TrialLog in
Avatar of Bright01
Bright01Flag for United States of America

asked on

Macro for separating out a specific range

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
Avatar of Kimputer
Kimputer

I think this is what you mean?
Added Form, Added code to fill combobox, added find code to activate found cell
Navigation-Macro.xlsm
Avatar of Bright01

ASKER

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.
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
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.
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

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.
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.
Keep forgetting to press Upload File (because Gmail automatically attached it after you chose your file).
Navigation-Macro--2-.xlsm
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
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.
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
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.
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.
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

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.
ASKER CERTIFIED SOLUTION
Avatar of Kimputer
Kimputer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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?
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.
Perfect!  Thank you again!