You might want to look at KeepItCool's post on working with the bitmap and
mask:

http://tinyurl.com/cdb9m

--
Regards,
Tom Ogilvy


"HSalim[MVP]" wrote in message
...
Right direction is right!
Thanks for the reply - that is exactly what I wanted and did not know how
to
do.
- I'll try it out right now.

I was trying to find ways to do this. I found out that each buttonface
can
has a picture and a mask property, both if which need to be supplied - and
I
did not want to distribute 16 image files with my add-in.

Thanks also for the cel/select tip.

Regards
Habib



"Peter T" <peter_t@discussions wrote in message
...
: Hope somewhere is in the right direction! Couple of points -
:
: If your file is an addin all worksheets are of course hidden. Suggest
don't
: use Select, rarely necessary and particularly not in a non-active sheet.
: Change -
:
: Range("Buttons").Select
: For Each cell In Selection
:
: (I prefer not to use Each "Cell" which is a keyword)
:
: Dim cel as Range
: For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
:
: Why not store your icons, assuming you want to use them, as "Pictures"
with
: their names in your Buttons range
:
: ThisWorkbook.Worksheets.Shapes(cel.Offset(, 5).Value).Copypicture
: .PasteFace
:
: Regards,
: Peter T
:
:
: "HSalim[MVP]" wrote in message
: ...
: Peter,
: We are getting somewhere now !
:
: I was fighting the idea of creating a toolbar at run-time but I
actually
: am
: beginning to like it.
: In the worksheet (which will get hidden) Ihave a named range called
: buttons
: where I can list all the buttons that will be created on Auto_Open. -
here
: is the relevant code-snippet.
:
: Range("Buttons").Select
: For Each cell In Selection
: cBar.Controls.Add Type:=msoControlButton
: With cBar.Controls(cell.Value)
: .OnAction = cell.Offset(, 1).Value
: .FaceId = cell.Offset(, 2).Value
: .Caption = cell.Offset(, 3).Value
: .TooltipText = cell.Offset(, 4).Value
: .Style = msoButtonCaption 'was msoButtonIconAndCaption
:
: End With
: Next
:
:
: If I edit a built-in button image (face) the faceId becomes 0. I know
it
: is
: being stored somewhere - i can copy the picture and the mask and even
save
: it to a file but I can't seem to access it.
:
: Or at least, there does not seem to be an elegant way to access it. I
: dont
: want to dostribute a bunch of image files with my add-in, so I guess
I'll
: just keep it simple and make it a text toolbar.
:
: Thanks for all your help.
:
: Regards
: Habib
:
:
:
:
:
: "Peter T" <peter_t@discussions wrote in message
: ...
: : Hi Habib,
: :
: : Are you "Attaching" a toolbar, try creating one:
: :
: : Paste the following into a new workbook.
: : File/SaveAs, in the "Save As type" dropdown scroll down to Microsoft
: Addin
: : and select, Save.
: :
: : Can load from file first time to add the toolbar, thereafter click
on
: : toolbar to load. But to avoid the macro warning (if Medium & in
Trusted
: : sources - trust all installed addins checked) install as an Addin.
If
: always
: : going to be installed as an addin then set the Temporary = True
option
: when
: : creating the Toolbar.
: :
: : Sub Auto_Open()
: : Dim cBar As CommandBar
: : Dim i As Long
: :
: : On Error Resume Next
: : Set cBar = Application.CommandBars("MyTestBar")
: : On Error GoTo 0
: :
: : If Not cBar Is Nothing Then
: :
: : If cBar.Controls.Count < 2 Then
: : cBar.Delete
: : Set cBar = Nothing
: : End If
: : End If
: :
: : If cBar Is Nothing Then
: : Set cBar = Application.CommandBars.Add(Name:="MyTestBar")
: : 'or .Add.(Name:="MyTestBar", Temporary = True)
: : For i = 1 To 2
: : cBar.Controls.Add Type:=msoControlButton
: : Next i
: : End If
: :
: : With cBar.Controls(1)
: : .OnAction = "Macro1"
: : .FaceId = 482
: : .TooltipText = "Macro1"
: : End With
: : With cBar.Controls(2)
: : .OnAction = "Macro2"
: : .FaceId = 483
: : .TooltipText = "Macro2"
: : End With
: :
: : cBar.Enabled = True
: : cBar.Visible = True
: : End Sub
: :
: : Sub Macro1()
: : MsgBox "Macro1"
: : End Sub
: : Sub Macro2()
: :
: : If MsgBox("Close me", vbYesNo, "Macro2") = vbYes Then
: : Application.OnTime Now, "CloseMe"
: : End If
: : End Sub
: :
: : Sub CloseMe()
: : ThisWorkbook.Close
: : End Sub
: :
: : '' in thisworkbook module
: : Private Sub Workbook_BeforeClose(Cancel As Boolean)
: : On Error Resume Next
: : 'Application.CommandBars("MyTestBar").Delete
: :
: : 'or not installed as an addin
: : Application.CommandBars("MyTestBar").Visible = False
: :
: : End Sub
: :
: : Regards,
: : Peter T
: :
: : "HSalim[MVP]" wrote in message
: : ...
: : Tom and Peter,
: :
: : Thanks for the assistance, and the KB links.
: :
: : I have
: : a workbook (lockbox.xls)
: : a toolbar - also called Lockbox, with about 7 buttons. macros
point
: to
: : routines in Lockbox.xls
: :
: : If I convert the workbook to an add-in, - lockbox.xla the toolbar
does
: not
: : seem to follow
: : along, it still points to the lockbox.xls.
: :
: : If I convert lockbox.xls into a macro in the immediate window -
: : thisworkbook.isaddin = true
: : then the user gets a macro warning.
: :
: : How can I make it all nicely integrated without generating
warnings?
: :
: : Thanks
: : Habib
: :
: : "Peter T" <peter_t@discussions wrote in message
: : ...
: : : Hi HAbib,
: : :
: : : Referring to your OP perhaps you don't want to delete the
toolbar
: when
: : the
: : : addin unloads. If so don't set the toolbar's Temporary property
True
: and
: : : don't delete in the file's close event.
: : :
: : : With this arrangement it's NOT necessary to install the addin
into
: the
: : addin
: : : manager (with code or with user Tools / Options / Addins.).
: : :
: : : When user clicks on your toolbar if your addin is not already
loaded
: it
: : will
: : : automatically load.
: : :
: : : You could have an additional button linking to a routine to
close
: the
: : addin
: : : if no longer required in the current session. Maybe also make
the
: : toolbar
: : : invisible on unload, eg when Excel closes if not before.
: : :
: : : In the open routine test if your toolbar exists, if not create
it,
: if
: it
: : : does check it is all correct. There are plenty of unused icons
that
: if
: : : suitable might avoid necessity of creating custom icons.
: : :
: : : Advise user to simply double click the addin in it's folder
first
: time
: : to
: : : load and create the toolbar. When user closes Excel there should
be
: no
: : other
: : : running instances to ensure it gets stored in user's toolbar /
*.xlb
: : file).
: : :
: : : This is certainly NOT the normal way to distribute an addin and
: could
: : : antagonise users. However it might fit your user's particular
: : requirements,
: : : a) knows how to make the toolbar visible, b) infrequent use
(addin
: only
: : gets
: : : loaded when specifically required), c) knows how to recover the
: toolbar
: : if
: : : it accidentally gets deleted (load addin from file).
: : :
: : : Regards,
: : : Peter T
: : :
: : : "HSalim[MVP]" wrote in message
: : : ...
: : : Tom,
: : :
: : : Thanks for the super-fast reply, and for all the links.
: : : I did find Jan Karel's site, and have been trying to follow
his
: : advice.
: : :
: : : I cant put my finger on what I am doing wrong, but I think I
am
: : : permanently
: : : deleting the toolbar.
: : : I've had to recreate my toolbar a few times now as I seem to
: delete
: : it.
: : : Is there a way to backup a toolbar?
: : : I could create the toolbar in code like JKP does - perhaps
that
is
: the
: : : answer.
: : : How do I save the button images so that I can create the
toolbar
: with
: : the
: : : images I want?
: : :
: : : REgards
: : : HAbib
: : :
: : :
: : : "Tom Ogilvy" wrote in message
: : : ...
: : : : http://support.microsoft.com/?id=167909
: : : : XL: Securing Visual Basic Code in Microsoft Excel
: : : :
: : : : http://support.microsoft.com/?id=156942
: : : : XL97: How to Create an Add-in File in Microsoft Excel 97
: : : :
: : : : http://support.microsoft.com/?id=211563
: : : : How to create an add-in file in Excel 2000
: : : :
: : : :
: :
: http://msdn.microsoft.com/library/en...xceladdins.asp
: : : :
: : : :
: : :
: : :
: :
: :
:
:

