How to create a button on each row with value having the same macro?

[RyukoSeiko]

Hello guys. I'm new in excel vba programming, so I dunno a lot of stuffs yet. But I'm hoping someone could help me on what I plan to do.

So Here it is: I want to create a button for each row with a value on a certain range.

For example:
if A1 has a value, it will create a button on D1
if A2 has a value, it will create a button on D2 but having the same macro as the button above it but processes the values on its own row.
and so on...

For clarity: I'm planning to make a time stamp button for each row with an ID.

When I'm looking for codes, I found this one:


Option Explicit

Sub createButtons()
Dim theButton As OLEObject
Dim rngRange As Range
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set rngRange = Sheets(2).Range("B2")

For i = 0 To 9
If rngRange.Offset(i, 0).Value <> "" Then
With rngRange.Offset(i, 1)
Set theButton = ActiveSheet.OLEObjects.Add _
(ClassType:="Forms.CommandButton.1", _
Left:=.Left, _
Top:=.Top, _
Height:=.Height, _
Width:=.Width)

theButton.Name = "cmd" & rngRange.Offset(i, 0).Value
theButton.Object.Caption = rngRange.Offset(i, 0).Value

'-- you may edit other properties such as word wrap, font etc..
End With
End If
Next i

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

It works great, however my problem on this one is every time I run the macro, it will create another button on the same cell, even though it already has a button. It kinda gets messy. Plus I dunno how to make an auto-created button have the same macro in it. And what if I deleted the ID, the button will still remain accessable, when it shouldn't.

I hope you get my explanation.

Thank you in advance!!

 

[jim may]

I'd seriously consider having ONLY 1 Button for the 1 Macro (Place or stretch the button to show in rows 1 and 2, and freeze the rows above row 3 - that way the button is always visible as you scroll down)

When you are ready to select the row to perform your task on simply - click, or select the row header of that row (the entire row) - then click the button

Your macro will perform only of the data in your selected row #... Just my 2 cents...