How can I make Ctrl + Z work in these simple color macros?

[RandomGerman]

Dear all,

as mentioned in another thread I know a little bit about VBA coding in PowerPoint, but Excel still is a different planet.

I was thinking about three little macros for quick implementation of some brand colors into one's Excel sheet: One for fills, one for borders, one for text. Googling this, led me to a very simple solution:

Option Explicit
Sub Fill()
    Selection.Interior.Color = RGB(255, 153, 0)
End Sub
Sub Border()
    Selection.Borders.Color = RGB(255, 153, 0)
End Sub
Sub Font()
    Selection.Font.Color = RGB(255, 153, 0)
End Sub

So far, so good. But I'm used to use Ctrl + Z a lot and this shortcut doesn't work here. This is an issue I never had when coding for PowerPoint, "Undo" always worked well. Is this usual in Excel VBA? Do I have to add something to the code? Or use different commands?

Maybe it is all too simple for you guys here, but as I'm new to VBA coding for Excel, please help me with this, if you can. Thank you!

RG


(And next step would be how to make the border macro work in diagrams, too.)

[mdmackillop]

You cannot "undo" changes made by a macro. I use this for highlighting yellow/undoing for this reason

Sub Yellow()
With Selection
If .Interior.ColorIndex = 6 Then
 .Interior.ColorIndex = xlNone
 Else
  .Interior.ColorIndex = 6
  End If
  End With
End Sub

[Paul_Hossler]

This seems pretty reliable IF you Control-Z right away. Sometimes the captured initial settings can get forgot

It does not seem to queue it in the undo stack

Option Explicit

Public FillUndoRange As Range
Public FillUndoColor As Long

'http://spreadsheetpage.com/index.php/tip/undoing_a_vba_subroutine/
'https://msdn.microsoft.com/en-us/library/office/ff194135(v=office.15).aspx (Office 2013 and up)

Sub Fill()
    If TypeName(Selection) <> "Range" Then Exit Sub
    
    Set FillUndoRange = Selection
    FillUndoColor = Selection.Interior.Color
    Selection.Interior.Color = RGB(255, 153, 0)
    
    'If a procedure doesn?t use the OnUndo method, the Undo command is disabled.
    'The procedure must use the OnRepeat and OnUndo methods last, to prevent the repeat and undo
    '   procedures from being overwritten by subsequent actions in the procedure
    
    Application.OnRepeat "Fill Sub", "Fill"
    Application.OnUndo "Fill Sub", "FillUndo"
    
End Sub


Public Sub FillUndo()
    FillUndoRange.Parent.Select
    FillUndoRange.Select
    Selection.Interior.Color = FillUndoColor
End Sub

[RandomGerman]

This is interesting - how far different from PowerPoint!

Thank you, both of you.

@mdmackillop: As far as I understand ColorIndex is limited to only 54 pre-defined colors, which makes the use of branding colors nearly impossible.

@Paul: I had some tries with your code and found two issues.
First is, after undoing a coloring, the original grey borders are gone. The range is all white, without visible borders.
Second is, sometimes selected cells turn black after undoing. "Sometimes" means:
1. Select some cells, e.g., B3 : D6, and use the Fill macro.
2. Deselect the range
3. Click Undo
4. Select a range which includes parts of the old range and other cells, too, e.g., D4:E7, and use the fill macro
5. Deselect the range
6. Click Undo

Is this second issue what you meant with "captured initial settings can get forgot"?



At last, new idea, I could go back to my original code and forget about undoing, in case I had a No-Fill- and a Back-to-original-border-Macro.

Is there a command in Excel similar to this PowerPoint thing:

.Fill.Visible = msoFalse

And: What is the original border? When I click on the border tool, it says automatically black - but the borders aren't black.


Thank you so much, guys!
RG

[Paul_Hossler]

As far as I understand ColorIndex is limited to only 54 pre-defined colors, which makes the use of branding colors nearly impossible.

1. There are 56 choices on the palette, but you can change them persistently for a workbook

Sub ChangePalette()
    ThisWorkbook.ResetColors
    
    ThisWorkbook.Colors(24) = RGB(226, 234, 234)
End Sub

What is the original border? When I click on the border tool, it says automatically black - but the borders aren't black.

2. There are the normal cell gridlines that display [View tab, Show, Gridlines], and then there can be borders around the cell or cells

Is there a command in Excel similar to this PowerPoint thing:

3. This should remove any Fill. I usually use xlColorIndexAutomatic

Selection.Interior.Color = xlNone

Selection.Interior.ColorIndex = xlColorIndexNone        . removes fill

Selection.Interior.ColorIndex = xlColorIndexAutomatic ' makes white

4. For some reason, a fill of white (vbWhite) is apparently treated a little differently

This a a slight mod; see if it works for you

 
Public Sub FillUndo()

    FillUndoRange.Parent.Select
    FillUndoRange.Select
    
    With Selection
        If FillUndoColor = vbWhite Then
            .Interior.Color = xlNone
        Else
            .Interior.Color FillUndoColor
        End If
    End With
End Sub

[RandomGerman]

ColorMacros.xlsm

:-(

"Deleting" borders leaves a light blue border now ...

[Paul_Hossler]

Try these

Sub BordersOn()
    On Error GoTo ErMsg
    Selection.Borders.Color = RGB(255, 153, 0)
    
    Exit Sub

ErMsg:
    MsgBox "This macro is for cell borders only, not for gridlines, axes or data point borders in diagrams"
End Sub

Sub BordersOff()
    On Error GoTo ErMsg
    
    Selection.Borders.ColorIndex = xlColorIndexNone
    
    Exit Sub
    
ErMsg:
    MsgBox "This macro is for cell borders only, not for gridlines, axes or data point borders in diagrams"
End Sub

[mdmackillop]

@mdmackillop: As far as I understand ColorIndex is limited to only 54 pre-defined colors, which makes the use of branding colors nearly impossible.

You can use .Color for the full range and also set up a template for your commonly used colours

Sub Colours()Dim x
x = InputBox("My colour index; 0 to clear")
If x = 0 Then
    Selection.Interior.Color = xlNone
    Else
x = Sheets("Colours").Range("A1").CurrentRegion.Cells(x).Interior.Color
    Selection.Interior.Color = x
    End If
End Sub

[Jan Karel Pieterse]

Arguably, you should be creating a custom Theme which has all the branded colours at the right locations and install that theme on the computers. Then set Excel to use that theme. By doing so, the ribbon will automagically contain the right colorset to choose from and all standard Undo levels just work.

[RandomGerman]

Thank you, Paul - this works!

@mdmackillop: Thanks a lot for being so helpful again, but I think, the guys I'm trying to help will prefer Paul's solution.

@Jan Karel: For the usual use I would agree, but in this case the guys I'm trying to help, have to handle two different sets of branding colors. I thought it might be easier to have one as default and the other one as macros. By the way: I have found your explanation, how to make a custom theme default in Excel 2010. (https://answers.microsoft.com/en-us/...c-68b599b31bf5) But it seems, I don't know the right place to store the Book.xltx to make it appear directly, when I open Excel from the task bar. I stored it in C:\Users\MYUSERNAME\AppData\Roaming\Microsoft\Templates and it does not appear as default. Where do I have to store it?

[Jan Karel Pieterse]

It needs to go in xlstart:

%appdata%\Microsoft\Excel\xlstart

[Jan Karel Pieterse]

Silly enough, this does NOT make it as the default when you select File, New. It does when you hit control+n. Consistency is a terrific thing, isn't it?

[Paul_Hossler]

You might consider NOT using it as a 'all the time' default Book.xltx in XLSTART

If you formatted a standard (or several) template as (for ex) CompanyStrandard_External.xlst and CompanyStrandard_Internal.xlst and put it in the user's default templates folder (Options, Save, Default personal templates location) the users could pick the 'standard' when necessary, or something else when required by using File, New

If you have a shared drive / folder you could put the 'gold copy' of the xltx's in a 'standard format' folder for the Workgroup

The 'weird' thing is that you have to use MS Word to set the workgroup folder location, but Excel will look there for it

Word:

Capture.JPG

Excel:

File, New, Custom

Capture2.JPG

Probably a way to do it through the registry but I haven't looked

[RandomGerman]

No, unfortunately this is not working. I put my Book.xltx to %appdata%\Microsoft\Excel\xlstart but it is not appearing as default. Neither with ctrl+N, nor on File ->New -> Empty Workbook. I can choose it via File -> New -> My Templates, but I have to do that everytime - which is the same as dropping it to %appdata%\Microsoft\Templates. It is not becoming default anywhere. Any idea, what I might have done wrong?

I'd be happy to solve this without the registry as I'm always afraid of doing something there on dangerous ground. ;-)

[Paul_Hossler]

No, unfortunately this is not working. I put my Book.xltx to %appdata%\Microsoft\Excel\xlstart but it is not appearing as default. Neither with ctrl+N, nor on File ->New -> Empty Workbook. I can choose it via File -> New -> My Templates, but I have to do that everytime - which is the same as dropping it to %appdata%\Microsoft\Templates. It is not becoming default anywhere. Any idea, what I might have done wrong?

I'd be happy to solve this without the registry as I'm always afraid of doing something there on dangerous ground. ;-)

Yea, FWIW I've never gotten the Book.xltx in XLSTART to work either

I think it has something to do with the various options related to starting Excel, but I've never spent much time investigating.

I prefer to have a discrete set of project-specific templates in my personal templates folder to choose from to use as a starting point

[Jan Karel Pieterse]

@RandomGerman: The name is language specific, so if you use German Excel, look at how an empty workbook is named (Mappe1?), strip out the number and use that name: Mappe.xltx. That should work.

[RandomGerman]

@RandomGerman: The name is language specific, so if you use German Excel, look at how an empty workbook is named (Mappe1?), strip out the number and use that name: Mappe.xltx. That should work.

That's it! I thought about that and had tried yesterday without success, because I used Mappe1.xltx. Without the number it's it! (It's funny - and in a way annoying - that some things are language sensitive in Windows and others are not ... how should anyone know all that?)


Thank you, guys! It's priceless, what you all do here with helping all those less experienced users like me.

[Jan Karel Pieterse]

You're welcome!

[Paul_Hossler]

Silly enough, this does NOT make it as the default when you select File, New. It does when you hit control+n. Consistency is a terrific thing, isn't it?

You're welcome!

I went back and tried it again since you said it worked.

I found out that if I remove my 2 XLSM's in XLSTART (PERSONAL.XLSM and SUB.XLSM) when I start Excel, I do get a initial WB based on book.xltx. If the XLSM's are there, then Excel opens without a WB open

Ctrl-N does work, but I never use it since (old dog, new tricks) I'm so used to File, New and picking the XLTX from the list


Consistency IS a terrific thing, isn't it