Undo is emptied after macro

[ed.ayers315]

Hello,

I am using the following macro to start a double click event to start a combobox and also enter "1" or "0" in different adresses from the combobox addresses.

I just figured out that in the below macro, I guess the error handler or the window reset it also empties the list in Undo.

Can this be written out or am I stuck?

Thanks.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim HasValidation As Boolean
On Error Resume Next
HasValidation3 = Target.Validation.Type = 3
On Error GoTo 0
Dim str As String
Dim cboTemp As OLEObject
Cancel = True
Set cboTemp = Me.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = True
End With
On Error GoTo errHandler
If HasValidation3 Then
'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left + 1
.Top = Target.Top + 1
.Width = Target.Width
.Height = Target.Height
.ListFillRange = Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
ActiveSheet.Shapes("TempCombo").Visible = False
Else
If Not Intersect(Target, Range("g21:h21,g23:h23,g25:h25,g27:h27,g29:h29,g31:h31,g33:h33,g35:h35,g37:h37,g39:h39,g41:h41,g43:h43,g48:h48,g50:h50,g52:h52, g54:h54,g56:h56,g58:h58,g60:h60,g62:h62,g64:h64,g66:h66")) Is Nothing Then
Cancel = True
If VarType(Target.Value) = vbBoolean Then
Target.Value = Not (Target.Value)
Else
Target.Value = IIf(Target.Value = 1, Null, 1)
End If
End If
End If
errHandler:
Application.EnableEvents = True
ActiveSheet.Shapes("TempCombo").Visible = False
ActiveSheet.Calculate
ActiveWindow.SmallScroll
Application.WindowState = Application.WindowState
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
ActiveWindow.SmallScroll
Application.WindowState = Application.WindowState
ActiveSheet.Shapes("TempCombo").Visible = False
End Sub

 

[shg]

I haven't waded through your code, but as soon as VBA changes the Excel environment (e.g., writing to a cell), the undo stack is flushed.

IMO, it's a good reason to be circumspect about introducing VBA to a workbook.

 

[VoG]

All macros clear the undo stack. There isn't a setting that you can change.

 

[njimack]

The Undo stack gets wiped whenever a VBA routine is called, although the following page does give an example of a workaround: http://spreadsheetpage.com/index.php/site/tip/undoing_a_vba_subroutine/

 

[ed.ayers315]

Thanks

 

[ed.ayers315]

I found another issue; when I copy something to paste, it disapears.

I can understand the undo, but the copy & paste?

 

[ed.ayers315]

I figured it out. It was the select change at the end. once deleted the copy paste works fine.

Still think there should be a way to stop unloading the undo. For people like me that is an important tool.

 

[VoG]

Unfortunately there isn't http://spreadsheetpage.com/index.php/comments/excels_vba_undo_problem/

 

[ed.ayers315]

Thanks VoG