for each userform in vbcomponents

Question

Hi I want to change the graphic in each image control on each form, but I've never programmed the VBcomponents before and I'm struggling...

I want something like:

Dim xForm As MSForms.UserForm

Dim xControl As MSForms.Control

For Each xForm In ThisWorkbook.VBProject.UserForms

For Each xControl In xForm.Controls

If xControl.Name = "imgTSC" Then

xControl.Picture -"C:\Users\Michael\LOGOS\TSC_small.jpg"

End If

Next xControl

Next xForm

But of course this doesn't work... Any ideas how can I achieve it, or is there an MVP page that explains it all for a simpleton like me?

Thanks

Answer 1

Hi Thanks, but my problem is not with LoadPicture, but with how to reference the userforms class to use with a 'For Next' loop

Answer 2

Remember to load all forms you need (Load UserForm1, Load UserForm2) and then try to use this code:

<CODE>

For Each xForm In UserForms

    For Each xControl In xForm.Controls

        If xControl.Name = "imgTSC" Then

            xControl.Picture = LoadPicture("C:\Users\Michael\LOGOS\TSC_small.jpg")

        End If

    Next xControl

Next xForm

</CODE>

You can read more about LoadPicture Function at http://msdn.microsoft.com/en-us/library/aa264946(v=vs.60).aspx website.

Answer 3

I had not been loading the userforms, so my 'UserForms' class was empty. However, in the meantime, I have found a way of doing it without loading them:

For archive purposes and to leave the string of this question complete, I will add the code tha - with your help and much reading - I have finally got to work below...

Dim xForm As Object, xControl As Object

Dim MyFileAndPath As Variant

MyFileAndPath = Application.GetOpenFilename("Select picture type file (*.bmp; *.gif; *.jpg; *.jpeg; *.wmf; *.emf)" _

& ",*.bmp;*.gif;*.jpg;*.jpeg;*.wmf;*.emf")

If MyFileAndPath = False Then End

For Each xForm In ThisWorkbook.VBProject.VBComponents

If LCase(Left(xForm.Name, 3)) = "frm" Then

For Each xControl In xForm.designer.Controls

If xControl.Name = "imgTSC" Then

xControl.Picture = LoadPicture(MyFileAndPath)

End If

Next xControl

End If

Next xForm

(P.S.all my forms are named with the prefix 'frm')

Thanks

Answer 4

You should not use For Each xForm In ThisWorkbook.VBProject.UserForms in VBA code. Instead you should use For Each xForm In UserForms.

If you have more than one userform, you should set ShowModal Property to false or load these forms and/or show them in modeless mode  (to get them all in UserForms collection), like this:

<CODE>

Load UserForm1

Load UserForm2

UserForm1.Show vbModeless

UserForm2.Show vbModeless

</CODE>

and then you can use a 'For Next' loop.

You can read more at http://support.microsoft.com/kb/829070 and http://support.microsoft.com/kb/207714.