[VBA] Macro to create buttons with dynamic names (name +1)

[Trikson]

Hi guys,

I have a code that pastes some data in the next free row and creates formcontrol button in the cells next to this data:

Sub

Dim NextRow As RangeDim NextComment As Range
Dim i As Integer


Set NextRow = Sheets(Me.ProjectName_Box.Text).Cells(Rows.Count, 2).End(xlUp).Offset(6, 0)
Set NextComment = NextRow.Offset(0, 5)


Sheets("Template").Select
Range("B5:E11").Select
Selection.Copy
Sheets(Me.ProjectName_Box.Text).Select
NextRow.PasteSpecial Paste:=xlPasteAll




ActiveSheet.Buttons.Add(NextComment.Left, NextComment.Top, 81, 14.25).Select
Selection.Caption = "Comment"
For i = 1 To 50
Selection.Name = "Comment" & i
Next i
Selection.OnAction = "CommentButton_Click"

When run, macro creates the Comment button named Comment50. When run again, the data is pasted correctly below the last entry, button is also created in the correct place, but its named Comment50 again (instead of Comment49).

The naming is important because of the "CommentButton_Click" macro that hides/unhides rows:

Sub CommentButton_Click()


    Dim Btn As Button
    
    Set Btn = ActiveSheet.Buttons(Application.Caller)
    
    With Btn.TopLeftCell.Offset(1, 0).Resize(5).EntireRow
        .Hidden = Not .Hidden
    End With


End Sub

When both buttons are named Comment50 then clicking on either of them will hide rows that are below the topmost Comment50 button.

Appreciate any help/suggestions.
Thanks in advace!

 

[Norie]

Why do you have this code?

For i = 1 To 50
Selection.Name = "Comment" & i
Next i

Isn't that always going to name the newly created button 'Comment50'?

Couldn't you use code to find the name of the last button added and use that to name the next button?

strLastBtnName = ActiveSheet.Buttons(ActiveSheet.Buttons.Count).Name

strNewBtnName = "Comment" & (Replace(strLastBtnName, "Comment","") +1)

ActiveSheet.Buttons.Add(NextComment.Left, NextComment.Top, 81, 14.25).Select

Selection.Name = strNewBtnName

Selection.OnAction = "CommentButton_Click"

By the way, are you sure you need to have the buttons named sequentially?

When a new button is added it will be given a unique name, so it should be identified properly within the assigned macro.

 

[Trikson]

That's really dumb of me.

Of course you are right that if I force .onaction to the newly created button it will work with the auto-generated number. Now that you pointed this out I remembered that this fixation on Comment &i was from time that I wanted to copy buttons from template, not create new ones.

Thank you very much for your insight. Problem Solved.

All the best in 2015!

 

[AlphaFrog]

You could name them using the row number they are located in.

Selection.Name = "Comment" & NextRow.Row