http://msdn.microsoft.com/library/en...tingexceladdin.
: : : asp
: : : :
: : : : http://www.j-walk.com/ss/excel/tips/tip45.htm
: : : : Excel 97: Creating Add-Ins
: : : :
: : : : You addin should delete any existing version of the toolbar
and
: : create
: : a
: : : new
: : : : version when it is loaded. It should also mark it as
temporary
: and
: : : delete
: : : it
: : : : in the beforeclose event. The creation can be done in the
: : : workbook_open
: : : : event of the addin.
: : : :
: : : : If the user will only load the addin when they want to use
it,
: then
: : this
: : : : should be sufficient. If they will load the addin all the
time,
: : then
: : it
: : : : depends on the user. In tools=Customize, they can select
: whether
: : the
: : : bar
: : : : is visible or not. If that is too hard, then perhaps you
could
: : provide
: : : a
: : : : function in your addin that they can run from
: Tools=Macro=Macros
: : or
: : : you
: : : : can add an additional menu choice in the tools menu.
: : : :
: : : : --
: : : : Regards,
: : : : Tom Ogilvy
: : : :
: : : :
: : : :
: : : : "HSalim[MVP]" wrote in message
: : : : ...
: : : : How do I create an add-in and distribute it to others?
: : : : I have a toolbar associated with the add-in. The user
wants
: the
: : : toolbar
: : : : to
: : : : be invisible most of the time. When she wants to use the
: add-in -
: : : : approximately once a month, she will make the toolbar
visible.
: : : : What is the best way to achieve this?
: : : : Thanks
: : : : Habib
: : : :
: : : :
: : : :
: : : :
: : :
: : :
: : :
: : :
: :
: :
: :
: :
: :
:
:
:
:

