how to add a code to programatically created command button

[saurabh_546]

Dear all
i am adding a command button programmically to the worksheet.
i need to assign a simple code which will activate the main sheet in workbook..
can any body help me.
thanks and regards
Saurabh

 

[NateO]

Hello,

The most practical way to do this is to avoid ActiveX controls and use Shapes, which are created traditionally from the Forms toolbar.

This way, you don't have to add code to the Project, which requires Trust, and isn't set up by default as such on other people's computers. So, here's an example:

Sub foo()
Dim cl As Range
Dim shpBt As Button
Application.ScreenUpdating = False
For Each cl In ActiveSheet.Range("B1:B200")
    Set shpBt = ActiveSheet.Buttons.Add( _
        Left:=cl.Left, _
        Top:=cl.Top, _
        Width:=cl.Width, _
        Height:=cl.Height)
    With shpBt
        .Characters.Text = shpBt.Name
        .OnAction = ThisWorkbook.Name & "!bar"
    End With
Next
Application.ScreenUpdating = True
End Sub

Sub bar()
With ActiveSheet.Buttons(Application.Caller)
    MsgBox "Address: " & _
        .TopLeftCell.Address(0, 0)
End With
End Sub

foo() creates the buttons, each button calls bar().

 

[saurabh_546]

thanx for help nateo
i am able to create button now..
but my problem is i am creating sheets also programically.. so my question is where shoud i write micro for that button...
regards
Saurabh

 

[NateO]

You're welcome.

Place it in a normal module. (Insert->Module)

 

[saurabh_546]

thank u thnak u thnak u very much nathan...
i wanted to do this desparately..