[SOLVED] Add standard button on custum commandbar

[Jos Vens]

Hi,

I created a commandbar for my application. Adding a custom button works with
this procedure, but I want ot add a standard button of excel. How can I do
this?

Thanks a lot
Jos Vens

CB_LibraAdd "Maak/Bewerk Opmerking", "TB_CommentCreate", 1589, True

In this line, I want to have the original excel button for editing comments
in stead of my procedure TB_CommentCreate. FaceID is correct.

Function CB_LibraAdd(vTooltipText As String, vOnAction As String, vFaceId As
Long, vGroup As Boolean)

Dim cbLibra As CommandBar
Dim vControl As CommandBarControl

On Error Resume Next

Set cbLibra = CommandBars("Libra")

Set vControl = cbLibra.Controls.Add

vControl.TooltipText = vTooltipText
vControl.OnAction = vOnAction
vControl.FaceId = vFaceId
vControl.Style = msoButtonIcon
vControl.BeginGroup = vGroup

End Function

[Leith Ross]

Hello Jos,

Here is a code example taken from the VBA help files. Thiss adds a button to a custom toolbar to execute the Excel toolbar AutoSum.

This Microsoft Excel example creates a command bar and then adds a built-in command bar button control to it. The button executes the Excel AutoSum function. This example uses the Execute method to total the selected range of cells when the command bar appears.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Bob Phillips]

Jos,

You set the ID to the BuiltinId for that control

Set cbCtl = CB.Controls.Add(Type:=msoControlButton, _
ID:=BuiltinId, _
Temporary:=True)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jos Vens" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> I created a commandbar for my application. Adding a custom button works
with
> this procedure, but I want ot add a standard button of excel. How can I do
> this?
>
> Thanks a lot
> Jos Vens
>
> CB_LibraAdd "Maak/Bewerk Opmerking", "TB_CommentCreate", 1589, True
>
> In this line, I want to have the original excel button for editing
comments
> in stead of my procedure TB_CommentCreate. FaceID is correct.
>
> Function CB_LibraAdd(vTooltipText As String, vOnAction As String, vFaceId
As
> Long, vGroup As Boolean)
>
> Dim cbLibra As CommandBar
> Dim vControl As CommandBarControl
>
> On Error Resume Next
>
> Set cbLibra = CommandBars("Libra")
>
> Set vControl = cbLibra.Controls.Add
>
> vControl.TooltipText = vTooltipText
> vControl.OnAction = vOnAction
> vControl.FaceId = vFaceId
> vControl.Style = msoButtonIcon
> vControl.BeginGroup = vGroup
>
> End Function
>
>

[Jos Vens]

Thanks Bob,

it works, but not on my protected sheet (the cell is editable, but the
commandbutton stays grey). I cannot unprotect the sheet because users will
delete my formula's. It's the button to edit/create comment on a cell.

Can you still help?
Thanks
Jos Vens


"Bob Phillips" <[email protected]> schreef in bericht
news:%[email protected]...
> Jos,
>
> You set the ID to the BuiltinId for that control
>
> Set cbCtl = CB.Controls.Add(Type:=msoControlButton, _
> ID:=BuiltinId, _
> Temporary:=True)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Jos Vens" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> I created a commandbar for my application. Adding a custom button works
> with
>> this procedure, but I want ot add a standard button of excel. How can I
>> do
>> this?
>>
>> Thanks a lot
>> Jos Vens
>>
>> CB_LibraAdd "Maak/Bewerk Opmerking", "TB_CommentCreate", 1589, True
>>
>> In this line, I want to have the original excel button for editing
> comments
>> in stead of my procedure TB_CommentCreate. FaceID is correct.
>>
>> Function CB_LibraAdd(vTooltipText As String, vOnAction As String, vFaceId
> As
>> Long, vGroup As Boolean)
>>
>> Dim cbLibra As CommandBar
>> Dim vControl As CommandBarControl
>>
>> On Error Resume Next
>>
>> Set cbLibra = CommandBars("Libra")
>>
>> Set vControl = cbLibra.Controls.Add
>>
>> vControl.TooltipText = vTooltipText
>> vControl.OnAction = vOnAction
>> vControl.FaceId = vFaceId
>> vControl.Style = msoButtonIcon
>> vControl.BeginGroup = vGroup
>>
>> End Function
>>
>>
>
>

[Bob Phillips]

Jos,

