In order to perform my most common formatting operations, I’m going to try to use a userform with only those operations on it. These 16 buttons should cover about 95% of the cell formatting I do.
I set the Ctrl+1 shortcut, which normally shows the Format Cells dialog, to show my userform instead. Now that I type that, I should put a button on my userform to go that dialog. Oh well, version 2 I guess. I now have five or six Application.OnKey statements in my PMW. I think a table driven approach might be in order.
Before I show the userform, I make sure a range is selected as opposed to a shape or some such thing.
If TypeName(Selection) = “Range” Then
UFormatting.Show
Else
MsgBox “No cells selected”
End If
End Sub
Most of the code behind the buttons is pretty simple. You could get all of it by recording a macro, in fact. The Gridlines button puts a 25% gray border around to simulate gridlines when a range has an interior fill.
With Selection
Selection.BorderAround xlContinuous, xlThin, , RGB(192, 192, 192)
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(192, 192, 192)
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(192, 192, 192)
End With
End With
End Sub
The Total button puts a single border at the top and a double at the bottom.
cmdDouble_Click
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End Sub
When the userform shows, I attempt to move it to the right of the screen. I figure it will be out of the way most often there.
Me.Top = Application.Top + 125
Me.Left = Application.Left + Application.Width – Me.Width
End Sub
All I really want is the selected cells to be visible, but it’s too much of a pain, particularly with dual monitors. I’ve wanted something like this for a while. Usually when it takes me a long time to make something it’s because it’s too hard (not the case here) or not as useful as I first thought. We’ll see. I doubt this will ever be useful as a general purpose add-in for two reasons. First, the buttons that I want aren’t necessarily the buttons that you want. Sure there’s some overlap, but if you can’t have it just the way you want, the way that covers 95% of your formatting needs, then it’s no good. Or it gets more buttons to cover everyone, which is exactly what the Format Cells dialog already is. Second, there’s no benefit here in 2007 or beyond because a custom tab on the ribbon has all the keyboard support you could want. But if it makes my remaining 2003 days more comfortable, then it’s a winner for me.
Did you choose not to put these on a custom toolbar to save the screen real estate? It seems like few enough of them to make a toolbar. Just wondering.
I assume that Dick’s using Excel 2007, so a floating toolbar isn’t an option.
You can simplify the coding by executing the old Excel 2003 toolbar commands directly (even though they aren’t visible in XL 2007). For example:
Application.CommandBars(“Formatting”).Controls(“&Percent Style”).Execute
I don’t put them on a toolbar because I want a keyboard method. I’m half and half between 2007 and 2003. For 2007, I just made a custom tab with all that I want and the keyboard support comes with it automatically. For 2003, I need to be able to have a keyboard hotkey or I might as well just use the existing tools. I guess I could have made them a menu with accelerators.
Why not make then on a actual task pane? maybe a good start off for a VSTO addin. I Dan dare you to give it a goooooo!
Can I make task panes in 2003? For some reason I thought that was introduced in 2007. Anyway, it’s on my list to be my first .NET project!
I know your bias toward keyboard vs. mouse (mine too), so the hotkey opens the dialogcan you then tab between the buttons on the user form and return? If not, they you’re back to the mouse, right?
I haven’t done a lot of user form programming so I’m not sureI know you can tab between entry fields but didn’t know if you could change the focus on a collection of buttons that way.
Dick –
Stephen Bullen did some work to convert user forms to task panes, and he’s got some kind of download on his web site that will do the conversion for you. Task panes came out in 2003 (if not 2002), but in 2003 you needed VSTO to customize them. I think 2007 makes it easier, but I’ve never tired it.
>>Can I make task panes in 2003? For some reason I thought that was introduced in 2007. Anyway, it’s on my list to be my first .NET project!
Yep. you can do it in all 2003 and 2002, although they are a little different (I hope I’m right now that I’ve said that!!!)
Jon,
The download you are referring to is a user form that “mimics” as Task pane.
Task panes are there from 2002.
To create/customize a task pane you need Visual Studio + VSTO in 2002/2003/2007/2010 or VB6
You are all wrong! ;-)
Stephen’s down load is a com dll(addin), that must be deployed with the vba project. and it only works with 2007 (i guess). the ones in 07 are slightly different from the one in xp and 03.
With panes (actions or task) you have to create a com object, which you cant do via VBA so hence your buggered.
http://msdn.microsoft.com/en-us/library/aa942864.aspx
http://blogs.msdn.com/gabhan_berry/default.aspx
There is a possible work around which I have been playing with but it’s not very good.
Dick: Here’s something, well a bunch of somethings, to think about.
1) show the userform modeless. 2) Shrink the buttons so that they, and consequently the uf, occupies a lot less space.
You could continue using the above in 2007.
Alternatively, and this is not very well thought out in terms of execution…for 2003 create a toolbar. Trap CTRL+1 to show the toolbar. While the toolbar is visible trap all number keys. The user types 2 numbers to select a toolbar button (1st number identifies the row, the 2nd the column). Execute that button. Release all the trapped keystrokes when the toolbar closes.
I chose modal because I don’t know how to get from the spreadsheet to the userform and back without using the mouse. I tried shrinking the buttons, but I can’t show the picture and the caption at a smaller button size. Maybe there’s a way to resize the picture, but I don’t know what it is. I could just get rid of the picture and go with all text.
I love the toolbar idea. I could put all the tools on one row and group them to it’s easy to tell which is the, say, 6th button. Oh, maybe not. I’m up to 19 buttons on this thing now, so your idea of rows and columns might be easier.