rediffusion
Board Regular
- Joined
- Apr 24, 2019
- Messages
- 60
Cross-post!
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 and jkp-ads.com there is info, though it is not clear how to combine it all with all my macros!?
1. Example XML+VBA implementation of the book here (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! ↴↴↴ ↴↴↴ ↴↴↴
2. There is still macro to which, too, want to tie:
3. Still hve a macro (need a combine):
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).
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 and jkp-ads.com there is info, though it is not clear how to combine it all with all my macros!?
1. Example XML+VBA implementation of the book here (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! ↴↴↴ ↴↴↴ ↴↴↴
Code:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#2B91AF][FONT=inherit]Sub [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]CellColor[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()
[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#2B91AF][FONT=inherit]Call [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]CellColor_Do[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]End [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Private [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Sub [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]CellColor_Do[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Undo [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]As [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Integer[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Static[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xBook [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]As[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Workbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xSheet [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]As[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Worksheet[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xSelection [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]As[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xColor [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]As[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Long[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Const[/FONT][/COLOR][COLOR=#303336][FONT=inherit] sName [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]As[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]String [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"CellColor"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Const[/FONT][/COLOR][COLOR=#303336][FONT=inherit] sUndo [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]As[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]String [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Undo Color in "[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Const[/FONT][/COLOR][COLOR=#303336][FONT=inherit] sRedo [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]As[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]String [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Redo Color in "[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]If [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Undo [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0 [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xBook [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]ActiveWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xSheet [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]ActiveSheet[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xSelection [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Selection[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
xColor [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Selection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Cells[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Interior[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Color[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
xSelection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Show[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
xSelection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Interior[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Color [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] RGB[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]227[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]38[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]54[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]OnUndo [/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]sUndo [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xSelection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Address[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#101094][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#101094][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit])), [/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]ThisWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Name [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+ [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"!" [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#303336][FONT=inherit] sName [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+ [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"_Undo"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Else If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xSelection [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Is [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Nothing [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Beep[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Else[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
xBook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Activate[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
xSheet[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Activate[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
xSelection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Select[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
xSelection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Show[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]If [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Undo [/FONT][/COLOR][COLOR=#303336][FONT=inherit]< [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0 [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
xSelection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Interior[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Color [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xColor
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]OnRepeat [/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]sRedo [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xSelection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Address[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#101094][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#101094][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit])), [/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]ThisWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Name [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+ [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"!" [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#303336][FONT=inherit] sName [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+ [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"_Redo"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Else[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
xSelection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Interior[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Color [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] RGB[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]227[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]38[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]54[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xSelection [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Nothing[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]End [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]End [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]End [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Private [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Sub [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]CellColor_Undo[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Call [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]CellColor_Do[/FONT][/COLOR][COLOR=#303336][FONT=inherit](-[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]End [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Private [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Sub [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]CellColor_Redo[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Call [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]CellColor_Do[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]End [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Sub[/FONT][/COLOR]</code></code>
2. There is still macro to which, too, want to tie:
Code:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#2B91AF][FONT=inherit]Sub [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Note_FillColor_White[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] myComm [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]As[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Comment[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]If [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Not [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]ActiveCell[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Comment [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Is [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Nothing [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]If [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]MsgBox[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"[/FONT][/COLOR]The cell already contains a note, delete?[COLOR=#7D2727][FONT=inherit]"[/FONT][/COLOR][COLOR=#303336][FONT=inherit], [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]4[/FONT][/COLOR][COLOR=#303336][FONT=inherit]) [/FONT][/COLOR][COLOR=#303336][FONT=inherit]- [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]7[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]ActiveCell[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Comment[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Delete[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Else[/FONT][/COLOR][COLOR=#303336][FONT=inherit]: [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Exit [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]End [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]End [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] myComm [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]ActiveCell[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]AddComment[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit] myComm[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Shape[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Height [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]110[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Width [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]200[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Top [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]55[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]AutoShapeType [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1 [/FONT][/COLOR][FONT=inherit][COLOR=#7d2727]'form[/COLOR]
[COLOR=#7d2727] .Fill.ForeColor.SchemeColor = 1 '[/COLOR][COLOR=#303336]Fill color white[/COLOR][/FONT][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Line[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]ForeColor[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]RGB [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] RGB[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]255[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]DrawingObject[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Font[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Name [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Consolas"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]DrawingObject[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Font[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]FontStyle [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"normal"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]DrawingObject[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Font[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Size [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]8[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]End [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]'[/FONT][/COLOR]Emulate the choice of "Edit Note".[COLOR=#7D2727][FONT=inherit]
SendKeys "+{F2}"
End Sub
[/FONT][/COLOR]</code>
3. Still hve a macro (need a combine):
Code:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#2B91AF][FONT=inherit]Sub [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]CoverCommentIndicator[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]control [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]As[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]IRibbonControl[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]'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 '[/FONT][/COLOR]width of the shape.[COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] shpH [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]As[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Double [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]'[/FONT][/COLOR]the height of the form.[COLOR=#7D2727][FONT=inherit]
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[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Visible [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] msoTrue
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Solid[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]End [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]With [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Line[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Visible [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] msoTrue
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]ForeColor[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]SchemeColor [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]64 [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]'[/FONT][/COLOR]The stroke color is set automatically for all indicators![COLOR=#7D2727][FONT=inherit]
.Weight = 0.25 '[/FONT][/COLOR]The thickness of the stroke is automatically set for all indicators![COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]End [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]With [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]TextFrame[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Characters[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Text [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] lCmt
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Characters[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Font[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Size [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]5[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]
.Characters.Font.ColorIndex = xlAutomatic
.MarginLeft = 0#
.MarginRight = 0#
.MarginTop = 0#
.MarginBottom = 0#
.HorizontalAlignment = xlCenter '[/FONT][/COLOR]The location of the text (in this case, in the center).[COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]End [/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Top [/FONT][/COLOR][COLOR=#303336][FONT=inherit]= [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#2B91AF][FONT=inherit]Top [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+ [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0.001 [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]'[/FONT][/COLOR]The location of the frame (in this case on the right).[COLOR=#7D2727][FONT=inherit]
End With
lCmt = lCmt + 1
Next cmt
End Sub
[/FONT][/COLOR]</code>
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).