I think that, rather than call the builtin control directly, you would have
to have a button that unprotects the sheet, insert the comment via code,
then protect the sheet.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jos Vens" <[email protected]> wrote in message
news:OldF%[email protected]...
> Thanks Bob,
>
> it works, but not on my protected sheet (the cell is editable, but the
> commandbutton stays grey). I cannot unprotect the sheet because users will
> delete my formula's. It's the button to edit/create comment on a cell.
>
> Can you still help?
> Thanks
> Jos Vens
>
>
> "Bob Phillips" <[email protected]> schreef in bericht
> news:%[email protected]...
> > Jos,
> >
> > You set the ID to the BuiltinId for that control
> >
> > Set cbCtl = CB.Controls.Add(Type:=msoControlButton, _
> > ID:=BuiltinId, _
> > Temporary:=True)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Jos Vens" <[email protected]> wrote in message
> > news:%[email protected]...
> >> Hi,
> >>
> >> I created a commandbar for my application. Adding a custom button works
> > with
> >> this procedure, but I want ot add a standard button of excel. How can I
> >> do
> >> this?
> >>
> >> Thanks a lot
> >> Jos Vens
> >>
> >> CB_LibraAdd "Maak/Bewerk Opmerking", "TB_CommentCreate", 1589, True
> >>
> >> In this line, I want to have the original excel button for editing
> > comments
> >> in stead of my procedure TB_CommentCreate. FaceID is correct.
> >>
> >> Function CB_LibraAdd(vTooltipText As String, vOnAction As String,
vFaceId
> > As
> >> Long, vGroup As Boolean)
> >>
> >> Dim cbLibra As CommandBar
> >> Dim vControl As CommandBarControl
> >>
> >> On Error Resume Next
> >>
> >> Set cbLibra = CommandBars("Libra")
> >>
> >> Set vControl = cbLibra.Controls.Add
> >>
> >> vControl.TooltipText = vTooltipText
> >> vControl.OnAction = vOnAction
> >> vControl.FaceId = vFaceId
> >> vControl.Style = msoButtonIcon
> >> vControl.BeginGroup = vGroup
> >>
> >> End Function
> >>
> >>
> >
> >
>
>

[Jos Vens]

Thanks for your help, Leith,

Jos

"Leith Ross" <[email protected]>
schreef in bericht
news:[email protected]...
>
> Hello Jos,
>
> Here is a code example taken from the VBA help files. Thiss adds a
> button to a custom toolbar to execute the Excel toolbar AutoSum.
>
> This Microsoft Excel example creates a command bar and then adds a
> built-in command bar button control to it. The button executes the
> Excel AutoSum function. This example uses the Execute method to total
> the selected range of cells when the command bar appears.
>
>
> Code:
> --------------------
>
> Dim cbrCustBar As CommandBar
> Dim ctlAutoSum As CommandBarButton
>
> Set cbrCustBar = CommandBars.Add("Custom")
> Set ctlAutoSum = cbrCustBar.Controls _
> .Add(msoControlButton, CommandBars("Standard") _
> .Controls("AutoSum").Id)
>
> cbrCustBar.Visible = True
> ctlAutoSum.Execute
>
> --------------------
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile:
> http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=480655
>

[Jos Vens]

Thanks for your help Bob!

Jos

"Bob Phillips" <[email protected]> schreef in bericht
news:[email protected]...
> Jos,
>
> I think that, rather than call the builtin control directly, you would
> have
> to have a button that unprotects the sheet, insert the comment via code,
> then protect the sheet.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Jos Vens" <[email protected]> wrote in message
> news:OldF%[email protected]...
>> Thanks Bob,
>>
>> it works, but not on my protected sheet (the cell is editable, but the
>> commandbutton stays grey). I cannot unprotect the sheet because users
>> will
>> delete my formula's. It's the button to edit/create comment on a cell.
>>
>> Can you still help?
>> Thanks
>> Jos Vens
>>
>>
>> "Bob Phillips" <[email protected]> schreef in bericht
>> news:%[email protected]...
>> > Jos,
>> >
>> > You set the ID to the BuiltinId for that control
>> >
>> > Set cbCtl = CB.Controls.Add(Type:=msoControlButton, _
>> > ID:=BuiltinId, _
>> > Temporary:=True)
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Jos Vens" <[email protected]> wrote in message
>> > news:%[email protected]...
>> >> Hi,
>> >>
>> >> I created a commandbar for my application. Adding a custom button
>> >> works
>> > with
>> >> this procedure, but I want ot add a standard button of excel. How can
>> >> I
>> >> do
>> >> this?
>> >>
>> >> Thanks a lot
>> >> Jos Vens
>> >>
>> >> CB_LibraAdd "Maak/Bewerk Opmerking", "TB_CommentCreate", 1589, True
>> >>
>> >> In this line, I want to have the original excel button for editing
>> > comments
>> >> in stead of my procedure TB_CommentCreate. FaceID is correct.
>> >>
>> >> Function CB_LibraAdd(vTooltipText As String, vOnAction As String,
> vFaceId
>> > As
>> >> Long, vGroup As Boolean)
>> >>
>> >> Dim cbLibra As CommandBar
>> >> Dim vControl As CommandBarControl
>> >>
>> >> On Error Resume Next
>> >>
>> >> Set cbLibra = CommandBars("Libra")
>> >>
>> >> Set vControl = cbLibra.Controls.Add
>> >>
>> >> vControl.TooltipText = vTooltipText
>> >> vControl.OnAction = vOnAction
>> >> vControl.FaceId = vFaceId
>> >> vControl.Style = msoButtonIcon
>> >> vControl.BeginGroup = vGroup
>> >>
>> >> End Function
>> >>
>> >>
>> >
>> >
>>
>>
>
>