Peter,
Thanks for the suggestions - you and Tom have been a great help.
I will incorporate your suggestions.
re. On error Resume Next:
I'll check for the existence of the toolbar in a subroutine
(I feel that on error resume next should only be used in a tight little
routine that specifically checks for the anticipated error). I guess I have
been burned by that statement in the past.
I like your suggestion that the user may have positioned the bar elsewhere
so I'll adopt that, but that brings up another problem:
When does the add-in close? when the user exits Excel, right? And it loads
at excel start up, right? I am not sure how much memory each add-in
consumes and it might be a good idea to just start the add-in when it is
needed that one time a month. Your thoughts on that would be most welcome.
Regards
Habib
"Peter T" <peter_t@discussions wrote in message
...
: Looks like you're there! To be ultra picky a couple more points -
:
: Set ws = Worksheets(1)
:
: I assume you are testing in an xls that becomes active when it opens,
unlike
: an xla. So you would need to qualify your Worksheet with the workbook.
: However if it's an xls, if user had moved the original Worksheets(1) it
will
: refer to wrong sheet. If an xls use sheet-name (though in the pasted code
: "ws" is not used).
:
: Set ws = ThisWorkbook.Worksheets("Sheet1")
:
: and later in the code
: For Each cel In ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
: can be changed to
: For Each cel In ws.Range("Buttons")
:
:
: Set cBar = Application.CommandBars("LockBox")
: should be preceded with "On error Resume Next" in case the bar does not
: exist.
:
: This line from my previous suggestion
: ' If cBar.Controls.Count < 2 Then
: was intended if the bar exists but without at least the number of expected
: controls (eg 2), then delete the bar and make a new one.
:
: I see you are deleting the bar if it exists and creating a new one each
: time. This is a normal thing to do particularly if the bar should have
been
: deleted on close. However if you are leaving it place on close, originally
: you said user wants to keep it, then don't delete the bar. User may have
: positioned it to preference.
:
: I'm not sure if you are always going to create a new bar or only if needs.
: If the latter might be an idea to move
: cBar.Visible = True
: to after the section starting "If cBar Is Nothing Then", ie ensure it's
: visible if you are not creating a new bar. However no harm to recreate the
: controls each time even if the bar exists, eg
:
: If cBar Is Nothing Then
: Set cBar = Application.CommandBars.Add(Name:="LockBox")
: End If
: ' create the controls and set bar properties
:
: If your icons look OK I wouldn't worry about the additional code for
Picture
: & Mask, what you have will work in all versions. I think how "sharp" they
: appear depends both on design and "type" of colours.
:
: Regards,
: Peter T
:
: PS When I posted the link to KeepITcools method I didn't see Tom's
slightly
: earlier post with same.
:
: "HSalim[MVP]" wrote in message
: ...
: Finally got it working. It is not quite waht I expected to see .
: I never did get the setIcon routine to work, not did the .Picture and
: .Mask
: methods work
:
: This is what I did.
: 1. added a new Picturename column to my template.
: 2. Pasted the button image as a picture in the worksheet
: 3. Asigned a name for each picture
:
: It appears that I can copy the image to memory and the paste the button
: image on to the new button using .paste so here is the code snippet.
:
: Regards
: Habib
:
:
:
: '---------------------
: Sub Auto_Open()
: Dim cBar As CommandBar
: Dim i As Long, cel As Range
: Dim ob As Office.CommandBarButton
: Dim ws As Worksheet
:
: 'On Error GoTo 0
: 'On Error Resume Next
: Set ws = Worksheets(1)
:
: Set cBar = Application.CommandBars("LockBox")
: 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:="LockBox")
: cBar.Visible = True
:
: For Each cel In
: ThisWorkbook.Worksheets("Sheet1").Range("Buttons")
: cBar.Controls.Add Type:=msoControlButton
: With cBar.Controls(cel.Value)
: .OnAction = cel.Offset(, 1).Value
: .Caption = cel.Offset(, 2).Value
: .TooltipText = cel.Offset(, 3).Value
: .Style = msoButtonIconAndCaption
: ws.Shapes(cel.Offset(, 4).Value).CopyPicture xlScreen,
: xlBitmap
: .PasteFace
: .BeginGroup = True
: End With
: Next
: End If
:
: cBar.Enabled = True
: cBar.Position = msoBarTop
: End Sub
:
: '--------------------------------------
:
: "HSalim[MVP]" wrote in message
: ...
: : Peter,
: : It was not the file size that concerened me.
: : I just did not like the idea of having those image files outside - now
I
: had
: : to be converned with handling errors if those files were deleted -
: troubles
: : with ensuring a consistent user experience etc...
: :
: : Looks like you have a bit of experience in this area, huh!
: : Thanks for the help.
: :
: : Regards
: : Habib
: :
: : :
:
:
:
: