dynamically adding activeX button in a loop not working in Excel VBA

Question

I have a loop in which I am adding ActiveX buttons in a particular sheet. But no matter how big loop is it is adding only one button. The loop is on rows of the sheet. Each time it should come to new row and add a button. But first time its coming to a row, adds the button and then function gets terminated. Even msgbox written after the line that adds the dynamic button does not executes. Below is the code:

Public Function AddButton(strSheetName, counter)
Dim btn As OLEObject
Dim cLeft, cTop, cWidth, cHeight
    With Worksheets(strSheetName).Range("J" & (6 + counter))
        cLeft = .Left
        cTop = .Top
        cWidth = .Width
        cHeight = .Height
    End With
    With Worksheets(strSheetName)
        btn = .OLEObjects.Add(ClassType:="Forms.Label.1", Link:=True, DisplayAsIcon:=False, Left:=cLeft, Top:=cTop, Width:=cWidth, Height:=cHeight)
    End With
    MsgBox "After Adding button"
End Function

Now This function is getting called in a loop. In first iteration it comes to the function adds a button to desired location and immediately after adding the button this function and the calling function(and hence the loop) gets terminated. Even the msgbox(After Adding Button) does not executes.

What may be the reason? Any help? Thanks in advance Akki J

Siddharth Rout · Accepted Answer · 2012-05-16 10:15:28Z

1

You are missing the SET Command before the btn = code.

Also you don't need a Function for this :) Try this (TRIED AND TESTED)

Option Explicit

Sub Sample()
    Dim i As Long

    For i = 1 To 5
        AddButton "Sheet1", i
    Next i
End Sub

Public Sub AddButton(strSheetName As String, counter As Long)
    Dim btn As OLEObject
    Dim cLeft, cTop, cWidth, cHeight

    With Worksheets(strSheetName).Range("J" & (6 + counter))
        cLeft = .Left
        cTop = .Top
        cWidth = .Width
        cHeight = .Height
    End With
    With Worksheets(strSheetName)
        Set btn = .OLEObjects.Add(ClassType:="Forms.Label.1", Link:=True, _
        DisplayAsIcon:=False, Left:=cLeft, Top:=cTop, Width:=cWidth, _
        Height:=cHeight)
    End With

    MsgBox "After Adding button"
End Sub

answered May 16, 2012 at 10:15

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Akki J Over a year ago

It worked. You rock dude. I am new in Excel VBA. No idea at all of what is happening. Also what is this Option Explicit

Siddharth Rout Over a year ago

Option Explicit is the "Big Brother is watching you!" ;) See this link Point 2 (siddharthrout.wordpress.com/2011/08/01/to-err-is-human)

Collectives™ on Stack Overflow

dynamically adding activeX button in a loop not working in Excel VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related