Programming code to new object

I want to create button with some code to worksheet. I can programm button
but i don't know how can i do action for this button :-(. Do you know please?

See other post.

--
HTH

Bob Phillips

"Martin" <Martin@discussions.microsoft.com> wrote in message
news:77CCA590-A227-41DD-9327-70C1D53DB807@microsoft.com...
> I want to create button with some code to worksheet. I can programm button
> but i don't know how can i do action for this button :-(. Do you know
please?

Hi Martin,

Try:

ActiveSheet.Buttons("Button 1").OnAction = "MyMacro"

---
Regards,
Norman



"Martin" <Martin@discussions.microsoft.com> wrote in message
news:77CCA590-A227-41DD-9327-70C1D53DB807@microsoft.com...
>I want to create button with some code to worksheet. I can programm button
> but i don't know how can i do action for this button :-(. Do you know
> please?

Thank you, but it doesn't work :-(. It show me warning: "Can't get button
property worksheet class". For illustration, there is program:

ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1", Link:=False,
DisplayAsIcon:=False, Left:=521.25, Top:=174, Width:=253.5, Height:=34.5
ActiveSheet.OLEObjects("CommandButton1").Object.Caption = "Návrat"
ActiveSheet.Buttons("CommandButton1").OnAction = "NavratZpet"

"Norman Jones" wrote:

> Hi Martin,
>
> Try:
>
> ActiveSheet.Buttons("Button 1").OnAction = "MyMacro"
>
> ---
> Regards,
> Norman
>
>
>
> "Martin" <Martin@discussions.microsoft.com> wrote in message
> news:77CCA590-A227-41DD-9327-70C1D53DB807@microsoft.com...
> >I want to create button with some code to worksheet. I can programm button
> > but i don't know how can i do action for this button :-(. Do you know
> > please?
>
>
>

Hi Martin,

See your other post.

---
Regards,
Norman



"Martin" <Martin@discussions.microsoft.com> wrote in message
news:FD34BF34-7F4F-4CA4-A8E7-A4DC53001419@microsoft.com...
> Thank you, but it doesn't work :-(. It show me warning: "Can't get button
> property worksheet class". For illustration, there is program:
>
> ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1",
> Link:=False,
> DisplayAsIcon:=False, Left:=521.25, Top:=174, Width:=253.5, Height:=34.5
> ActiveSheet.OLEObjects("CommandButton1").Object.Caption = "Návrat"
> ActiveSheet.Buttons("CommandButton1").OnAction = "NavratZpet"
>
> "Norman Jones" wrote:
>
>> Hi Martin,
>>
>> Try:
>>
>> ActiveSheet.Buttons("Button 1").OnAction = "MyMacro"
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Martin" <Martin@discussions.microsoft.com> wrote in message
>> news:77CCA590-A227-41DD-9327-70C1D53DB807@microsoft.com...
>> >I want to create button with some code to worksheet. I can programm
>> >button
>> > but i don't know how can i do action for this button :-(. Do you know
>> > please?
>>
>>
>>

Hi, thank you but it doesn't work, it shows me warning:"Can't get button
property of worksheet class". Do you know where i make mistake?

ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1", Link:=False,
DisplayAsIcon:=False, Left:=521.25, Top:=174, Width:=253.5, Height:=34.5
ActiveSheet.OLEObjects("CommandButton1").Object.Caption = "Návrat"
ActiveSheet.Buttons("CommandButton1").OnAction = "NavratZpet"

"Norman Jones" wrote:

> Hi Martin,
>
> Try:
>
> ActiveSheet.Buttons("Button 1").OnAction = "MyMacro"
>
> ---
> Regards,
> Norman
>
>
>
> "Martin" <Martin@discussions.microsoft.com> wrote in message
> news:77CCA590-A227-41DD-9327-70C1D53DB807@microsoft.com...
> >I want to create button with some code to worksheet. I can programm button
> > but i don't know how can i do action for this button :-(. Do you know
> > please?
>
>
>

Hi Martim.

Your problem is that you are not using a button from the Forms toolbar but a
CommandButton from the Controls Toolbox.

In the relevant worksheet module (right-click the worksheet tab | View
Code), paste the following:


Private Sub CommandButton1_Click()

'Your macro code here

End Sub


---
Regards,
Norman



"Martin" <Martin@discussions.microsoft.com> wrote in message
news:6DCA7984-76E3-4DF8-BA47-31A44922F4D3@microsoft.com...
> Hi, thank you but it doesn't work, it shows me warning:"Can't get button
> property of worksheet class". Do you know where i make mistake?
>
> ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1",
> Link:=False,
> DisplayAsIcon:=False, Left:=521.25, Top:=174, Width:=253.5, Height:=34.5
> ActiveSheet.OLEObjects("CommandButton1").Object.Caption = "Návrat"
> ActiveSheet.Buttons("CommandButton1").OnAction = "NavratZpet"
>
> "Norman Jones" wrote:
>
>> Hi Martin,
>>
>> Try:
>>
>> ActiveSheet.Buttons("Button 1").OnAction = "MyMacro"
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Martin" <Martin@discussions.microsoft.com> wrote in message
>> news:77CCA590-A227-41DD-9327-70C1D53DB807@microsoft.com...
>> >I want to create button with some code to worksheet. I can programm
>> >button
>> > but i don't know how can i do action for this button :-(. Do you know
>> > please?
>>
>>
>>

