Return to same autofilter setting

[jomili]

I have a workbook which, among other things, has two pages in relationship with each other. On the PAC sheet, I have a chart which displays percentages from the data on the DivReg_PAC sheet. In PAC, the user uses a Data Validation to choose which region they want to look at. Their choice triggers an autofilter on DivReg_PAC.

The PAC viewers can choose in another validation list to go see the DivReg_PAC sheet if they want to see the actual dollar numbers.

My problem occurs when they want to return to PAC via my command button in DivReg_PAC. My current code in the PAC sheet is that whenever the sheet is activated range E1 (my data validation cell) contains a vbnullstring. This is because when the user comes in I don't know what PAC he'll want to see, so use the vbnullstring. But, when the user is coming from the DivReg_PAC sheet, I'd like him to return to the same view he left. Below I have my present code for both sheets. My hope is that someone can help me reconfigure it so the user can return to the same view he left.

Here's the Worksheet Activate code for the PAC sheet:

Please Login or Register  to view this content.

When the user chooses to go to DivReg_PAC, their location is put into cell AA1. When they're ready to leave, they click the command button. Here's the command button code:

Please Login or Register  to view this content.

Thanks in advance for any help.

[watersev]

hi, jomili, if I understood everything right:

1. Do not change E1, N1 on sheet_activate event
2. Put vbNullString for PAC sheet E1, N1 cells through workbook_open event or workbook before close event

[jomili]

Congratulations Watersev, you WERE able to understand my poor explanation.

Making the changes you suggested did the trick, but when I went back to PAC the view of my chart wasn't filtered by what was in E1 until I reselected it, so there still work to do. I don't see anything in my code that would unfilter DivReg_PAC upon leaving, but that's what's happening; the chart displays ALL of the information, not just the autofiltered info, though E1 shows what I last selected.

[jomili]

Update: I forgot, there IS something that changes the view. When I go into the PAC view, I may be coming from any of 15 different worksheets. So, whenver PAC is acitivated, the DivReg_Pac autofilter is removed, so that the values in colmumn A can be used (after an advanced filter is applied) to create the Divs3 named range, which fuels the options in the PAC data validation list.

So, I'd need something in the PAC worksheet_Activate that would recognize that we're coming from the DivReg_Pac sheet, and NOT to remove the autofilter or redo the Div3 named range.

[watersev]

, well it was a quess, can you post sample workbook (data is not important, just the structure with sheet objects) with a couple of sheets?

[jomili]

I'll try. It's pretty big, so I'll have to really pare it down. Might not have it until tomorrow.

[watersev]

strip down everything to reproduce exact bottleneck for the issue, that's it

[jomili]

Okay, I stripped it as much as I could, while still trying to retain most of the functionality. You navigate through by choosing the options in the blue region to the right of the chart. You select the options you want to see in your view by selecting from the options on the left of the chart.

Ideally, I want to be able to open the tool and have it set to nothing (no data, blank chart) or all of the information (no selection). From there the user should select whatever they want to see, and the view fills. When they go into Details, and then return, or when they go to another view, or return, I'd like the same selections to autofill, so wherever they go in the workbook they'll have the same material on view, just a different level of detail, but they'll also have the option to change their selections on the fly.

Sorry it's so complicated and clunky. Any help on making it better is greatly appreciated.

Thanks,
John

[jomili]

Still wanting help on this one.

[jomili]

Well, problem solved. I found some neat code out here: http://www.ozgrid.com/forum/showthread.php?t=76618
which helped me solve the problem. My functioning code is shown below:

Please Login or Register  to view this content.