I am looking for a way I can deactivate/disable a Forms style macro
assigned button on a worksheet if a certain condition on the worksheet
eg. I only want the macro button to be active if the value in cell A1
I know some of you may know that a more elegant solution or the only
solution exists if I instead used a Command Toolbox style button
assigned to the macro. However, I am unfamiliar with and have never
used these types of buttons before so if you are to offer assistance,
I may need a bit more help with it.
NOTE: The reason why I am trying to do this is to get around another
Excel problem that I think i harder to solve and has been of much
annoyance. I have a well laid out worksheet that features adjacent
and dedicated cell ranges for both pivot tables and the data ranges
they feed on as well as a range of other formulas that are based on
the pivot tables and other cells. Ideally, I DO NOT want the Pivot
Table to automatically enlarge/reduce in size based on what values are/
aren not in the associated data range.
eg. I only want the pivot table to provide infomation on Bill, Bob,
Jane and Sally and NOT anyone else, even if Fred, Tony, Anne, Mary or
anyother infinite number of names appear in the data source. Nor do I
want the pivot tabel to reduce in size if either or all of Bill, Bob,
Jane and Sally's names DO NOT appear in the data source range.
Unvalidated data is usually copied from an extrnal source and pasted
in to the reserved pivot table data ranges on the worksheet.
I know check the drop down menus to ONLY show data on Bill, Bob, Jane
and Sally but by default, all new field values enter the pivot table
as checked and any refereshing of the table will enlarge the table if
new previously unseen/unchecked field values are in the data source.
By preventing the pivot tables from refershing if "unvalidated"/junk
data exists in the data ranges, I can prevent the curse of the auto
enlarging pivot tablles and prevent them form overwiting the cells
below and to the right of them. Would also apprecuate any help
dealing with this probelm directly.