Thank you very much! Can i have last question? Can I change caption of button?
It is worst: ActiveSheet.Buttons("Button 1").Caption = "Back"
"Norman Jones" wrote:

> Hi Martim.
>
> Your problem is that you are not using a button from the Forms toolbar but a
> CommandButton from the Controls Toolbox.
>
> In the relevant worksheet module (right-click the worksheet tab | View
> Code), paste the following:
>
>
> Private Sub CommandButton1_Click()
>
> 'Your macro code here
>
> End Sub
>
>
> ---
> Regards,
> Norman
>
>
>
> "Martin" <Martin@discussions.microsoft.com> wrote in message
> news:6DCA7984-76E3-4DF8-BA47-31A44922F4D3@microsoft.com...
> > Hi, thank you but it doesn't work, it shows me warning:"Can't get button
> > property of worksheet class". Do you know where i make mistake?
> >
> > ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1",
> > Link:=False,
> > DisplayAsIcon:=False, Left:=521.25, Top:=174, Width:=253.5, Height:=34.5
> > ActiveSheet.OLEObjects("CommandButton1").Object.Caption = "Návrat"
> > ActiveSheet.Buttons("CommandButton1").OnAction = "NavratZpet"
> >
> > "Norman Jones" wrote:
> >
> >> Hi Martin,
> >>
> >> Try:
> >>
> >> ActiveSheet.Buttons("Button 1").OnAction = "MyMacro"
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "Martin" <Martin@discussions.microsoft.com> wrote in message
> >> news:77CCA590-A227-41DD-9327-70C1D53DB807@microsoft.com...
> >> >I want to create button with some code to worksheet. I can programm
> >> >button
> >> > but i don't know how can i do action for this button :-(. Do you know
> >> > please?
> >>
> >>
> >>
>
>
>

Hi Martin,

You have already provided the code to name (or rename) the button yourself:


ActiveSheet.OLEObjects("CommandButton1").Object.Caption _
=
"Návrat"

---
Regards,
Norman



"Martin" <Martin@discussions.microsoft.com> wrote in message
news:C526A451-BD37-40F5-857C-3E1187666B59@microsoft.com...
> Thank you very much! Can i have last question? Can I change caption of
> button?
> It is worst: ActiveSheet.Buttons("Button 1").Caption = "Back"
> "Norman Jones" wrote:
>
>> Hi Martim.
>>
>> Your problem is that you are not using a button from the Forms toolbar
>> but a
>> CommandButton from the Controls Toolbox.
>>
>> In the relevant worksheet module (right-click the worksheet tab | View
>> Code), paste the following:
>>
>>
>> Private Sub CommandButton1_Click()
>>
>> 'Your macro code here
>>
>> End Sub
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Martin" <Martin@discussions.microsoft.com> wrote in message
>> news:6DCA7984-76E3-4DF8-BA47-31A44922F4D3@microsoft.com...
>> > Hi, thank you but it doesn't work, it shows me warning:"Can't get
>> > button
>> > property of worksheet class". Do you know where i make mistake?
>> >
>> > ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1",
>> > Link:=False,
>> > DisplayAsIcon:=False, Left:=521.25, Top:=174, Width:=253.5,
>> > Height:=34.5
>> > ActiveSheet.OLEObjects("CommandButton1").Object.Caption = "Návrat"
>> > ActiveSheet.Buttons("CommandButton1").OnAction = "NavratZpet"
>> >
>> > "Norman Jones" wrote:
>> >
>> >> Hi Martin,
>> >>
>> >> Try:
>> >>
>> >> ActiveSheet.Buttons("Button 1").OnAction = "MyMacro"
>> >>
>> >> ---
>> >> Regards,
>> >> Norman
>> >>
>> >>
>> >>
>> >> "Martin" <Martin@discussions.microsoft.com> wrote in message
>> >> news:77CCA590-A227-41DD-9327-70C1D53DB807@microsoft.com...
>> >> >I want to create button with some code to worksheet. I can programm
>> >> >button
>> >> > but i don't know how can i do action for this button :-(. Do you
>> >> > know
>> >> > please?
>> >>
>> >>
>> >>
>>
>>
>>

If it is a forms button, just right-click it and assign a macro from the
menu options.

--
HTH

Bob Phillips

"Martin" <Martin@discussions.microsoft.com> wrote in message
news:6DCA7984-76E3-4DF8-BA47-31A44922F4D3@microsoft.com...
> Hi, thank you but it doesn't work, it shows me warning:"Can't get button
> property of worksheet class". Do you know where i make mistake?
>
> ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1",
Link:=False,
> DisplayAsIcon:=False, Left:=521.25, Top:=174, Width:=253.5, Height:=34.5
> ActiveSheet.OLEObjects("CommandButton1").Object.Caption = "Návrat"
> ActiveSheet.Buttons("CommandButton1").OnAction = "NavratZpet"
>
> "Norman Jones" wrote:
>
> > Hi Martin,
> >
> > Try:
> >
> > ActiveSheet.Buttons("Button 1").OnAction = "MyMacro"
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Martin" <Martin@discussions.microsoft.com> wrote in message
> > news:77CCA590-A227-41DD-9327-70C1D53DB807@microsoft.com...
> > >I want to create button with some code to worksheet. I can programm
button
> > > but i don't know how can i do action for this button :-(. Do you know
> > > please?
> >
> >
> >