Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
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
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
Asked:
10-
10
1 Solution
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.
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
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).Fin
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 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).Fin
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
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.
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.
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
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
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
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.
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
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.
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.
You have written this code yourself?
Try to remove it the Range("A3").Activate command (from all the button actions)
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
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.
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:
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.
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
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.
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 Already a member? Login.
Featured Post
Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
Added Form, Added code to fill combobox, added find code to activate found cell
Navigation-Macro.xlsm