Replies

[indent]

---

It’s caused by the 3D llok of commandbars in Office 2003. This cannot be turned off.

---

[/indent]

I thought as much – that’s why they llok so ugly, as well; granted, it’s a matter of taste, but they are a step backwards from XL 2K.

Thanks for your assistance

try

=MAX(A1:A7*B1:B7)

entered as an array formula (Ctrl-Shift-Enter). It works for me, as attached

Edited to change attached workbook – the original version contained some macros from a project I was working on. Nothing dangererous, but distracting…

Call the workbook that you retain (with proprietary information, etc) the “Master” and the one you send to clients for data entry the “data” workbook. In the master workbook, for each input cell replace the value with a link to the ‘equivalent’ location in the data workbook. After doing one on each input tab in the Master work book, you can just copy the formula as required, so it won’t be too bad (remember to replace the absolute references in the link with relative ref’s). You might have formatting issues – just copy the format back from the “data” book, or keep a dummy copy from the Master just for the format; copy the formats and then delete the dummy sheet.

When you distribute the data-collection workbooks, give them names like “ClientName_Data” – when you open your master workbook you can go to Edit | Links | Change Source and change “data” as the source to “ClientName1_Data” (with appropriate path information) and all the links will return the required data. If you include a cell in each data tab with the actual client name on it, then you will always be able to tell which client the Master is currently referencing. If you need to “freeze” client data, you could write code to overwrite the links with their own values, or even to identify each external link in the workbook and do the same – it depends on how widely scattered the cells are that refer to the “ClientName_Data” workbook. You would probably want to include a routine to save the book with a name other than “Master” (and possibly impose a naming convention) either before you make the change or immediately after, so you don’t step on the formulas you want to use to import the next set of client data.

You can set up the “autocorrect” feature to replace “m3” with “m-superscripted-3” (and “oC” with “degree-symbol-C” etc).

• Click into Tools | Autocorrect;
• Click the “replace text as you type” check box, if it is not already set:
• In the left-hand (“Replace:”) box under “replace text as you type” put in the text you want to have replaced – in this case, m3;
• In the right-hand (“With:”) box type m then alt-0179 – you should get a properly formatted cubic metres symbol;
• Click “Add”
  [/list]When you return to Excel and type m3 the “3” will be replaced by a “superscripted 3.” This will also work if you are typing expressions like m3/sec. NOte that this setting will apply to all MS office programs that use the same dictionary (ie, you will get the same effect in Word, Powerpoint, etc) – if that’s not what you want, you are probably stuck with typing alt-0179 or manually superscripting the numbers after you type them in.

  Note that this will also apply when you are entering formulas (yikes!) – if you need a formula like “=M3 * K2/4” the autoreplace will turn the “3” into a superscript, Excel will not understand how to interpret the formula, and will return an error. One work-around for this is to type in the formula, and then go back in and manually change the superscript to a “3” – if you are not typing directly after the “m3” (with an operator or a space) the autoreplace will not kick in, so move off that portion of the formula with the cursor keys.

  I would try it, to see if the benefit of having m

There just happens to be a post (and attached file) on this very subject at Dick Kusleika’s Daily Dose of Excel website…

[indent]

---

I pity the person who will do this!!

---

[/indent]
After I get through with them, I will pity the person who does this on a spreadsheet I have to deal with – which usually means responding to “This doesn’t work anymore; can you fix it?” Anyone else who is foolhardy enough to avail themselves of this feature gets what they deserve!

Other thoughts?

Would your client be able to step through the routine themselves, perhaps with you on the phone?
If so, would they be able to set up the VBE to show the locals or watch window?
Would they be able to set a watch or breakpoint for you?

Has this problem just come to light recently, or has it been happening since the code was put into production?

It seems that the problem is either related to the Citrix environment, or that the file they are running has been changed from the one that you have, since you are not encountering the same error. Could they send you a copy of the file they are having trouble with? – If you get the same error then maybe something has changed there – they might have changed a worksheet name, a named range, or something else that the code is relying on. If they can run the same file without errors when NOT running on Citrix, then that may point to a solution, or at least identification of the area to look at.

If your client can’t do any of the things you need, can you have them run a modified file for testing purposes only, with changes in objects selected, etc or loop counters recorded in a new file? I am a bit confused about the policy to not modify the code that clients are running – I assumed that you (or your company) were the original source of the code: what is your normal procedure for moving code into a client environment?

Rob – a couple of things:

• The series of If Then statements each need to be terminated with an End If.
• If the MoveLeft2 routines are like the MoveUp routine, then you will be re-setting the active cell when each of the subroutines are executed, and then re-entering the comparison routine somewhere in the middle. While this may do what you wish, I suspect that it will be error-prone; you might want to use a Select Case construct instead, since it will ‘drop out’ of the comparison stack after a successful comparison so the loop will repeat from the top. This may require that you define the activecell.colorindex as a variable and then use the value of the variable in the Select Case statement; I can’t remember and am just running air code here.
• The final End If statement does not refer to a prior If Then that I can see.
  [/list]

