Excel 2003 toolbar with custom image
 

Hi,
Is it possible to use a custom image for excel toolbar? I mean using
external picture such as .png, .img...etc. The toolbar was created in xla
format using vba.

Thanks.

Excel 2003 toolbar with custom image
 

You can certainly use your 16x16 picture for the icon property of a button,
but nothing larger than that on a commandbar.

It Excel 2007 you can use 'Gallery' for your own pictures in your
customized Ribbon.

Regards,
Peter T

"Westman" wrote in message
...
Hi,
Is it possible to use a custom image for excel toolbar? I mean using
external picture such as .png, .img...etc. The toolbar was created in xla
format using vba.

Thanks.

Excel 2003 toolbar with custom image
 

Thanks for the reply.

What is the best way to do that? How to use external picture in my toolbar?



"Peter T" wrote:

You can certainly use your 16x16 picture for the icon property of a button,
but nothing larger than that on a commandbar.

It Excel 2007 you can use 'Gallery' for your own pictures in your
customized Ribbon.

Regards,
Peter T

"Westman" wrote in message
...
Hi,
Is it possible to use a custom image for excel toolbar? I mean using
external picture such as .png, .img...etc. The toolbar was created in xla
format using vba.

Thanks.

Excel 2003 toolbar with custom image
 

Simplest way would be first to "Insert" the picture onto a sheet, then
CopyPicture and PasteFace. Adapt the following to your needs -

Sub test()
Dim sPic As String, sPath As String
Dim cbr As CommandBar
Dim cbt As CommandBarButton

On Error Resume Next
' delete the old bar if it exists
CommandBars("TestBar").Delete
On Error GoTo 0

sPath = "C:\Documents and Settings\Owner\My Documents\My Pictures\"

Set cbr = CommandBars.Add("TestBar", temporary:=True)
Set cbt = cbr.Controls.Add(msoControlButton)

sPic = "myPic.bmp"

If GetAndCopyPic(sPath, sPic) Then
cbt.PasteFace
Else
' what to do if failed to get the picture
MsgBox "failed to copy " & sPic
End If

cbt.OnAction = "myMacro"
cbt.Visible = True

cbr.Visible = True

End Sub

Function GetAndCopyPic(sPath As String, sPic As String) As Boolean
Dim sName As String
Dim ws As Worksheet
Dim pic As Picture

On Error GoTo errH
Set ws = ThisWorkbook.Worksheets("Sheet1")
sName = Left$(sPic, InStrRev(sPic, ".") - 1)

On Error Resume Next
Set pic = ws.Pictures(sName)
On Error GoTo errH

If pic Is Nothing Then
Set pic = ws.Pictures.Insert(sPath & sPic)
pic.Name = sName
End If

pic.CopyPicture
GetAndCopyPic = True

Exit Function

errH:
Debug.Print Err.Description
''' uncoment for testing
'stop
'resume nextion
End Function


Regards,
Peter T

"Westman" wrote in message
...
Thanks for the reply.

What is the best way to do that? How to use external picture in my
toolbar?



"Peter T" wrote:

You can certainly use your 16x16 picture for the icon property of a
button,
but nothing larger than that on a commandbar.

It Excel 2007 you can use 'Gallery' for your own pictures in your
customized Ribbon.

Regards,
Peter T

"Westman" wrote in message
...
Hi,
Is it possible to use a custom image for excel toolbar? I mean using
external picture such as .png, .img...etc. The toolbar was created in
xla
format using vba.

Thanks.

Excel 2003 toolbar with custom image
 

Thanks, it worked.

"Peter T" wrote:

Simplest way would be first to "Insert" the picture onto a sheet, then
CopyPicture and PasteFace. Adapt the following to your needs -

Sub test()
Dim sPic As String, sPath As String
Dim cbr As CommandBar
Dim cbt As CommandBarButton

On Error Resume Next
' delete the old bar if it exists
CommandBars("TestBar").Delete
On Error GoTo 0

sPath = "C:\Documents and Settings\Owner\My Documents\My Pictures\"

Set cbr = CommandBars.Add("TestBar", temporary:=True)
Set cbt = cbr.Controls.Add(msoControlButton)

sPic = "myPic.bmp"

If GetAndCopyPic(sPath, sPic) Then
cbt.PasteFace
Else
' what to do if failed to get the picture
MsgBox "failed to copy " & sPic
End If

cbt.OnAction = "myMacro"
cbt.Visible = True

cbr.Visible = True

End Sub

Function GetAndCopyPic(sPath As String, sPic As String) As Boolean
Dim sName As String
Dim ws As Worksheet
Dim pic As Picture

On Error GoTo errH
Set ws = ThisWorkbook.Worksheets("Sheet1")
sName = Left$(sPic, InStrRev(sPic, ".") - 1)

On Error Resume Next
Set pic = ws.Pictures(sName)
On Error GoTo errH

If pic Is Nothing Then
Set pic = ws.Pictures.Insert(sPath & sPic)
pic.Name = sName
End If

pic.CopyPicture
GetAndCopyPic = True

Exit Function

errH:
Debug.Print Err.Description
''' uncoment for testing
'stop
'resume nextion
End Function


Regards,
Peter T

"Westman" wrote in message
...
Thanks for the reply.

What is the best way to do that? How to use external picture in my
toolbar?



"Peter T" wrote:

You can certainly use your 16x16 picture for the icon property of a
button,
but nothing larger than that on a commandbar.

It Excel 2007 you can use 'Gallery' for your own pictures in your
customized Ribbon.

Regards,
Peter T

"Westman" wrote in message
...
Hi,
Is it possible to use a custom image for excel toolbar? I mean using
external picture such as .png, .img...etc. The toolbar was created in
xla
format using vba.

Thanks.