Build Buttons in Columns with click function

[sm9748]

This is my first post so bear with me if I have not provided enough information.

I have a list of Serial Numbers on Excel and their associated photographs of the item in a folder. I am trying to build a column of buttons next to the serial numbers which has the number on the button. The button when clicked should then open the photo from the external folder and display on the worksheet.

I can do this for each individual button and corresponding photo but I have over 200 numbers/photographs and am trying to automate the process.

I have managed to create a column of buttons using in the sheet 1 object page:

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(1).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

End With
End If
Next i

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


However I am struggling to get the buttons to do anything. Please could someone help? I am used to working in C/C++/Matlab etc and therefore the foibles of VBA I am not quite akin with yet. Should the above code or any other code be in a module or in the worksheet?

Thanks in advance

[sm9748]

Is there anyone out there that can help?