Tom
That is just amazing! I just gave up hunting around in the help files, and
was going to ask for more help when Voila! that answer is waiting for me.

Thanks.
Habib



"Tom Ogilvy" wrote in message
...
: You might want to look at KeepItCool's post on working with the bitmap and
: mask:
:
: http://tinyurl.com/cdb9m
:
: --
: Regards,
: Tom Ogilvy
:
:
: "HSalim[MVP]" wrote in message
: ...
: Right direction is right!
: Thanks for the reply - that is exactly what I wanted and did not know
how
: to
: do.
: - I'll try it out right now.
:
: I was trying to find ways to do this. I found out that each buttonface
: can
: has a picture and a mask property, both if which need to be supplied -
and
: I
: did not want to distribute 16 image files with my add-in.
:
: Thanks also for the cel/select tip.
:
: Regards
: Habib
:
:
:
: "Peter T" <peter_t@discussions wrote in message
: ...
: : Hope somewhere is in the right direction! Couple of points -
: :
: : If your file is an addin all worksheets are of course hidden. Suggest
: don't
: : use Select, rarely necessary and particularly not in a non-active
sheet.
: : Change -
: :
: : Range("Buttons").Select
: : For Each cell In Selection
: :
: : (I prefer not to use Each "Cell" which is a keyword)
: :
: : Dim cel as Range
: : For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
: :
: : Why not store your icons, assuming you want to use them, as "Pictures"
: with
: : their names in your Buttons range
: :
: : ThisWorkbook.Worksheets.Shapes(cel.Offset(, 5).Value).Copypicture
: : .PasteFace
: :
: : Regards,
: : Peter T
: :
: :
: : "HSalim[MVP]" wrote in message
: : ...
: : Peter,
: : We are getting somewhere now !
: :
: : I was fighting the idea of creating a toolbar at run-time but I
: actually
: : am
: : beginning to like it.
: : In the worksheet (which will get hidden) Ihave a named range called
: : buttons
: : where I can list all the buttons that will be created on
Auto_Open. -
: here
: : is the relevant code-snippet.
: :
: : Range("Buttons").Select
: : For Each cell In Selection
: : cBar.Controls.Add Type:=msoControlButton
: : With cBar.Controls(cell.Value)
: : .OnAction = cell.Offset(, 1).Value
: : .FaceId = cell.Offset(, 2).Value
: : .Caption = cell.Offset(, 3).Value
: : .TooltipText = cell.Offset(, 4).Value
: : .Style = msoButtonCaption 'was
msoButtonIconAndCaption
: :
: : End With
: : Next
: :
: :
: : If I edit a built-in button image (face) the faceId becomes 0. I
know
: it
: : is
: : being stored somewhere - i can copy the picture and the mask and
even
: save
: : it to a file but I can't seem to access it.
: :
: : Or at least, there does not seem to be an elegant way to access it.
I
: : dont
: : want to dostribute a bunch of image files with my add-in, so I guess
: I'll
: : just keep it simple and make it a text toolbar.
: :
: : Thanks for all your help.
: :
: : Regards
: : Habib
: :
: :
: :
: :
: :
: : "Peter T" <peter_t@discussions wrote in message
: : ...
: : : Hi Habib,
: : :
: : : Are you "Attaching" a toolbar, try creating one:
: : :
: : : Paste the following into a new workbook.
: : : File/SaveAs, in the "Save As type" dropdown scroll down to
Microsoft
: : Addin
: : : and select, Save.
: : :
: : : Can load from file first time to add the toolbar, thereafter click
: on
: : : toolbar to load. But to avoid the macro warning (if Medium & in
: Trusted
: : : sources - trust all installed addins checked) install as an Addin.
: If
: : always
: : : going to be installed as an addin then set the Temporary = True
: option
: : when
: : : creating the Toolbar.
: : :
: : : Sub Auto_Open()
: : : Dim cBar As CommandBar
: : : Dim i As Long
: : :
: : : On Error Resume Next
: : : Set cBar = Application.CommandBars("MyTestBar")
: : : On Error GoTo 0
: : :
: : : If Not cBar Is Nothing Then
: : :
: : : If cBar.Controls.Count < 2 Then
: : : cBar.Delete
: : : Set cBar = Nothing
: : : End If
: : : End If
: : :
: : : If cBar Is Nothing Then
: : : Set cBar = Application.CommandBars.Add(Name:="MyTestBar")
: : : 'or .Add.(Name:="MyTestBar", Temporary = True)
: : : For i = 1 To 2
: : : cBar.Controls.Add Type:=msoControlButton
: : : Next i
: : : End If
: : :
: : : With cBar.Controls(1)
: : : .OnAction = "Macro1"
: : : .FaceId = 482
: : : .TooltipText = "Macro1"
: : : End With
: : : With cBar.Controls(2)
: : : .OnAction = "Macro2"
: : : .FaceId = 483
: : : .TooltipText = "Macro2"
: : : End With
: : :
: : : cBar.Enabled = True
: : : cBar.Visible = True
: : : End Sub
: : :
: : : Sub Macro1()
: : : MsgBox "Macro1"
: : : End Sub
: : : Sub Macro2()
: : :
: : : If MsgBox("Close me", vbYesNo, "Macro2") = vbYes Then
: : : Application.OnTime Now, "CloseMe"
: : : End If
: : : End Sub
: : :
: : : Sub CloseMe()
: : : ThisWorkbook.Close
: : : End Sub
: : :
: : : '' in thisworkbook module
: : : Private Sub Workbook_BeforeClose(Cancel As Boolean)
: : : On Error Resume Next
: : : 'Application.CommandBars("MyTestBar").Delete
: : :
: : : 'or not installed as an addin
: : : Application.CommandBars("MyTestBar").Visible = False
: : :
: : : End Sub
: : :
: : : Regards,
: : : Peter T
: : :
: : : "HSalim[MVP]" wrote in message
: : : ...
: : : Tom and Peter,
: : :
: : : Thanks for the assistance, and the KB links.
: : :
: : : I have
: : : a workbook (lockbox.xls)
: : : a toolbar - also called Lockbox, with about 7 buttons. macros
: point
: : to
: : : routines in Lockbox.xls
: : :
: : : If I convert the workbook to an add-in, - lockbox.xla the
toolbar
: does
: : not
: : : seem to follow
: : : along, it still points to the lockbox.xls.
: : :
: : : If I convert lockbox.xls into a macro in the immediate window -
: : : thisworkbook.isaddin = true
: : : then the user gets a macro warning.
: : :
: : : How can I make it all nicely integrated without generating
: warnings?
: : :
: : : Thanks
: : : Habib
: : :
: : : "Peter T" <peter_t@discussions wrote in message
: : : ...
: : : : Hi HAbib,
: : : :
: : : : Referring to your OP perhaps you don't want to delete the
: toolbar
: : when
: : : the
: : : : addin unloads. If so don't set the toolbar's Temporary
property
: True
: : and
: : : : don't delete in the file's close event.
: : : :
: : : : With this arrangement it's NOT necessary to install the addin
: into
: : the
: : : addin
: : : : manager (with code or with user Tools / Options / Addins.).
: : : :
: : : : When user clicks on your toolbar if your addin is not already
: loaded
: : it
: : : will
: : : : automatically load.
: : : :
: : : : You could have an additional button linking to a routine to
: close
: : the
: : : addin
: : : : if no longer required in the current session. Maybe also make
: the
: : : toolbar
: : : : invisible on unload, eg when Excel closes if not before.
: : : :
: : : : In the open routine test if your toolbar exists, if not create
: it,
: : if
: : it
: : : : does check it is all correct. There are plenty of unused icons
: that
: : if
: : : : suitable might avoid necessity of creating custom icons.
: : : :
: : : : Advise user to simply double click the addin in it's folder
: first
: : time
: : : to
: : : : load and create the toolbar. When user closes Excel there
should
: be
: : no
: : : other
: : : : running instances to ensure it gets stored in user's toolbar /
: *.xlb
: : : file).
: : : :
: : : : This is certainly NOT the normal way to distribute an addin
and
: : could
: : : : antagonise users. However it might fit your user's particular
: : : requirements,
: : : : a) knows how to make the toolbar visible, b) infrequent use
: (addin
: : only
: : : gets
: : : : loaded when specifically required), c) knows how to recover
the
: : toolbar
: : : if
: : : : it accidentally gets deleted (load addin from file).
: : : :
: : : : Regards,
: : : : Peter T
: : : :
: : : : "HSalim[MVP]" wrote in message
: : : : ...
: : : : Tom,
: : : :
: : : : Thanks for the super-fast reply, and for all the links.
: : : : I did find Jan Karel's site, and have been trying to follow
: his
: : : advice.
: : : :
: : : : I cant put my finger on what I am doing wrong, but I think I
: am
: : : : permanently
: : : : deleting the toolbar.
: : : : I've had to recreate my toolbar a few times now as I seem to
: : delete
: : : it.
: : : : Is there a way to backup a toolbar?
: : : : I could create the toolbar in code like JKP does - perhaps
: that
: is
: : the
: : : : answer.
: : : : How do I save the button images so that I can create the
: toolbar
: : with
: : : the
: : : : images I want?
: : : :
: : : : REgards
: : : : HAbib
: : : :
: : : :
: : : : "Tom Ogilvy" wrote in message
: : : : ...
: : : : : http://support.microsoft.com/?id=167909
: : : : : XL: Securing Visual Basic Code in Microsoft Excel
: : : : :
: : : : : http://support.microsoft.com/?id=156942
: : : : : XL97: How to Create an Add-in File in Microsoft Excel 97
: : : : :
: : : : : http://support.microsoft.com/?id=211563
: : : : : How to create an add-in file in Excel 2000
: : : : :
: : : : :
: : :
: :
http://msdn.microsoft.com/library/en...xceladdins.asp
: : : : :
: : : : :
: : : :
: : : :
: : :
: : :
: :
: :
:
:
http://msdn.microsoft.com/library/en...tingexceladdin.
: : : : asp
: : : : :
: : : : : http://www.j-walk.com/ss/excel/tips/tip45.htm
: : : : : Excel 97: Creating Add-Ins
: : : : :
: : : : : You addin should delete any existing version of the
toolbar
: and
: : : create
: : : a
: : : : new
: : : : : version when it is loaded. It should also mark it as
: temporary
: : and
: : : : delete
: : : : it
: : : : : in the beforeclose event. The creation can be done in
the
: : : : workbook_open
: : : : : event of the addin.
: : : : :
: : : : : If the user will only load the addin when they want to use
: it,
: : then
: : : this
: : : : : should be sufficient. If they will load the addin all the
: time,
: : : then
: : : it
: : : : : depends on the user. In tools=Customize, they can select
: : whether
: : : the
: : : : bar
: : : : : is visible or not. If that is too hard, then perhaps you
: could
: : : provide
: : : : a
: : : : : function in your addin that they can run from
: : Tools=Macro=Macros
: : : or
: : : : you
: : : : : can add an additional menu choice in the tools menu.
: : : : :
: : : : : --
: : : : : Regards,
: : : : : Tom Ogilvy
: : : : :
: : : : :
: : : : :
: : : : : "HSalim[MVP]" wrote in message
: : : : : ...
: : : : : How do I create an add-in and distribute it to others?
: : : : : I have a toolbar associated with the add-in. The user
: wants
: : the
: : : : toolbar
: : : : : to
: : : : : be invisible most of the time. When she wants to use
the
: : add-in -
: : : : : approximately once a month, she will make the toolbar
: visible.
: : : : : What is the best way to achieve this?
: : : : : Thanks
: : : : : Habib
: : : : :
: : : : :
: : : : :
: : : : :
: : : :
: : : :
: : : :
: : : :
: : :
: : :
: : :
: : :
: : :
: :
: :
: :
: :
:
:
:
: