shapes

Fluppe · Nov 26th 2004

Hi all,

I use the following macro to create a series of commandbuttons:

Code

Option Explicit
Dim strButtonname As String
Dim strButtoncaption As String
Dim intI As Integer
Dim intXpositie As Integer


Public Sub click_cmd1()


Call check_groupname




End Sub


Public Sub check_groupname()




intXpositie = 65 'buttons onder elkaar plaatsen


Workbooks("overzicht.xls").Worksheets("Sheet1").Activate


For intI = 1 To 4


Application.ScreenUpdating = False


'plaatsen avn nieuwe button
ActiveSheet.Buttons.Add(30, intXpositie, 100, 30).Select
'macro aanhangen
Selection.OnAction = "Macro3"
'naam geven aan button


strButtonname = Workbooks("meetpunten.xls").Worksheets("Sheet1").Range("A1").Offset(intI, 0).Value


On Error Resume Next


ActiveSheet.Shapes(strButtonname).Select
Selection.Characters.Text = Workbooks("meetpunten.xls").Worksheets("Sheet1").Range("A1").Offset(intI, 1).Value


strButtoncaption = Selection.Characters.Text


intXpositie = intXpositie + 40


Next intI


End Sub

Display More

I want to have a button name (strButtonname) the same as the Selection.Characters.text. But when I select the new button on my sheet excel named the new buttons as “button 2” “button 3”…
What did I do wrong?

Thanks,
Fluppe

tinyjack · Nov 26th 2004

Re: shapes

The code at the end of your macro is not running, due to the On Error Resume Next.

If you remove that line, then ActiveSheet.Shapes(strButtonname).Select will error.

What you have not done is set the Name property of the button. I would do this by creating an Object variable and setting this to your new button. You can then refer to the button using the variable and set the relevant properties. The following is a small example that should get you started.

Code

Sub Example()


Dim objButton As Object


Set objButton = ActiveSheet.Buttons.Add(100, 100, 100, 100)
objButton.Caption = "Hello"
objButton.Name = "HiButton"
objButton.OnAction = "Macro3"


End Sub

Display More

HTH

TJ

ps I would only start to put error handling in when you are happy that the code is working as expected.

**Andy Pope** · Nov 26th 2004

Re: shapes

Hi Fluppe,

You where not assigning the button name to the shape.

Code

Option Explicit
 
Public Sub click_cmd1()
    Call check_groupname
End Sub


Public Sub check_groupname()
    Dim strButtonname As String
    Dim strButtoncaption As String
    Dim intI As Integer
    Dim intXpositie As Integer
     
    intXpositie = 65 'buttons onder elkaar plaatsen
     
'    Workbooks("overzicht.xls").Worksheets("Sheet1").Activate
    Worksheets("Sheet1").Activate
     
    For intI = 1 To 4
         
        Application.ScreenUpdating = False
         
        With Workbooks("meetpunten.xls").Worksheets("Sheet1")
            strButtonname = .Range("A1").Offset(intI, 0).Value
            strButtoncaption = .Range("A1").Offset(intI, 1).Value
        End With
         
         'plaatsen avn nieuwe button
        ActiveSheet.Buttons.Add(30, intXpositie, 100, 30).Select
         'macro aanhangen
        With Selection
            .OnAction = "Macro3"
            .Name = strButtonname
            .Caption = strButtoncaption
        End With
         
        intXpositie = intXpositie + 40
         
    Next intI
     
End Sub

Display More

Fluppe · Nov 26th 2004

Re: shapes

Oké thank you! It's working grate.

shapes

Participate now!

Share