Change cell backgrounds from VBA with undo enabled

[Fede Sauret]

Hi,

I've been trying to find a way to do this, but couldn't find enough
information out there. So sorry if this was already explained
somewhere else. I also hope am asking in the right place.

I have added a custom ribbon tab in an add-in. One of the controls
will consist of 9 small buttons, each button changes the active cell
background to a specific color. This way I expect we'll be saving some
time. My problem is that by changing the color this way (using a VBA
macro) I loose the possibility of undoing the changes. So I was
wondering if it would be possible to simulate the click of the user in
the color filler button that comes in the default Excel ribbon at the
Font group in the Home tab.

After a few hours trying it I could do something similar with:
Application.CommandBars.ExecuteMso ("CellFillColorPicker")
But this solution doesn't allow to undo. Also I couldn't find a way to
change the selected color, so it would be pretty much useless.

I also tried accessing the control doing:

Dim CBR As CommandBar
Set CBR = Application.CommandBars("Fill color")

Dim colControl As CommandBarControl
Set colControl = CBR.Controls(3) 'This three is in order to
access one of the buttons in the bar, in this case, "Standard colors".
But I also tried the rest.

colControl.Execute

But this code's "colControl.execute" doesn't do anything. I guess it's
because I am accessing a CommandBarControl, not a CommandBarButton,
but I didn't find a way to do it. I did though find a way to format
the text bold:

Dim CBR As CommandBar
Set CBR = Application.CommandBars("Formatting")

Dim bold As CommandBarButton
Set bold = CBR.Controls(3)
bold.Execute

And again, this option doesn't allow undoing actions.


So, how should I do it?

Thanks a lot in advance and I hope I explained myself!
Fede

 

[Jim Cone]

Excel will not undo any action initiated in VBA.
You will have to write your own undo code and provide a button for the user to click.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)




"Fede Sauret" <[email protected]>
wrote in message
news:[email protected]...

 

[Fede Sauret]

Excel will not undo any action initiated in VBA.
You will have to write your own undo code and provide a button for the user to click.
--
Jim Cone
Portland, Oregon USA .http://www.mediafire.com/PrimitiveSoftware.
(free and commercial excel programs)

"Fede Sauret" <[email protected]>
wrote in message

Thanks Jim,

yes, I did have to implement my own Undo function. But I couldn't find
a workaround to keep using the old "undo stack", neither be able to
memorize more than action to undo, since everytime the code I have is
run, the whole stack is emptied.

I know Excel empties the "undo stack" when certain things are done
from VBA. But not every time, for example if we run a HelloWorld
function as follows:


Sub HelloWorld()
MsgBox "Hello world"
End Sub

In this case undo stack will remain untouched, with all previous
actions. That's why I thought that maybe I could emulate a call to the
"fill in color" button that would not empty the stack and be
memorized.

Are you sure this is completely impossible? What about adding copies
of the default fill-in button to a custom ribbon? I could change the
icon, using a different color for each copy, and then try to run some
code that would change the picked up color before the default "fill in
button" code is run. Is this possible? How would I do it?

 

[Jim Cone]

You will have to save the "before" configuration and then use that in your undo code.
John Walkenbach has an example here...
http://spreadsheetpage.com/index.php/tip/undoing_a_vba_subroutine/

Note also that the undo option should turn itself off at some point.
My Special Sort add-in undo feature, for instance, saves a sampling of cell values from the post
sort range and won't undo if those cells have changed .
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
(editorial review of Special Sort add-in)





"Fede Sauret" <[email protected]>
wrote in message

Thanks Jim,
yes, I did have to implement my own Undo function. But I couldn't find
a workaround to keep using the old "undo stack", neither be able to
memorize more than action to undo, since everytime the code I have is
run, the whole stack is emptied.

I know Excel empties the "undo stack" when certain things are done
from VBA. But not every time, for example if we run a HelloWorld
function as follows:


Sub HelloWorld()
MsgBox "Hello world"
End Sub

In this case undo stack will remain untouched, with all previous
actions. That's why I thought that maybe I could emulate a call to the
"fill in color" button that would not empty the stack and be
memorized.

Are you sure this is completely impossible? What about adding copies
of the default fill-in button to a custom ribbon? I could change the
icon, using a different color for each copy, and then try to run some
code that would change the picked up color before the default "fill in
button" code is run. Is this possible? How would I do it?