VBA to Create button [Activex or Form] in sheet1.

[pedie]

Hi, I need help with VBA to Create button [Activex or Form] in sheet1...and make the caption depending on the cell.
A1 text = button 1 caption etc. from A1 to A10.
And assign the macro names in the corresponding B column...

Thanks for helping in advance.

Regards
Pedie

 

[pedie]

Tried this way and errors..
"runtime error 438; object doesnt support this property or method...."

[FONT=Courier New]Sub try2()[/FONT]
[FONT=Courier New]Dim v As Range[/FONT]
[FONT=Courier New]Dim Mycaption As String[/FONT]
[FONT=Courier New]Dim MyAction As String[/FONT]
[FONT=Courier New]Dim myobj As Object[/FONT]
 
[FONT=Courier New]For Each v In Range("A1:A10")[/FONT]
[FONT=Courier New]If v.Value <> "" Then[/FONT]
[FONT=Courier New]Mycaption = v.Value[/FONT]
[FONT=Courier New]MyAction = v.Offset(0, 1).Value[/FONT]
[FONT=Courier New]       Set myobj = ActiveSheet.Buttons.Add(199.5, 20, 81, 36)[/FONT]
[FONT=Courier New]       myobj.Name = "New Button"[/FONT]
[FONT=Courier New]       myobj.OnAction = MyAction[/FONT]
[FONT=Courier New]       ActiveSheet.Shapes("New Button").Characters.Text = Mycaption[/FONT]
[FONT=Courier New]End If[/FONT]
[FONT=Courier New]ext v[/FONT]
[FONT=Courier New]End Sub[/FONT]

my table looks like this

A1 B1
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18 width=64>Button Try1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Macro_try1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>Button Try2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Macro_try2</TD></TR></TBODY></TABLE>

......


Please advice.

 

[pedie]

[/FONT]Sorry for bumping but can someone please do something? I did find so many instances however it does not completely suit what 'm wanting....
Thanks again

 

[meldoc]

Hello Pedie

Maybe.

http://www.mrexcel.com/archive/Controls/19553.html

 

[Norie]

Pedie

If you change the way you are trying to set the caption of the button your original code should work.

    myobj.Text = Mycaption

Mind you it's going to create multiple buttons on top of each other.

 

[pedie]

Thanks Brian, thanks Norie...yep! Now things are working ok
But all of the buttons r getting over the other...
Is there a way to arrange these buttons in some way?

Thanks again

 

[Norie]

Pedie

Try changing the Left and Top vaues for the buttons when you create them.

Perhaps you could use the values from the relevant cells.

 

[pedie]

Norie, if you could show me one example on how to achieve it
It will be great...Thanks again!

 

[pedie]

Hi, I tried this way....it is coming in serial not over the other but it is going down too below ...i want the button to start adding from row 1, is that possible....

I guess i'm taking up too much of your time here but just in case you have time...pls check it again...

[/FONT]
[FONT=Courier New]Option Explicit
Sub i()
Dim i As Long
Dim btn As Object
For i = 1 To 20
Set btn = ActiveSheet.Buttons.Add(100, 200 + 40 * i, 100, 25)
btn.OnAction = "Macro1"
btn.Characters.Text = "Run Proc " & i
Next i
End Sub
'Set objBtn = lWorkSheet.OLEObjects.Add"Forms.CommandButton.1", Left:=1,
'Top:=lWorkSheet.Cells(lStartingRow, 1).Top + 1, Height:=lWorkSheet.Cells(lStartingRow, 1).Height - 1, Width:=lbtnWidth - 1)[/FONT]
[FONT=Courier New]

 

[Norie]

Pedie

I've basically told you how to do it.

I've got something somewhere but I can never remember the order of Top, Left etc when adding buttons so didn't want to guess.

You've sort of got the right idea anyway.

If you just adjusted the numbers, eg 40, you should get it.

Anyway, you probably don't want that so here something I used earlier to create option buttons down a column:

Sub CreateFormOptionButtons()
Dim ws As Worksheet
Dim opt
Dim rng As Range
Dim cl As Range

    Set ws = Worksheets("Sheet1")
    
    Set rng = ws.Range("M2:M22")
    
    For Each cl In rng
 
        Set opt = ActiveSheet.OptionButtons.Add(cl.Left, cl.Top, cl.Width, cl.Height)

        If cl.Row = 2 Then

            opt.Name = "optAll"

            opt.Text = "All"

            opt.LinkedCell = "$M$2"

            opt.OnAction = "DoOptionStuff"

        Else
            opt.Name = "opt" & cl.Row - 2

            opt.Text = cl.Row - 2

            opt.LinkedCell = "$M$2"

            opt.OnAction = "DoOptionStuff"

        End If
 
    Next cl
 
End Sub

You can probably ignore most of it but you'll see the right order for the position and dimension - which I got wrong.