Change cell backgrounds from VBA with undo enabled
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 |
Change cell backgrounds from VBA with undo enabled
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" wrote in message ... 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 |
Change cell backgrounds from VBA with undo enabled
On 18 abr, 23:47, "Jim Cone" wrote:
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" wrote in ... 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 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? |
Change cell backgrounds from VBA with undo enabled
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...ba_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" 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? |
All times are GMT +1. The time now is 02:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com