Ed – have you tried stepping through the code, with either a watch variable set on “TargetSheet” or the locals window open to see what value it has when the code encounters this line? What happens if you replace Worksheets(TargetSheet).Calculate with ActiveSheet.Calculate?

In the SelectionChange event, test for whether the desired range includes the selection; something like (warning, air code)

private sub Worksheet_SelectionChange(byval Target as range)

if intersect([mymaze],target) is nothing then
' code to perform if selection is outside "MyMaze"
else
'code if selection is inside "MyMaze"
end if

end sub

Note:

• the method to refer to the area where you want to confine the selection will depend on how it is used or already defined – this example assumes that it is a named range within excel, but if it already a range object you can just use the name.
• there is a flaw in this test if you are going to allow multi-cell selections – in this case, part of the selection could be within MyMaze, and part could be outside – you have to decide what to do with such a selection, and then test for it – possibly by testing for “if union([mymaze],target) = mymaze” – this will be true only if target is entirely contained within mymaze
  [/list]

Gee – it works for me; the values in the list are available either through the drop-down or when I type them in.

You can define the validation list either by XL renge references ($A$1:$A$15 style) or as a defined range name. The attached file has both. If you may be adding items to (or deleting – but then you get into ‘orphan’ problems) a defined name using a dynamic range name will work well.

If you insert a hidden blank row between the two rows, Excel will guess that only the lower of the two (visible) rows is intended to be the header – you will have to ensure that any descriptions are meaningful using only that row. If this is going to be sorted by a VBA routine then that doesn’t matter – but then you can set the sort range directly in any event…

[indent]

---

You need a space between PasteSpecial and the Underscore(_):

---

[/indent]

Sorry – that was my mistake in re-formatting the cut&paste from the VBE to fit better in the Lounge window

Another suggestion – with the worksheet “On Change” event deleted the routine is speeded up by approximately a factor of six times (by the admittedly imprecise method of counting “One Thousand One / One Thousand Two / One Thousand Three…) by setting the calculation method to “manual” by inserting a line:

Application.Calculation = xlCalculationManual

before the routine starts to do very much. You should then reverse this by setting xlCalculationAutomatic at the end of the routine (although I note that your s/sheet is set to manual re-calc in any event).

Brad – I have made one more modification. I assume that you will be adding data periodically. As it is set up now the new formats and formulas are copied down from Row 2 all the way to the bottom of the sheet. This will mean that if you are adding -say- 200 rows at a time, the performance for the first addition will be okay, the second a little worse, the third a little worse still, and so on – the workload will be a function of the total number of rows.

Instead, if you can rely on their being a named range “Database” that coresponds to good data (ie – that you have added before and run this routine on) then you only have to add the formatting and formulas to the new data – from the existing database to the end of the used range. The performance is still pretty slow, but at least it will be consistent, not getting slower and slower (other than the first time you run the routine). If you are satisfied with the state of the w-book now, just ensure that the “Database” range is defined to be the all fo the current data, then add any new data and run the routine below.

The following routine just adds formulas and formatting to the new data:

Sub CopyFormats()
'  Copies Formats and Formulas from row 2 of "CR Log" (known to be good)
'  to all active rows of that workbook tab.  Formats include conditional
'  formatting, Formulas include validity tests and results

Dim lLastRow        As Long
Dim lFirstRow
Dim i               As Integer
Dim ws              As Worksheet

Set ws = Sheet2
lLastRow = ws.UsedRange.Rows.Count
lFirstRow = Range("Database").Rows.Count

Application.ScreenUpdating = False
ws.Activate
With ws

    For i = 1 To .UsedRange.Columns.Count
        If .Cells(2, i).HasFormula Then
            ' copy cell - includes formula and formatting
            .Cells(2, i).Copy
            .Paste Destination:=Range(.Cells(lFirstRow, i), .Cells(lLastRow, i))
        Else
            ' copy formatting only
            .Cells(2, i).Copy
            .Range(.Cells(lFirstRow, i), .Cells(lLastRow, i)).PasteSpecial_ 
                                  Paste:=xlFormats, Operation:=xlNone, _
                                  SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
        End If
    Next i
    Application.CutCopyMode = False
    .Cells(2, 2).Select
    .Calculate
    Range("Database").Resize(lLastRow).Name = "Database"
End With
Set ws = Nothing

Application.ScreenUpdating = True

End Sub

As for combining the w-sheet change event with this routine – I tried running this routine with the “On Change” event deleted, and it wasn’t appreciably faster, although I didn’t time it at all formally.