Create customised control Face Id's and attach them to Excel control controls

Hi All,

I've written some simple code below to add some toolbar controls to a custom commandbar. I want to put custom built face iIs on the buttons and it sounds like this can be done by pasting the Face Id's from the Clipboard. But this is outside my Excel programming skill, how do I create, then acess custom button faceid's via the Clipboard?

Cheers

Dave

Public Const CB As String = "Colour Toolbar"

Sub NewToolbar()
Dim Ctrl       As CommandBarControl
Dim TB         As CommandBar
    On Error Resume Next
    Application.CommandBars(CB).Delete
    On Error GoTo 0
    Set TB = Application.CommandBars.Add(Name:=CB, Position:=msoBarTop)
    TB.Visible = True
        For i = 1 To 3
        Set Ctrl = TB.Controls.Add(Type:=msoControlButton)
        Ctrl.FaceId = 400 + i  'Random id's
        Ctrl.Caption = "Control " & i
    Next
End Sub
LVL 50
DaveAsked:
Who is Participating?
 
Suat OzgurWeb / Application DeveloperCommented:
0
 
rajaamirapuCommented:
Clipboard.SetData LoadPicture(App.Path & "\Help.bmp")
    Set cbSubCommandBar = cbMenuCommandBar.Controls.Add(1)
    cbSubCommandBar.Caption = "Help"
    cbSubCommandBar.PasteFace
Hope this helps
See the link
0
 
DaveAuthor Commented:
no link?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rajaamirapuCommented:
I missed the Link Using PasteFace you can do this
Ctrl.FaceId = 400 + i  'Random id's
Ctrl.Caption = "Control " & i
Clipboard.SetData LoadPicture(App.Path & "\Help.bmp")
cbSubCommandBar.PasteFace

0
 
DaveAuthor Commented:
Hi,

I cant seem to load the ClipBoard from Excel

Cheers

Dave
0
 
Suat OzgurWeb / Application DeveloperCommented:
Yes, Dave. VBA doesn't have Clipboard object but VB.

Have you seen the second method ?

http://www.mvps.org/word/FAQs/MacrosVBA/SetCustomButtonImage.htm

Suat
0
 
DaveAuthor Commented:
I cant believe I missed that link  - thanks Suat

Thanks to you as well rajaamirapu , I've given you some points for your effort

Cheers

Dave
0
 
Suat OzgurWeb / Application DeveloperCommented:
Great to hear it helps!!!

I think I have free access on VIP next month too!! If it is still 3000 points... LOL!! ;)

Suat
(Thanks for the grade, Dave)
(By the way, am I the only one who gets notification emails from EE very but VERY late. It was manual refresh this time to make sure.  I have been getting notifications TOMORROW!)
0
 
DaveAuthor Commented:
Me too - the EE mail is arriving up to a day late
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.