Macro disables "undo" feature..help please

[Terry]

I have a spreadsheet that I introduced a macro to eliminate duplications
with ranking formulae in the sheet.

All works ok until after I input data to the sheet, then run (update
button) the macro.
The macro does the job it was intended for but now the "undo" function in my
Excel sheet is disabled.

Without the macro the "undo" function works OK.

Has anyone had similar problems and curred them without removing the macro.

Dim myArray(500, 2) As String
Dim myIndex As Long

Sub getNames()
Dim myArray(500, 2) As String
Dim myIndex As Long
Dim myRow As Long
Dim lastRow As Long

lastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
myRow = 1
myIndex = 1

For i = 4 To lastRow
myArray(myIndex, 1) = Sheets(1).Cells(i, 1)
myArray(myIndex, 2) = Sheets(1).Cells(i, 16)
myIndex = myIndex + 1
Next
'MsgBox myArray(myIndex, 1).Rank
myRow = myIndex - 1
myIndex = 1
For i = 4 To 13
For j = 1 To myRow
If CStr(myArray(j, 2)) = CStr(Sheets(2).Cells(i, 4)) Then
Sheets(2).Cells(i, 3) = myArray(j, 1)
myArray(j, 1) = ""
myArray(j, 2) = ""
Exit For
End If
myIndex = myIndex + 1
Next
myIndex = 1
Next

End Sub

TIA
Terry

 

[Niek Otten]

Hi Terry,

Macros always disable undos from before the macro was run.
To get around that, you'd have program your own undo. Not an easy task, but it can be done.
Consider saving your workbook before the macro is run (or even in the macro itself)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a spreadsheet that I introduced a macro to eliminate duplications
| with ranking formulae in the sheet.
|
| All works ok until after I input data to the sheet, then run (update
| button) the macro.
| The macro does the job it was intended for but now the "undo" function in my
| Excel sheet is disabled.
|
| Without the macro the "undo" function works OK.
|
| Has anyone had similar problems and curred them without removing the macro.
|
| Dim myArray(500, 2) As String
| Dim myIndex As Long
|
| Sub getNames()
| Dim myArray(500, 2) As String
| Dim myIndex As Long
| Dim myRow As Long
| Dim lastRow As Long
|
| lastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
| myRow = 1
| myIndex = 1
|
| For i = 4 To lastRow
| myArray(myIndex, 1) = Sheets(1).Cells(i, 1)
| myArray(myIndex, 2) = Sheets(1).Cells(i, 16)
| myIndex = myIndex + 1
| Next
| 'MsgBox myArray(myIndex, 1).Rank
| myRow = myIndex - 1
| myIndex = 1
| For i = 4 To 13
| For j = 1 To myRow
| If CStr(myArray(j, 2)) = CStr(Sheets(2).Cells(i, 4)) Then
| Sheets(2).Cells(i, 3) = myArray(j, 1)
| myArray(j, 1) = ""
| myArray(j, 2) = ""
| Exit For
| End If
| myIndex = myIndex + 1
| Next
| myIndex = 1
| Next
|
| End Sub
|
| TIA
| Terry
|
|

 

[Terry]

Niek Otten

Thanks for that.....at least I know it is a known fact now.
I do not wish to have another programe for own undo function.
Regards
Terry

 

[Arvi Laanemets]

Hi

More correctly, any VBA code always resets undo counter. When after running
the macro you do some changes manually, you can use undo again - back to
moment when the macro was run. You never can use undo to restore your
workbook in state, it had before you run some VBA procedure - the only
option is to close the file without saving and reopening it then.

 

[Terry]

Arvi..Thank you.

Terry

Hi

More correctly, any VBA code always resets undo counter. When after
running the macro you do some changes manually, you can use undo again -
back to moment when the macro was run. You never can use undo to restore
your workbook in state, it had before you run some VBA procedure - the
only option is to close the file without saving and reopening it then.

 

[Dave Peterson]

John Walkenbach shows how:
http://j-walk.com/ss/excel/tips/tip23.htm

 

[Terry]

Thank you Dave

Terry

John Walkenbach shows how:
http://j-walk.com/ss/excel/tips/tip23.htm