I am wanting to display a variable image in a user form dependant
alphebetically on a number selected in a text box (so if textbox1.value=1
then load picture apple.jpg, if 2 banana.jpg etc I cant just list all the
images as there are going to be somewhere in the region of 4000 total images
in the target folder.

any ideas


cyrrently have it set to just display a sigle image as below.
Dim picPicture As IPictureDisp
Set picPicture =
stdole.StdFunctions.LoadPicture("c:\windows\mydocu ments\newfolder\apple.jpg")

UserForm1.Image1.Picture = picPicture

UserForm1.Show

Hi "Atishoo"

1. Insert a new sheet and name 'PicIndex'
2. Enter the numbers in ColA and its corresponding image name in ColB
starting from row1. Name the range as 'PicList'
3. Use the below code

Dim picPicture As IPictureDisp
Dim strPath as String
Dim strFile As Variant
On Error Resume Next
strPath = "c:\windows\mydocuments\newfolder\"
strFile = Application.VLookup(CInt(TextBox1.Value), Range("piclist"), 2,
False)
Set picPicture = stdole.StdFunctions.LoadPicture(strPath & strFile & ".jpg")
UserForm1.Image1.Picture = picPicture
UserForm1.Show


4. If you dont name the range then refer the full range like
Sheets("PicIndex").Range("A1:B100") in the VLOOKUP formula.

Try and feedback

If this post helps click Yes
---------------
Jacob Skaria


"Atishoo" wrote:

I am wanting to display a variable image in a user form dependant
alphebetically on a number selected in a text box (so if textbox1.value=1
then load picture apple.jpg, if 2 banana.jpg etc I cant just list all the
images as there are going to be somewhere in the region of 4000 total images
in the target folder.

any ideas


cyrrently have it set to just display a sigle image as below.
Dim picPicture As IPictureDisp
Set picPicture =
stdole.StdFunctions.LoadPicture("c:\windows\mydocu ments\newfolder\apple.jpg")

UserForm1.Image1.Picture = picPicture

UserForm1.Show

Thanks
I could do that but with over 4000 images its gonna be impractical I just
need a way to reference the first item in a folder or the third or twenteeth
etc

"Jacob Skaria" wrote:

Hi "Atishoo"

1. Insert a new sheet and name 'PicIndex'
2. Enter the numbers in ColA and its corresponding image name in ColB
starting from row1. Name the range as 'PicList'
3. Use the below code

Dim picPicture As IPictureDisp
Dim strPath as String
Dim strFile As Variant
On Error Resume Next
strPath = "c:\windows\mydocuments\newfolder\"
strFile = Application.VLookup(CInt(TextBox1.Value), Range("piclist"), 2,
False)
Set picPicture = stdole.StdFunctions.LoadPicture(strPath & strFile & ".jpg")
UserForm1.Image1.Picture = picPicture
UserForm1.Show


4. If you dont name the range then refer the full range like
Sheets("PicIndex").Range("A1:B100") in the VLOOKUP formula.

Try and feedback

If this post helps click Yes
---------------
Jacob Skaria


"Atishoo" wrote:

I am wanting to display a variable image in a user form dependant
alphebetically on a number selected in a text box (so if textbox1.value=1
then load picture apple.jpg, if 2 banana.jpg etc I cant just list all the
images as there are going to be somewhere in the region of 4000 total images
in the target folder.

any ideas


cyrrently have it set to just display a sigle image as below.
Dim picPicture As IPictureDisp
Set picPicture =
stdole.StdFunctions.LoadPicture("c:\windows\mydocu ments\newfolder\apple.jpg")

UserForm1.Image1.Picture = picPicture

UserForm1.Show

I see some potential problems.
The user of the program might have the images in a different folder or drive.
Or they might not have any image files.
Are you going to provide the image files to the user and do a automatic install?
The user can also sort or move files, so an index (position) search could return the wrong file.
--
Jim Cone
Portland, Oregon USA




"Atishoo"
wrote in message
Thanks
I could do that but with over 4000 images its gonna be impractical I just
need a way to reference the first item in a folder or the third or twenteeth
etc

"Jacob Skaria" wrote:

Hi "Atishoo"

1. Insert a new sheet and name 'PicIndex'
2. Enter the numbers in ColA and its corresponding image name in ColB
starting from row1. Name the range as 'PicList'
3. Use the below code

Dim picPicture As IPictureDisp
Dim strPath as String
Dim strFile As Variant
On Error Resume Next
strPath = "c:\windows\mydocuments\newfolder\"
strFile = Application.VLookup(CInt(TextBox1.Value), Range("piclist"), 2,
False)
Set picPicture = stdole.StdFunctions.LoadPicture(strPath & strFile & ".jpg")
UserForm1.Image1.Picture = picPicture
UserForm1.Show


4. If you dont name the range then refer the full range like
Sheets("PicIndex").Range("A1:B100") in the VLOOKUP formula.

Try and feedback

If this post helps click Yes
---------------
Jacob Skaria


"Atishoo" wrote:

I am wanting to display a variable image in a user form dependant
alphebetically on a number selected in a text box (so if textbox1.value=1
then load picture apple.jpg, if 2 banana.jpg etc I cant just list all the
images as there are going to be somewhere in the region of 4000 total images
in the target folder.

any ideas


cyrrently have it set to just display a sigle image as below.
Dim picPicture As IPictureDisp
Set picPicture =
stdole.StdFunctions.LoadPicture("c:\windows\mydocu ments\newfolder\apple.jpg")

UserForm1.Image1.Picture = picPicture

UserForm1.Show

"Atishoo"

I didnt get you..

--You dont need to write any more code otherthan what is seen below.

--From your original post I understand each number corresponds to a item.
say 1 for apple, 2 for banana and so on..

--The minimum what should you do is refer this to a table to let the code
know which image relates to which number.

--What is impractical. To create the list of 4000 images? Go to command mode
and from DOS within the image directory use the below command to create the
list of image names. suppose you have images in C:\images\. Then open this
txt file in excel as fixed with and create the list of images.. Sort the list
and assign numbers..In the list if you are keeping the extension (.jpg) then
you can take it off from the below code...

c:\images\dir *.jpg 1.txt

If this post helps click Yes
---------------
Jacob Skaria


"Atishoo" wrote:

Thanks
I could do that but with over 4000 images its gonna be impractical I just
need a way to reference the first item in a folder or the third or twenteeth
etc

"Jacob Skaria" wrote:

Hi "Atishoo"

1. Insert a new sheet and name 'PicIndex'
2. Enter the numbers in ColA and its corresponding image name in ColB
starting from row1. Name the range as 'PicList'
3. Use the below code

Dim picPicture As IPictureDisp
Dim strPath as String
Dim strFile As Variant
On Error Resume Next
strPath = "c:\windows\mydocuments\newfolder\"
strFile = Application.VLookup(CInt(TextBox1.Value), Range("piclist"), 2,
False)
Set picPicture = stdole.StdFunctions.LoadPicture(strPath & strFile & ".jpg")
UserForm1.Image1.Picture = picPicture
UserForm1.Show


4. If you dont name the range then refer the full range like
Sheets("PicIndex").Range("A1:B100") in the VLOOKUP formula.

Try and feedback

If this post helps click Yes
---------------
Jacob Skaria


"Atishoo" wrote:

I am wanting to display a variable image in a user form dependant
alphebetically on a number selected in a text box (so if textbox1.value=1
then load picture apple.jpg, if 2 banana.jpg etc I cant just list all the
images as there are going to be somewhere in the region of 4000 total images
in the target folder.

any ideas


cyrrently have it set to just display a sigle image as below.
Dim picPicture As IPictureDisp
Set picPicture =
stdole.StdFunctions.LoadPicture("c:\windows\mydocu ments\newfolder\apple.jpg")

UserForm1.Image1.Picture = picPicture

UserForm1.Show

Fortunately in this instance the end user is my wife on her laptop!! (not
that her files ever stay still and thats not a euphamism (or maybe it is)) .
the difficulty with creating a list allocating numbers individually to each
image is that my wife may be adding images into the desired folder by
dragging and dropping in windows, and she wont have the know how to add to a
list of this nature, she may also be deleting images aswell.

"Jim Cone" wrote:

I see some potential problems.
The user of the program might have the images in a different folder or drive.
Or they might not have any image files.
Are you going to provide the image files to the user and do a automatic install?
The user can also sort or move files, so an index (position) search could return the wrong file.
--
Jim Cone
Portland, Oregon USA




"Atishoo"
wrote in message
Thanks
I could do that but with over 4000 images its gonna be impractical I just
need a way to reference the first item in a folder or the third or twenteeth
etc

"Jacob Skaria" wrote:

Hi "Atishoo"

1. Insert a new sheet and name 'PicIndex'
2. Enter the numbers in ColA and its corresponding image name in ColB
starting from row1. Name the range as 'PicList'
3. Use the below code

Dim picPicture As IPictureDisp
Dim strPath as String
Dim strFile As Variant
On Error Resume Next
strPath = "c:\windows\mydocuments\newfolder\"
strFile = Application.VLookup(CInt(TextBox1.Value), Range("piclist"), 2,
False)
Set picPicture = stdole.StdFunctions.LoadPicture(strPath & strFile & ".jpg")
UserForm1.Image1.Picture = picPicture
UserForm1.Show


4. If you dont name the range then refer the full range like
Sheets("PicIndex").Range("A1:B100") in the VLOOKUP formula.

Try and feedback

If this post helps click Yes
---------------
Jacob Skaria


"Atishoo" wrote:

I am wanting to display a variable image in a user form dependant
alphebetically on a number selected in a text box (so if textbox1.value=1
then load picture apple.jpg, if 2 banana.jpg etc I cant just list all the
images as there are going to be somewhere in the region of 4000 total images
in the target folder.

any ideas


cyrrently have it set to just display a sigle image as below.
Dim picPicture As IPictureDisp
Set picPicture =
stdole.StdFunctions.LoadPicture("c:\windows\mydocu ments\newfolder\apple.jpg")

UserForm1.Image1.Picture = picPicture

UserForm1.Show

Dear Jacob
I have seen your suggestion for displaying and inserting images to an excel
file. What I want is this: I have the names of the picture files in column
B.In the next column, I would like to use a macro or a function so that each
picture is inserted automatically in the corresponding cell. The pictures are
in a folder under drive c. Do you have any idea about it. I will appreciate
your prompt reply
"Jacob Skaria" wrote:

Hi "Atishoo"

1. Insert a new sheet and name 'PicIndex'
2. Enter the numbers in ColA and its corresponding image name in ColB
starting from row1. Name the range as 'PicList'
3. Use the below code

Dim picPicture As IPictureDisp
Dim strPath as String
Dim strFile As Variant
On Error Resume Next
strPath = "c:\windows\mydocuments\newfolder\"
strFile = Application.VLookup(CInt(TextBox1.Value), Range("piclist"), 2,
False)
Set picPicture = stdole.StdFunctions.LoadPicture(strPath & strFile & ".jpg")
UserForm1.Image1.Picture = picPicture
UserForm1.Show


4. If you dont name the range then refer the full range like
Sheets("PicIndex").Range("A1:B100") in the VLOOKUP formula.

Try and feedback

If this post helps click Yes
---------------
Jacob Skaria


"Atishoo" wrote:

I am wanting to display a variable image in a user form dependant
alphebetically on a number selected in a text box (so if textbox1.value=1
then load picture apple.jpg, if 2 banana.jpg etc I cant just list all the
images as there are going to be somewhere in the region of 4000 total images
in the target folder.

any ideas


cyrrently have it set to just display a sigle image as below.
Dim picPicture As IPictureDisp
Set picPicture =
stdole.StdFunctions.LoadPicture("c:\windows\mydocu ments\newfolder\apple.jpg")

UserForm1.Image1.Picture = picPicture

UserForm1.Show

Since the list can change, you will probably have to generate
the list each time the workbook is opened.
Listing 4000 files could take a few seconds.
But a simple status bar message would handle that.
The question becomes - why the form?
Why not just pick directly from the list?

There is plenty of code available to list files from a directory.
I can supply some (just ask) or you could implement Jacob's suggestion.

You may want to look at the free trial version of my "List Files" Excel add-in.
It lists files/folders with hyperlinks and provides an option to display a
small picture of most image files.
--
Jim Cone
Portland, Oregon USA



"Atishoo"
wrote in message
Fortunately in this instance the end user is my wife on her laptop!! (not
that her files ever stay still and thats not a euphamism (or maybe it is)) .
the difficulty with creating a list allocating numbers individually to each
image is that my wife may be adding images into the desired folder by
dragging and dropping in windows, and she wont have the know how to add to a
list of this nature, she may also be deleting images aswell.

"Jim Cone" wrote:
I see some potential problems.
The user of the program might have the images in a different folder or drive.
Or they might not have any image files.
Are you going to provide the image files to the user and do a automatic install?
The user can also sort or move files, so an index (position) search could return the wrong file.
--
Jim Cone
Portland, Oregon USA

Try this I put mine on a command button from the control toolbox.

Private Sub CommandButton1_Click()

For Each c In Worksheets("Sheet1").Range("B:B").Cells
If c.Value < "" Then

With c.Offset(0, 1)
Dim myPict As Picture
Set myPict = .Parent.Pictures.Insert("C:\FolderName\" & c.Value)
myPict.Top = .Top
myPict.Width = .Width
myPict.Height = .Height
myPict.Left = .Left
myPict.Placement = xlMoveAndSize
End With

End If
Next

End Sub


"E Manouchos" wrote:


Dear Jacob
I have seen your suggestion for displaying and inserting images to an excel
file. What I want is this: I have the names of the picture files in column
B.In the next column, I would like to use a macro or a function so that each
picture is inserted automatically in the corresponding cell. The pictures are
in a folder under drive c. Do you have any idea about it. I will appreciate
your prompt reply
"Jacob Skaria" wrote:

Hi "Atishoo"

1. Insert a new sheet and name 'PicIndex'
2. Enter the numbers in ColA and its corresponding image name in ColB
starting from row1. Name the range as 'PicList'
3. Use the below code

Dim picPicture As IPictureDisp
Dim strPath as String
Dim strFile As Variant
On Error Resume Next
strPath = "c:\windows\mydocuments\newfolder\"
strFile = Application.VLookup(CInt(TextBox1.Value), Range("piclist"), 2,
False)
Set picPicture = stdole.StdFunctions.LoadPicture(strPath & strFile & ".jpg")
UserForm1.Image1.Picture = picPicture
UserForm1.Show


4. If you dont name the range then refer the full range like
Sheets("PicIndex").Range("A1:B100") in the VLOOKUP formula.

Try and feedback

If this post helps click Yes
---------------
Jacob Skaria


"Atishoo" wrote:

I am wanting to display a variable image in a user form dependant
alphebetically on a number selected in a text box (so if textbox1.value=1
then load picture apple.jpg, if 2 banana.jpg etc I cant just list all the
images as there are going to be somewhere in the region of 4000 total images
in the target folder.

any ideas


cyrrently have it set to just display a sigle image as below.
Dim picPicture As IPictureDisp
Set picPicture =
stdole.StdFunctions.LoadPicture("c:\windows\mydocu ments\newfolder\apple.jpg")

UserForm1.Image1.Picture = picPicture

UserForm1.Show