Changing Pictures Automatically

[spock1971]

Guys

I am building a s/sheet to display data relating to multiple stores. I want the user to pick a store code and all the data to change, which I'm fixing with the offset function.

However, I want a picture of the chosen store to display. Is there any way I can get excel to display a picture that changes depending on the users choice of store code.

Cheers

D

 

[spock1971]

OK - using the hyperlink() function I can get a link to changing pictures.

Is there any way excel will automatically open links?

 

[HaHoBe]

Hi, spock1971,

maybe assign the following code to a button and have a list according to data/validation in cell B1 (if you use different names or do not include the path for the file the code has to be altered):

Option Explicit

Sub InsertPicture()
' Hans W. Herber, 203503
' expects the path and the name of the file in cell B1
' F:\010314\15AXD.jpg
   Dim pct As Picture
   Dim iLeft, iTop, iWidth, iHeight
   Dim sFile As String
   sFile = Range("B1").Value
   If Dir(sFile) = "" Then
      Beep
      MsgBox "file not found"
      Exit Sub
   End If
   With ActiveCell
      iLeft = .Left
      iTop = .Top
      iWidth = .Width
      iHeight = .Height
   End With
   Set pct = ActiveSheet.Pictures.Insert(Range("B1").Value)
   pct.Left = iLeft + iWidth / 2 - pct.Width / 2
   pct.Top = iTop + iHeight / 2 - pct.Height / 2
End Sub

Ciao,
Holger

 

[spock1971]

Will give it a try - cheers.

 

[spock1971]

Ok buddy - code works great if I run the macro.

I have 2 questions:

1) Is there any way of stipluating the size of the image because it fills the screen when it opens, even though the original image is tiny.

2) Can I get the code to run automatically whenever the user changes the dropdown data validated cell?

Cheers

 

[spock1971]

Ok buddy - code works great if I run the macro.

I have 2 questions:

1) Is there any way of stipluating the size of the image because it fills the screen when it opens, even though the original image is tiny.

2) Can I get the code to run automatically whenever the user changes the dropdown data validated cell?

Cheers

 

[spock1971]

Ok buddy - code works great if I run the macro.

I have 2 questions:

1) Is there any way of stipluating the size of the image because it fills the screen when it opens, even though the original image is tiny.

2) Can I get the code to run automatically whenever the user changes the dropdown data validated cell?

Cheers

 

[HaHoBe]

Hi, spock1971,

shall I answer three times as well? (only kidding)

Use the Worksheet_Change-Event of the sheet to trigger the macro. In there you have to restrict the area to just and only the cell of the dropdown. You can refer as Target to it in the code later on.

Regarding the size of the image: when I tested the jpg I used it wasn´t any bigger than refarding it with ACDSee. For the moment I can´t think of what might be wrong with it: the last two lines of code determine the size of the picture inside the cell (maybe choose the cell to be greater than the pictures to include within...).

Caio,
Holger

 

[spock1971]

How do I get to see the change event option? Right click on the name of the sheet only gives me view code.

Cheers

 

[HaHoBe]

Hi, spock1971,

take that option and you´re inside the VBE right where you want to be: inside the code window you will see two dropdowns. From the left one choose Worksheet. According to that selection only the responding events will be listed in the right one where you than can find Change (SelectionChange is put in as standard if you made your choice)...

Ciao,
Holger

 

[spock1971]

Ok -got it now cheers.

So what do I put in it. range (b3).change run macro?

Cheers

D

 

[HaHoBe]

Hi, spock1971,

an example may look like:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$3" Then
  MsgBox "no macro"
  Exit Sub
End If
MsgBox "put macro here"
End Sub

The above is only for showing the working of the code and could be shortened to

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$3" Then Exit Sub
MsgBox "put macro here"
End Sub

Ciao,
Holger

 

[spock1971]

Ok - so now I've got:


Sub InsertPicture()

Dim pct As Picture
Dim iLeft, iTop, iWidth, iHeight

With ActiveCell
iLeft = .Left
iTop = .Top
iWidth = .Width
iHeight = .Height
End With
Set pct = ActiveSheet.Pictures.Insert(Range("aa1").Value)
pct.Left = iLeft + iWidth / 2 - pct.Width / 2
pct.Top = iTop + iHeight / 2 - pct.Height / 2
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$j$1" Then Exit Sub
MsgBox "put macro here"

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

And nothing's happening. Where am I going wrong buddy?

Cheers

Darren

 

[HaHoBe]

Hi, spock1971,

tell the excel-event whatelse than showing a messagebox it should do (which should be call the macro in a regular module):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$j$1" Then Exit Sub
InsertPicture
End Sub

Ciao,
Holger

 

[spock1971]

Sorry for the delay in replying buddy.

Got this now:

Option Explicit
Private Sub InsertPicture()

Dim pct As Picture
Dim iLeft, iTop, iWidth, iHeight

With ActiveCell
iLeft = .Left
iTop = .Top
iWidth = .Width
iHeight = .Height
End With

Range("h7").Select
Set pct = ActiveSheet.Pictures.Insert(Range("ac1").Value)
pct.Width = 180
pct.Height = 150
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$m$1" Then Exit Sub
InsertPicture
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

.. and it doesn't work. Shold I be doing something like run sub InsertPicture?

Cheers

 

[spock1971]

Actually - if I take out the 'if target.address' bit just leaving InsertPicture, every time I click in a cell it puts 2 copies of the picture in the worksheet.

So somehow, it is exit sub even if the cell I'm changing is m1. Any ideas?