Replies

On the Settings tab for the Data Validation box is a check option to “Ignore blank” which allows blank entries in the cell.

Well done. All I can say is:
You gotta love the M$ office products…

This can happen it the ‘Names’ were referring to old EXCEL4 functions like GET.DOCUMENT for example.

I think that’s the safest bet, but I haven’t researched it thoroughly.

Examine your ‘copied’ sheet for named ranges which have references to external sources. Also, charts, buttons, and other objects may have external links as well.

I think your last supposition is the culprit (range not on the active sheet), but there are a couple more implications:

Range names can be defined at the workbook level (VBA code should run as long as that workbook is active) or at the worksheet level (sheet must be active for the VBA code referencing that range name to run – OR – VBA code must fully qualify the Range name with the worksheet name). BTW, the same name can exist at both the workbook and worksheet level.
For examples sake, let’s say we have two ranges named MyRange: one is referenced on Sheet1 and shows up in the Insert/Names/Define dialog box as MyRange with its reference showing =Sheet1$A$1. On Sheet2 you can define a Name as Sheet2!MyRange with a reference of =Sheet2!$A$1.

Displaying the Insert/Name/Define dialog box while on Sheet2 will show:
MyRange Sheet2 with reference =Sheet2!$A$1
(notice that the sheet name is shown on the right side)

Doing the same while on Sheet 1 will display:
MyRange with reference =Sheet1!$A$1
(notice that there is no sheet name shown)

Hope this isn’t too confusing…

True, you can’t insert rows in the middle of the array, but there are cases where array formulas are invaluable. For example, I often have to use a ‘SumProductIF’ construct to calculate average salaries by month,by dept. from a long title/salary/dept listing which could have multiple counts for each title changing by month. I use an array formula construction like:
=SUM(IF(dept=something,(Month#headcount*salary)/month#headcount,0)

Once this is constructed, I can insert or delete rows withing the listing without breaking the formulas. I only have to work with a thousand rows or less so performance is no big factor.

I, too, have the exact same symptoms. It used to aggravate me. Now, I habitually go to one end of the selection, click it, then go to the other end and do a ‘Shift-Click’. That seems to always work.

BTW, I’ve seen this happen in Lotus Notes as well MS Word, so I think it may be a ‘Mouse/Windows’ issue instead of just an Office issue.

Going back to your original notion of ‘busting the users’ when they unhide the worksheet. You could put something in the Worksheet_Activate event that puts up a message box with your ‘You Are Busted’ message and then activates some other sheet before it ends. That way, the users would never get to access the worksheet (you could also activate a range or change the scroll values so that no data would be visible behind the messagebox). You could still protect your sheet and have some fun at the same time (assuming, of course, that your users have some sense of humor and you don’t insult them too cruelly in your messsage).

That sounds like a 5.7min mile to me — which, in my opinion, has crossed the line from jogging to running. And at a pretty decent pace to boot!

If you can get the results in a cell, you can insert the cell value in the footer via a VBA statement placed in the WorkBook BeforePrint event (also fires for Print Preview).

According to the HELP file, LINKS requires the full path/workbook name as its first parameter (defaults to current workbook). As I recall, the first time I tried this many moons ago, I had ‘volatility’ problems, in that, if the user added a new link, it wouldn’t show up in the list until the workbook was closed then re-opened. I think I added the CELLS(“filename”) approach and referenced that cell in the =LINKS reference to get around that somehow. I think the LINKS HELP may be wrong in that only the workbook name without the path seems to work. At any rate, I now use a short sub in the Worksheet_Activate event (I have one sheet which is only used for this display) to generate the workbook name in a cell and it makes the whole thing volatile.

You could insert a statement that activates a specific worksheet and range in either the Workbook_Open or Workbook_BeforeClose event to accomplish that. Something like:
Sub Workbook_Open ()
Worksheets.(“Sheet1”).activate
Range(“A1”).select
End Sub

You can use just =LINKS() as the reference for your Name — you need to have saved the workbook at some point.

I neglected to mention that the Transpose formula needs to be ‘array entered’ in your target range by selecting the range and using Cntrl-Shift-Enter when entering the formula.

It’s a ‘left-over’ from Excel4. You can use some of these functions by defining them as Names like in my example.