Using Form Check Boxes in VBA code - Help Please

[djmike]

I am trying to create an application for use in an office environment, and so VBA in Excel is more practical than a stand-alone VB application. I think I need a VBA loop that will be used to make an image "autocolour" if a corressponding check box is ticked, or grayscale if it is not, by default the box will be unticked and the image grayscale. There will be about 25 images and checkboxes. I made a macro to colour the image when ticked
[vbcode
ActiveSheet.Shapes("Picture 9").Select
Selection.ShapeRange.PictureFormat.ColorType = msoPictureAutomatic
[/Highlight]
However, this only works for one image and I need a separate macro for every image, and another macro to uncolour it when unticked. Therefore, I thought about using a loop, but I ma unsure how to use the Excel Check boxes from the form menu in VBA code. They are easier to edit, but do I need the VBA checkboxes from the VBA toolbox?

Also, can some suggest some loop code please for this scenario:
When any checkbox is ticked, it should run the code and the loop will do this:
if checkbox# = true then picture#=autocolour else picture# = grayscale
starting with i=1, to i=24
e.g. so if checkbox 14 was ticked, picture 14 would become colour

I have not made all my images and checkboxes yet, so I an easily name them specially if someone recommends it or maybe I will need an array?

Thanks

[DKenny]

DJMike
I would suggest using ActiveX controls rather than native Excel Forms controls. The ActiveX checkbox ( and all other AX contiols live on the "Controls" toolbar) The reason for using these controls it that you can then use the _Click event of each control to change the ColorType of the associated picture.


Add the following procedure to a module in you workbook

Sub ChangePicture(ByVal PicName As String, ByVal OptOn As Boolean)
    
    With Worksheets(1).Shapes(PicName).PictureFormat
        If OptOn Then
            .ColorType = msoPictureGrayscale
        Else
            .ColorType = msoPictureAutomatic
        End If
    End With
End Sub

Now for each of your ActiveX checkboxes you should call the above procedure as follows

Private Sub CheckBox1_Click()
    ChangePicture "Picture 3", Me.CheckBox1.Value
End Sub

Changing both instances of "CheckBox1" to the correct name of the CB you want the event to be associated with. Also change "Picture 3" to the name of the associated Picture.

This will toggle the picture between automatic and grayscale ColorType, dependant of the value of the checkbox.