rediffusion
08-09-2019, 12:57 AM
I have macros ··· to fill; font color; insert note. For them, not working Undo/Redo (which are located on the QAT) or CTRL+Z – CTRL+Y. I Found these links wellsr.com (https://wellsr.com/vba/2019/excel/how-to-undo-a-macro-with-vba-onundo-and-onrepeat/#comment-4551766283) and jkp-ads.com (https://jkp-ads.com/Articles/UndoWithVBA04.asp) there is info, though it is not clear how to combine it all with all my macros!? :confused:
1. Example XML+VBA implementation of the book here (https://yadi.sk/d/DqFQKbHUT9muzw) (the macro fill color). One user offered a working version! Here's just a shame that I have fills for color lot say 30-40. How it to adapt, we can variable what to insert in place xSelection.Interior.Color :
Note that this is a ready working version of Undo/Redo! ↴↴↴ ↴↴↴ ↴↴↴
Sub CellColor()
Call CellColor_Do(0)
End Sub
Private Sub CellColor_Do(Undo As Integer)
Static xBook AsWorkbook, xSheet AsWorksheet, xSelection AsRange, xColor AsLong
Const sName AsString = "CellColor"
Const sUndo AsString = "Undo Color in "
Const sRedo AsString = "Redo Color in "
If Undo = 0 Then
Set xBook = ActiveWorkbook
Set xSheet = ActiveSheet
Set xSelection = Selection
xColor = Selection.Cells(1).Interior.Color
xSelection.Show
xSelection.Interior.Color = RGB(227,38,54)
Application.OnUndo (sUndo + xSelection.Address(False,False)), (ThisWorkbook.Name + "!" + sName + "_Undo")
Else If xSelection Is Nothing Then
Beep
Else
xBook.Activate
xSheet.Activate
xSelection.Select
xSelection.Show
If Undo < 0 Then
xSelection.Interior.Color = xColor
Application.OnRepeat (sRedo + xSelection.Address(False,False)), (ThisWorkbook.Name + "!" + sName + "_Redo")
Else
xSelection.Interior.Color = RGB(227,38,54)
Set xSelection = Nothing
End If
End If
End Sub
Private Sub CellColor_Undo()
Call CellColor_Do(-1)
End Sub
Private Sub CellColor_Redo()
Call CellColor_Do(1)
End Sub
2. There is still macro to which, too, want to tie:
Sub Note_FillColor_White()
Dim myComm AsComment
If Not ActiveCell.Comment Is Nothing Then
If MsgBox("The cell already contains a note, delete?", 4) - 7Then
ActiveCell.Comment.Delete
Else: Exit Sub
End If
End If
Set myComm = ActiveCell.AddComment
With myComm.Shape
.Height = 110
.Width = 200
.Top = 55
.AutoShapeType = 1 'form
.Fill.ForeColor.SchemeColor = 1 'Fill color white
.Line.ForeColor.RGB = RGB(255,0,0)
.DrawingObject.Font.Name = "Consolas"
.DrawingObject.Font.FontStyle = "normal"
.DrawingObject.Font.Size = 8
End With
'Emulate the choice of "Edit Note".
SendKeys "+{F2}"
End Sub
3. Still hve a macro (need a combine):
Sub CoverCommentIndicator(control AsIRibbonControl)
'www.contextures.com/xlcomments03.html
Dim ws As Worksheet
Dim cmt As Comment
Dim lCmt As Long
Dim rngCmt As Range
Dim shpCmt As Shape
Dim shpW As Double 'width of the shape.
Dim shpH AsDouble 'the height of the form.
Set ws = ActiveSheet
shpW = 8
shpH = 6
lCmt = 1
For Each cmt In ws.Comments
Set rngCmt = cmt.Parent
With rngCmt
Set shpCmt = ws.Shapes.AddShape(msoShapeRectangle, _
rngCmt.Offset(0, 1).Left - shpW, .Top, shpW, shpH)
End With
With shpCmt
.Name = "CmtNum" & .Name
With .Fill
.ForeColor.SchemeColor = 9
.Visible = msoTrue
.Solid
End With
With .Line
.Visible = msoTrue
.ForeColor.SchemeColor = 64 'The stroke color is set automatically for all indicators!
.Weight = 0.25 'The thickness of the stroke is automatically set for all indicators!
End With
With .TextFrame
.Characters.Text = lCmt
.Characters.Font.Size = 5
.Characters.Font.ColorIndex = xlAutomatic
.MarginLeft = 0#
.MarginRight = 0#
.MarginTop = 0#
.MarginBottom = 0#
.HorizontalAlignment = xlCenter 'The location of the text (in this case, in the center).
End With
.Top = .Top + 0.001 'The location of the frame (in this case on the right).
End With
lCmt = lCmt + 1
Next cmt
End Sub
In fact, I have a lot of macros (perhaps if you understand how to tie Undo/Redo to these macros - you can understand the formula and in the future to adapt).
There is a topic (https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fwww.excel-vba.ru%2Fchto-umeet-excel%2Fkak-otmenit-dejstviya-makrosa%2F) on this issue.
The first macro in my opinion is the most suitable. Noticed the cons:
1. Ctrl+Y doesn't work.
2. Ctr+Z - only 1 step. And erases the cell (i.e. strips/tracings of the cell)!
3. If you fill several cells (multiselect) - fill is not black but different for each cell (black is only the first). More cells are numbered 1, 2, 3, etc. (what it meant)!?
4. The second option just not a cake, brew some. https://www.mrexcel.com/forum/images/smilies/icon_biggrin.gif
5. @Sergey (https://www.mrexcel.com/forum/members/sergey.html) - By the way, shared a good idea (https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fwww.excel-vba.ru%2Fchto-umeet-excel%2Fkak-otmenit-dejstviya-makrosa%2F%23comment-5618)!
---
Do you have any idea how to roll it all on VBA?
The above macro which I shared was better (in this topic the first code)... There are the same nuances and pluses:
Multiserivce works as it should. There is no numbering!
---
Stumbled on this add-in Ablebits (https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fwww.ablebits.com%2Fdownloads%2Findex.php) called, (the trial period can be downloaded from the official website but still there is always a crack here (https://www.mrexcel.com/forum/redirect-to/?redirect=http%3A%2F%2Fcwer.ws%2Fnode%2F465560%2F) and here (https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fdiakov.net%2F11558-ablebits-ultimate-suite-for-microsoft-excel-2018311975836-business-edition.html)). There is a backup Book:
http://www.cyberforum.ru/attachments/1060046d1565421641
http://www.cyberforum.ru/attachments/1060047d1565421692
I wrote them - (they mow under the Americans but I know that there Russian sit) also say that they have no access to some internal resources Excel and therefore implemented for their personal macros such `BackUP`. Using the buttons in this add-in, I noticed that Ctrl+Z work somewhere where it is not. Despite the fact that they made a dragon add-in and Ctrl+Z to implement failed.
---
Another BOURGES online read this (https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fexcel.tips.net%2FT002060_Preserving_the_Undo_List.h tml):
When you run a macro, however, the macro doesn't "play nice" with the Undo list. In fact, running a macro completely erases the Undo list, and therefore you cannot automatically undo the effects of running the macro. There is no intrinsic command—in Excel or in VBA—to preserve the Undo list
---
Does anyone have any ideas on implementing such backup options:
1. Described @Sergey's.
2. Ablebits backup.
3. Well or use the standard method, and it is - DELETE; delete anything through the "Context Menu"; Always make a copy of the Book for rollback.
1. Example XML+VBA implementation of the book here (https://yadi.sk/d/DqFQKbHUT9muzw) (the macro fill color). One user offered a working version! Here's just a shame that I have fills for color lot say 30-40. How it to adapt, we can variable what to insert in place xSelection.Interior.Color :
Note that this is a ready working version of Undo/Redo! ↴↴↴ ↴↴↴ ↴↴↴
Sub CellColor()
Call CellColor_Do(0)
End Sub
Private Sub CellColor_Do(Undo As Integer)
Static xBook AsWorkbook, xSheet AsWorksheet, xSelection AsRange, xColor AsLong
Const sName AsString = "CellColor"
Const sUndo AsString = "Undo Color in "
Const sRedo AsString = "Redo Color in "
If Undo = 0 Then
Set xBook = ActiveWorkbook
Set xSheet = ActiveSheet
Set xSelection = Selection
xColor = Selection.Cells(1).Interior.Color
xSelection.Show
xSelection.Interior.Color = RGB(227,38,54)
Application.OnUndo (sUndo + xSelection.Address(False,False)), (ThisWorkbook.Name + "!" + sName + "_Undo")
Else If xSelection Is Nothing Then
Beep
Else
xBook.Activate
xSheet.Activate
xSelection.Select
xSelection.Show
If Undo < 0 Then
xSelection.Interior.Color = xColor
Application.OnRepeat (sRedo + xSelection.Address(False,False)), (ThisWorkbook.Name + "!" + sName + "_Redo")
Else
xSelection.Interior.Color = RGB(227,38,54)
Set xSelection = Nothing
End If
End If
End Sub
Private Sub CellColor_Undo()
Call CellColor_Do(-1)
End Sub
Private Sub CellColor_Redo()
Call CellColor_Do(1)
End Sub
2. There is still macro to which, too, want to tie:
Sub Note_FillColor_White()
Dim myComm AsComment
If Not ActiveCell.Comment Is Nothing Then
If MsgBox("The cell already contains a note, delete?", 4) - 7Then
ActiveCell.Comment.Delete
Else: Exit Sub
End If
End If
Set myComm = ActiveCell.AddComment
With myComm.Shape
.Height = 110
.Width = 200
.Top = 55
.AutoShapeType = 1 'form
.Fill.ForeColor.SchemeColor = 1 'Fill color white
.Line.ForeColor.RGB = RGB(255,0,0)
.DrawingObject.Font.Name = "Consolas"
.DrawingObject.Font.FontStyle = "normal"
.DrawingObject.Font.Size = 8
End With
'Emulate the choice of "Edit Note".
SendKeys "+{F2}"
End Sub
3. Still hve a macro (need a combine):
Sub CoverCommentIndicator(control AsIRibbonControl)
'www.contextures.com/xlcomments03.html
Dim ws As Worksheet
Dim cmt As Comment
Dim lCmt As Long
Dim rngCmt As Range
Dim shpCmt As Shape
Dim shpW As Double 'width of the shape.
Dim shpH AsDouble 'the height of the form.
Set ws = ActiveSheet
shpW = 8
shpH = 6
lCmt = 1
For Each cmt In ws.Comments
Set rngCmt = cmt.Parent
With rngCmt
Set shpCmt = ws.Shapes.AddShape(msoShapeRectangle, _
rngCmt.Offset(0, 1).Left - shpW, .Top, shpW, shpH)
End With
With shpCmt
.Name = "CmtNum" & .Name
With .Fill
.ForeColor.SchemeColor = 9
.Visible = msoTrue
.Solid
End With
With .Line
.Visible = msoTrue
.ForeColor.SchemeColor = 64 'The stroke color is set automatically for all indicators!
.Weight = 0.25 'The thickness of the stroke is automatically set for all indicators!
End With
With .TextFrame
.Characters.Text = lCmt
.Characters.Font.Size = 5
.Characters.Font.ColorIndex = xlAutomatic
.MarginLeft = 0#
.MarginRight = 0#
.MarginTop = 0#
.MarginBottom = 0#
.HorizontalAlignment = xlCenter 'The location of the text (in this case, in the center).
End With
.Top = .Top + 0.001 'The location of the frame (in this case on the right).
End With
lCmt = lCmt + 1
Next cmt
End Sub
In fact, I have a lot of macros (perhaps if you understand how to tie Undo/Redo to these macros - you can understand the formula and in the future to adapt).
There is a topic (https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fwww.excel-vba.ru%2Fchto-umeet-excel%2Fkak-otmenit-dejstviya-makrosa%2F) on this issue.
The first macro in my opinion is the most suitable. Noticed the cons:
1. Ctrl+Y doesn't work.
2. Ctr+Z - only 1 step. And erases the cell (i.e. strips/tracings of the cell)!
3. If you fill several cells (multiselect) - fill is not black but different for each cell (black is only the first). More cells are numbered 1, 2, 3, etc. (what it meant)!?
4. The second option just not a cake, brew some. https://www.mrexcel.com/forum/images/smilies/icon_biggrin.gif
5. @Sergey (https://www.mrexcel.com/forum/members/sergey.html) - By the way, shared a good idea (https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fwww.excel-vba.ru%2Fchto-umeet-excel%2Fkak-otmenit-dejstviya-makrosa%2F%23comment-5618)!
---
Do you have any idea how to roll it all on VBA?
The above macro which I shared was better (in this topic the first code)... There are the same nuances and pluses:
Multiserivce works as it should. There is no numbering!
---
Stumbled on this add-in Ablebits (https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fwww.ablebits.com%2Fdownloads%2Findex.php) called, (the trial period can be downloaded from the official website but still there is always a crack here (https://www.mrexcel.com/forum/redirect-to/?redirect=http%3A%2F%2Fcwer.ws%2Fnode%2F465560%2F) and here (https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fdiakov.net%2F11558-ablebits-ultimate-suite-for-microsoft-excel-2018311975836-business-edition.html)). There is a backup Book:
http://www.cyberforum.ru/attachments/1060046d1565421641
http://www.cyberforum.ru/attachments/1060047d1565421692
I wrote them - (they mow under the Americans but I know that there Russian sit) also say that they have no access to some internal resources Excel and therefore implemented for their personal macros such `BackUP`. Using the buttons in this add-in, I noticed that Ctrl+Z work somewhere where it is not. Despite the fact that they made a dragon add-in and Ctrl+Z to implement failed.
---
Another BOURGES online read this (https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fexcel.tips.net%2FT002060_Preserving_the_Undo_List.h tml):
When you run a macro, however, the macro doesn't "play nice" with the Undo list. In fact, running a macro completely erases the Undo list, and therefore you cannot automatically undo the effects of running the macro. There is no intrinsic command—in Excel or in VBA—to preserve the Undo list
---
Does anyone have any ideas on implementing such backup options:
1. Described @Sergey's.
2. Ablebits backup.
3. Well or use the standard method, and it is - DELETE; delete anything through the "Context Menu"; Always make a copy of the Book for rollback.