We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
13 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Macro for separating out a specific range
Posted on 2014-04-08
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
10-
10
20 Comments
Active today
I think this is what you mean?
Added Form, Added code to fill combobox, added find code to activate found cell
Navigation-Macro.xlsm
Added Form, Added code to fill combobox, added find code to activate found cell
Navigation-Macro.xlsm
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.
Active today
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.
Active today
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.
Active today
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.
Active today
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
Active today
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
Active today
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.
Active today
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.
Active today
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?
Active today
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.
Featured Post
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses
Course of the Month13 days, 2 hours left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.