application.redo

[baitmaster]

I know, it doesn't exist...

Why not?! I saw somewhere that application.undo works once for undo, then once for redo, but that doesn't seem to work... And there is no application.redo

___________________________________
Thats my question, the following boring bit is about why:

I need to prevent users changing some sections of a worksheet, based on what is already there. I.e. they can change anything, until a specific value has been entered - once that happens, it stays as it is

The code I have so far is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim blQueryChanges As Boolean
Dim newValue As Variant, strOldValue As String, arrOldValue() As Variant
Dim i As Long, j As Integer
ReDim oldValue(1 To Target.Rows.Count, 1 To Target.Columns.Count)
 
' get new / old values of range
Application.EnableEvents = False
newValue = Target.Value
Application.Undo
 
' test if any values should not be changed
If Target.Cells.Count > 1 Then
    arrOldValue = Target.Value
    For i = 1 To UBound(arrOldValue, 1)
        For j = 1 To UBound(arrOldValue, 2)
            Select Case arrOldValue(i, j)
                Case "holiday", "non-avail", "training"
                    blQueryChanges = True
                    Exit For
            End Select
        Next j
    Next i
Else
    strOldValue = Target.Value
    Select Case strOldValue
        Case "holiday", "non-avail", "training"
            blQueryChanges = True
    End Select
End If
 
' actions if query made
If blQueryChanges Then
    Dim proceed As Integer: proceed = MsgBox("WARNING: one or more of the cells you are changing contains a value that should not be changed. Are you sure you wish to proceed?", vbOKCancel)
    If proceed = 1 Then
[COLOR=red]      Target.Value = newValue[/COLOR]
    End If
 
    If proceed = 2 Then
        MsgBox "action cancelled", vbInformation
    End If
Else
    [COLOR=red]Target.Value = newValue[/COLOR]
End If
 
Application.EnableEvents = True
End Sub

This code works, BUT my sheet needs to use cell comments quite liberally, and this code undoes them, but won't write them back in, it only rewrites the cell value (as highlighted in red sections). I thought application.redo would help here instead... surely it must exist in some way, else how does Excel have a redo feature?!

 

[baitmaster]

Any suggestions on how I could create a REDO function in VBA?

Or ignore this macro if only cell contents are changed, i.e. not if comments are changed?

thanks

 

[RoryA]

The Change event is not triggered by comments, so I'm not sure what you are asking?
FYI, Application.Repeat I think is what you were asking about for the other bit.

 

[baitmaster]

Thanks Rory

My issue was identified when a User wants to copy and paste one cell to another, such as to copy comments across a range of destination cells. In this case, the undo action kicks in, so that the target cells can be checked to make sure the action is allowed. If approved, I want to allow the action

My code currently does not do this, if approved it updates the target cell values, but nothing else associated with them - comments, formatting etc. Ideally I dont want to use undo at all, but its the only way I know to test the previous values in the target cells

I dont think Repeat does this, but I'll go and check now. I thought it repeats the previous User action, I want something to "undo the undo"...

 

[RoryA]

But your Undo is undoing the last user action, so surely Repeat is exactly what you want? Or did I miss something?

 

[baitmaster]

I have no idea what I did yesterday

Replacing the red lines with Application.undo worked, despite the fact I was so sure it didn't, that I even said so

Sorry for wasting your time Rory, thanks for making me think about it again