Copy in UserForm fails in 2004, works in 2002

[Jerry Natowitz]

The following code for a CommandButoon in a UserForm (yes, I have
TakeFocusOnClick set to false) fails miserably in Excel 2004, but works
perfectly in Excel 2002 on Win2000. Any suggestions?

Aside from row 1, the columns are:
1) Name
2) Date
3) Salary
4) a formula that is the sum of columns 7 to 13
5) Either "YES" or ""
6) A complex formula, with references to to another sheet
7-13) Either blank, or contain a percentage

I can copy from cell to cell, but I lose the formulas.
I can copy Cell.Formula, but then the relative portions of the formulas
are not updated.
I've tried a bunch of variations, none of which work.


Dim cur_people(100), this_person, last_person As String
Dim people_index(100), people_data_col, cur_row As Integer
Dim last_date, people_row, people_count, i, j, index_sel As Integer

Private Sub CommandButton1_Click()

With Worksheets("People")
Cells(1, 1) = cur_people(index_sel + 1)
Cells(1, 2) = index_sel
Cells(1, 3) = people_index(index_sel + 1)
Cells(1, 4) = people_index(index_sel + 2)
Cells(1, 5) = ""

ChangePeople2.Show
ActiveSheet.Activate
If Not IsEmpty(Cells(1, 5)) Then
i = Cells(1, 5)
.Range("A" & i - 1 & ":M" & i - 1).Copy
' also .Range(Cells(i-1,1),Cells(i-1,13)).Copy
' also .Range("A" & i - 1, "M" & i - 1).Copy
.Paste Destination:=Range("A" & i, "M" & i)
End If
Cells(1, 1) = ""
Cells(1, 2) = ""
Cells(1, 3) = ""
End With
End Sub

 

[JE McGimpsey]

The following code for a CommandButoon in a UserForm (yes, I have
TakeFocusOnClick set to false) fails miserably in Excel 2004, but works
perfectly in Excel 2002 on Win2000. Any suggestions?

This is a bug in XL04 (your code works fine in XLv.X). MS is aware of
it, but I don't know of any fix yet.

 

[aGraham]

I just ran into this also, but my example is much easier, basically try
to do a selection.copy from the immediate window fails also....

 

[JE McGimpsey]

I just ran into this also, but my example is much easier, basically try
to do a selection.copy from the immediate window fails also....

Yup. Appears to be a problem with where the focus is - when the focus is
a userform or the VBE, I get the error, but not when the macro is run
from the worksheet.

I've already had to tell some of my clients to revert to v.X. Hopefully
this is fixed *real* soon.