Customizable Custom Views 1

[DarcyNoonan]

Does anyone know if it possible to create a macro that can create a custom view based on criteria that is entered by a user in Excel 97?

 

[DarcyNoonan]

bump

 

[SkipVought]

Hi,

I suppose that you could macro record the print settings, row/column hiding, filter settings and adding a view...

    Rows("5:10").EntireRow.Hidden = True
    ActiveWorkbook.CustomViews.Add _
        ViewName:="View3", _
        PrintSettings:=True, _
        RowColSettings:=True

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DarcyNoonan]

Thank you very much Skip...I will try this out on Monday at work.

 

[DaleWatson123321]

Hi Likwit,

Another option... use of Excel's "Advanced Filter".

Using Advanced Filter, together with directing the user-input to pre-set criteria range(s) referenced by the Advanced Filter, you can very easily generate custom "views".

I would strong recommend not using "Filter-in-Place". Usually the best approach is to set up a SEPARATE sheet to receive the information extracted by the Advanced Filter. By having a separate sheet, there are two main advantages:

1) You don't have to worry about "messing up" your existing data. (The Advanced Filter extracts a "copy" of the data meeting your criteria).

2) On the separate sheet, you can create "pre-set views" - including whatever changes might be required to "customize" the "view" to suit your needs - i.e. different report heading(s), different field headings, with only those fields you require. And you can alter the order of the field names if desired.

Normally such separate "views" are also "pre-set" with formatting and print-settings that are specific to that separate worksheet.

Microsoft has unfortunately not provided adequate help with the use of the Advanced Filter. And there are actually a couple of "bugs" that can surface depending on your version of Excel.

One bug is a "false" ERROR message that tells you it's not possible to extract data to a separate sheet. Ironically, it's the ERROR message itself that prevents this. Using VBA, there is no problem.

The other bug is another "false" message that informs you that you cannot place your criteria on a separate sheet. Again, there is "irony" here. Not only is it possible, but it is actually "preferable" to have your criteria on a separate sheet. This is because if you place your criteria on the SAME sheet, Excel will begin to give you problems - because it will get "mixed up" between the field names used in your criteria and the field names in your worksheet-based database.

Microsoft appears to have a "lack of interest" in the "Advanced Filter" component of Excel. It also appears this lack of interest is based partly on their over-zealous attempt to convince end-users of Microsoft Office to go ahead and purchase their "optional" Access software.

The purchase of "Access" is highly recommended for smaller applications that are truly in need of fully functional database software. This is primarily to allow for: a) multi-users, b) relational tables, and c) a more secure environment.

However, what Microsoft needs to appreciate is that there are "MANY" applications where the end-user does NOT need to build a database application. And there are "MANY" situations where the end-user would like to be able to "manipulate" data that is received as a "data dump" from a true database - for example: Access, DB2, Oracle, Notes, etc.

This "lack of interest" is even more "obvious" when one takes into account that a whole decade - yes a full 10 years - has gone by and Microsoft has yet to include four worksheet-based "data manipulation" commands that they "neglected" to copy from Lotus 123. These commands include: Data-Find, Data-Modify, Data-Delete, and Data-Append.

In the same "ancient" version of Lotus 123, there were also commands to "link" tables and thereby create a worksheet-based "relational" database.

The "bottom line" is that there is a VERY obvious need for Microsoft to "get the picture" - and provide these missing features (commands) that will GREATLY enhance Excel.

In the meantime, I routinely notice here on Tek-Tips countless Excel users attempting to create "work-arounds" - usually with VBA - to manipulate the data in ways that would be EXTREMELY EASY - and LIGHTNING FAST - using the missing commands. When I talk of speed, I'm referring to the fact that these missing commands are written in the "C" language - which is MUCH faster than using VBA to "loop" through hundreds or thousands of records.

The one main Advanced Filter (VBA) command is based on the "C" language, and is therefore "lightning" fast in either: a) filtering data in place, or b) extracting data to a separate sheet.

If I’ve managed to “keep your attention”, “thanks” for listening.

To assist anyone who would like assistance with this very powerful “Advanced Filter” (data-manipulation”) component of Excel, I have some example files I can email you. They are relatively small files – and contained in a “Zip” file. These are basically “beginner” type files. Then once you’re ready for “more advanced” files, I can provide additional examples.

I hope this is of interest and will be of use to those who want to “manipulate data” within Excel, for example to create “custom views” or custom reports.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DarcyNoonan]

Hi Dale,

Can you please send me some of those examples. Send them to likwit18@hotmail.com.

Thank You