Assigning Macros to Macro created ActiveX Control Box

[sm9748]

I have created a column of ActiveX command buttons using a macro, this works well. I have then created a macro which imports pictures down the column to the right of the buttons. However, I want to allocate one of the buttons to each picture import so I do not have to see all photos at one time. The ActiveX buttons are currently dormant.

The code I am using for the picture import is:

Sub InsertPic()
Dim pic As String 'file path of pic
Dim myPicture As Picture 'embedded pic
Dim rng As Range 'range over which we will iterate
Dim cl As Range 'iterator

Set rng = Range("G2:G6")
For Each cl In rng
pic = cl.Offset(0, -1)
Set myPicture = ActiveSheet.Pictures.Insert(pic)
'
With myPicture
.ShapeRange.LockAspectRatio = msoFalse
.Width = cl.Width
.Height = cl.Height
.Top = Rows(cl.Row).Top
.Left = Columns(cl.Column).Left
End With
'
Next
End Sub

The button creation code is:

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 4
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

How would I go about changing the code above to get the pictures assigned to the buttons?

[AlphaFrog]

...the pictures assigned to the buttons?

Are the buttons suppose to toggle the pictures visible\hidden?

[sm9748]

Hello AlphaFrog,

I was hoping to have any of the buttons delete the imported photo and replace with its associated picture.

Thanks

[AlphaFrog]

Hello AlphaFrog,

I was hoping to have any of the buttons delete the imported photo and replace with its associated picture.

Thanks

I still don't understand.

Delete the image in column G (same row) and then what's an "associated picture"? Need specifics.

[sm9748]

Ah ok,
In column B I have a list of numbers, these are the labels used for the buttons.

Essentially when a button in Column C is pressed I want the corresponding picture to appear in the middle of the screen (currently appearing in column G, but will change)- a bit like a pop up. The storage location of the images is in column F.

When another of the buttons is pressed, I would like the first image to be deleted and replaced by the image related to the row of the button that has just been pressed. Something similar to that but obviously I need to automate the process as I have about 250 rows each with a button and picture.

Please Login or Register  to view this content.

[AlphaFrog]

This used Forms-type command buttons because one macro can be assigned to all the buttons created. The one macro can determine which button called it using the Application.Caller method.

Please Login or Register  to view this content.

[sm9748]

Thank you so much for your quick response, I will try this and let you know how it goes.

[sm9748]

I am just trying to add a MsgBox into the code to prevent error messages if the picture file isnt found.

At the moment if the picture isnt correct then I get Runtime Error 1004: Unable to get the Insert property of the Picture Classes. I would like a MsgBox to appear instead saying "no picture".

I have tried numerous if statements but with no avail any ideas?

[AlphaFrog]

I assume you got it working except for the error check?

Try something like this.

Please Login or Register  to view this content.

[sm9748]

Thank you very much AlphaFrog. All works beautifully.