I want to create an icon that formats the selected cells as a
date(mm/dd/yyyy). I need to learn how to make an icon in Excel and attach it
to the formatting icon bar with the appropriate code behind it. As always,
any help would be greatly appreciated.
--
Michael Conroy
Stamford, CT

Michael, take a look at 'CommandBars' in the VBA help. In the code
below I am just adding a button to the 'Standard' toolbar, however
there isn't much diffrence if you are adding it to a custom toolbar.
The deal with changing the icons is that Excel won't let you add a
custom icon. What have to do is play within the limits of what office
has loaded for icons, which is quite extensive. The 'FaceId' is the
key, see below. For instance in Office 2003 there are buttons with
ID's from 2 to 10037. There are several excel workbook applications
people have made that show the face id's. do a search from google
groups, its search capability within this group is excellent—search
for 'show face id'.
http://groups.google.com/group/micro...g/topics?hl=en

Sub Auto_Open()
'Add Buttons to the Standard toolbar
'Change view between normal and pagebreak preview
Dim btnCap$
Dim bPVfmt As Object

btnCap = "Change View"
On Error Resume Next
Application.CommandBars("Standard"). _
Controls(btnCap).Delete
On Error GoTo 0
Set bPVfmt = Application. _
CommandBars("Standard"). _
Controls.Add(Type:=msoControlButton, _
Before:=9, temporary:=True)
With bPVfmt
'FaceId is what changes the 'icon'
'Excel won't let you make custom icons
.FaceId = 10022
.Tag = btnCap
.Caption = btnCap
.OnAction = "myPageView"
End With
End Sub

HTH—Lonnie M.
Groton, CT

Lonnie,
Thanks for your response. After reading through your answer and the
newsgroup, I now realize I stumbled onto an issue with Excel that is beyond
what I was asking about, and that is putting a custom face on the icon. I
care very little what face is on the icon. This icon is just for me and all I
want it to do is to show up everytime I open Excel, in the formatting bar
with the rest of the selected icons, and run the formatting code behind it.
Thanks again for the code and the command bar suggestion in the help menu, I
will run with that.
--
Michael Conroy
Stamford, CT


"Lonnie M." wrote:

> Michael, take a look at 'CommandBars' in the VBA help. In the code
> below I am just adding a button to the 'Standard' toolbar, however
> there isn't much diffrence if you are adding it to a custom toolbar.
> The deal with changing the icons is that Excel won't let you add a
> custom icon. What have to do is play within the limits of what office
> has loaded for icons, which is quite extensive. The 'FaceId' is the
> key, see below. For instance in Office 2003 there are buttons with
> ID's from 2 to 10037. There are several excel workbook applications
> people have made that show the face id's. do a search from google
> groups, its search capability within this group is excellent—search
> for 'show face id'.
> http://groups.google.com/group/micro...g/topics?hl=en
>
> Sub Auto_Open()
> 'Add Buttons to the Standard toolbar
> 'Change view between normal and pagebreak preview
> Dim btnCap$
> Dim bPVfmt As Object
>
> btnCap = "Change View"
> On Error Resume Next
> Application.CommandBars("Standard"). _
> Controls(btnCap).Delete
> On Error GoTo 0
> Set bPVfmt = Application. _
> CommandBars("Standard"). _
> Controls.Add(Type:=msoControlButton, _
> Before:=9, temporary:=True)
> With bPVfmt
> 'FaceId is what changes the 'icon'
> 'Excel won't let you make custom icons
> .FaceId = 10022
> .Tag = btnCap
> .Caption = btnCap
> .OnAction = "myPageView"
> End With
> End Sub
>
> HTH—Lonnie M.
> Groton, CT
>

Michael, I should have read a little closer. The 'OnAction' property
is where you would place the name of the public macro that would
format your selected cells.

'The public procedure/macro in a Standard Module
Public Sub myDateFmt()
Selection.NumberFormat = "mm/dd/yyyy"
End Sub

'The open event to create the button in the 'ThisWorkbook' Module:
Sub Auto_Open()
'Add Buttons to the Standard toolbar
'Change view between normal and pagebreak preview
Dim btnCap$
Dim bPVfmt As Object

btnCap = "Format As Date"
On Error Resume Next
Application.CommandBars("Formatting"). _
Controls(btnCap).Delete
On Error GoTo 0
Set bPVfmt = Application. _
CommandBars("Formatting"). _
Controls.Add(Type:=msoControlButton, _
Before:=10, temporary:=True)
With bPVfmt
.FaceId = 9589 'calendar button
.Tag = btnCap
.Caption = btnCap
.OnAction = "myDateFmt"
End With
End Sub


HTH—Lonnie M.

Lonnie
Thanks for following up. It's getting late in Connecticut so I will have to
try this tomorrow. Will let you know how it turns out.
--
Michael Conroy
Stamford, CT


"Lonnie M." wrote:

> Michael, I should have read a little closer. The 'OnAction' property
> is where you would place the name of the public macro that would
> format your selected cells.
>
> 'The public procedure/macro in a Standard Module
> Public Sub myDateFmt()
> Selection.NumberFormat = "mm/dd/yyyy"
> End Sub
>
> 'The open event to create the button in the 'ThisWorkbook' Module:
> Sub Auto_Open()
> 'Add Buttons to the Standard toolbar
> 'Change view between normal and pagebreak preview
> Dim btnCap$
> Dim bPVfmt As Object
>
> btnCap = "Format As Date"
> On Error Resume Next
> Application.CommandBars("Formatting"). _
> Controls(btnCap).Delete
> On Error GoTo 0
> Set bPVfmt = Application. _
> CommandBars("Formatting"). _
> Controls.Add(Type:=msoControlButton, _
> Before:=10, temporary:=True)
> With bPVfmt
> .FaceId = 9589 'calendar button
> .Tag = btnCap
> .Caption = btnCap
> .OnAction = "myDateFmt"
> End With
> End Sub
>
>
> HTH—Lonnie M.
>