How to simplify Target.Address?

L

londonpeter

New Member
Joined
Apr 21, 2012
Messages
10
Hi everyone,

I am following this forum for long time and found many times solution.
Now is time for my question. I could not find answer anywhere.

I have got list of products in column DK and pictures names in column DL (where picture name is =DK&".jpg")
All the pictures are stored in P:\General\GADD\PICTURES\

Pictures are displaying in comment window.
Everything is working great, but I have got more than 400 lines of products.

How simplify my code?

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "DK2" Then
NewPic = "P:\General\GADD\PICTURES\" & Range("DL2").Value
Target.Comment.Shape.Fill.UserPicture NewPic
End If
If Target.Address = "DK3" Then
NewPic = "P:\General\GADD\PICTURES\" & Range("DL3").Value
Target.Comment.Shape.Fill.UserPicture NewPic
End If
If Target.Address = "DK4" Then
NewPic = "P:\General\GADD\PICTURES\" & Range("DL4").Value
Target.Comment.Shape.Fill.UserPicture NewPic
End If
If Target.Address = "DK5" Then
NewPic = "P:\General\GADD\PICTURES\" & Range("DL5").Value
Target.Comment.Shape.Fill.UserPicture NewPic
End If
If Target.Address = "DK6" Then
NewPic = "P:\General\GADD\PICTURES\" & Range("DL6").Value
Target.Comment.Shape.Fill.UserPicture NewPic
End If
If Target.Address = "DK7" Then
NewPic = "P:\General\GADD\PICTURES\" & Range("DL7").Value
Target.Comment.Shape.Fill.UserPicture NewPic
End If
If Target.Address = "DK8" Then
NewPic = "P:\General\GADD\PICTURES\" & Range("DL8").Value
Target.Comment.Shape.Fill.UserPicture NewPic
End If
If Target.Address = "DK9" Then
NewPic = "P:\General\GADD\PICTURES\" & Range("DL9").Value
Target.Comment.Shape.Fill.UserPicture NewPic
End If
If Target.Address = "DK10" Then
NewPic = "P:\General\GADD\PICTURES\" & Range("DL10").Value
Target.Comment.Shape.Fill.UserPicture NewPic
End If
If Target.Address = "DK11" Then
NewPic = "P:\General\GADD\PICTURES\" & Range("DL11").Value
Target.Comment.Shape.Fill.UserPicture NewPic
End If
If Target.Address = "DK12" Then
NewPic = "P:\General\GADD\PICTURES\" & Range("DL12").Value
Target.Comment.Shape.Fill.UserPicture NewPic
End If

End Sub



Thanks for any reply
 
Sort by date Sort by votes
Your code could be simplified to :

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = Range("DK1").Column And Target.Row >= 2 And Target.Row <= 12 Then
  NewPic = "P:\General\GADD\PICTURES\" & Cells(Target.Row, Range("DL1").Column).Value
  Target.Comment.Shape.Fill.UserPicture NewPic
End If
End Sub
 
Upvote 0
Perhaps this.
Code: 
If Intersect(Target, Range("DK2:DK12"))  Is Nothing Then Exit Sub
 
NewPic = "P:\General\GADD\PICTURES\" & Target.Offset(,1).Value 
 
Target.Comment.Shape.Fill.UserPicture NewPic
 
Upvote 0
This will shorten it, but you might want to check out the parameters on the row limitations. I set it from 2 thru 12 based on the posted code. You might want to make it more, less, different?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Row > 1 And Target.Row < 13 Then
MsgBox Target.Row
i = Target.Row
If Target = Range("DK" & i) Then
NewPic = "P:\General\GADD\PICTURES\" & Range("DL" & i).Value
End If
End If
End Sub
Code:
 
Upvote 0
Here is another example using the Select Case



<font face=Franklin Gothic Medium><SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> target.Address<br>    <SPAN style="color:#00007F">Case</SPAN> "DK2"<br>        <SPAN style="color:#007F00">'Do stuff for "DK2"</SPAN><br>    <SPAN style="color:#00007F">Case</SPAN> "DK3"<br>        <SPAN style="color:#007F00">'do stuff for "DK3"</SPAN><br>    <SPAN style="color:#007F00">'''continue example</SPAN><br>        <SPAN style="color:#007F00">'continue do stuff</SPAN><br>    <br>    <br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN></FONT>
 
Upvote 0
Guys, you are amazing.
I have lost already so much trying to solve this.

Got just one more question.

How to remove need of column DL?
Column DK hold articles numbers (12345678) and column DL just adding ".jpg" at the end of DK (12345678.JPG).

How to find picture name using only target cell?
So my code will be corresponding only to rows in column DK and returning picture as a comment to the same cell?
 
Upvote 0
Try this

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = Range("DK1").Column And Target.Row >= 2 And Target.Row <= 12 Then
  NewPic = "P:\General\GADD\PICTURES\" & Target.Value & ".JPG"
  Target.Comment.Shape.Fill.UserPicture NewPic
End If
End Sub
 
Upvote 0
Thanks Mungyo.
Beautiful code.

Let me tell you now what I am doing.
A map of articles on the shopfloor.

I would like to see articles pictures on the map.
Map is made from merged cells and range is A1:DG80.

Code made above is giving me the picture on the list of all articles.
Based on that list I can locate article on the map and paste article number in to merged cell.

How to expand range of the code that any merged cell with article number and comment on the map will show the picture?
 
Upvote 0
The code I posted should do exactly what the code in your first post did.

If you don't want to use DL for the name you can remove the Offset and concatenate ".JPG".
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewPic As String
    If Intersect(Target, Range("DK2:DK12")) Is Nothing Then Exit Sub
    NewPic = "P:\General\GADD\PICTURES\" & Target.Value & ".jpg"
    Target.Comment.Shape.Fill.UserPicture NewPic
End Sub
If you need to expand the range you want to 'monitor' you can change Range("DK2:DK12") as required.


Which range would the article no and comment be in?

Will the cell with the article no also be the cell with the comment?

PS Merged cells can be sometimes cause problems.:)
 
Upvote 0
One column and many rows is fine.
How to expand range to few more columns?

I would like see picture in any cell with comment in range A1:DG80.

For now we can forget DK and DL columns. These will be removed later after I will get pictures on my map A1:DG80.

I have drawed a map where each cell is one square meter of floor. Some of them been merged to show palete size shape. In that merged cell is one article number. Each merged cell have got comment waiting to display picture matching article number.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Copying values from formulas without deleting the formulas
Replies
6
Views
292
Fluff
Fluff
cmschmitz24
  • Question Question
Macro Help - Move/Delete based on cell criteria
2
Replies
13
Views
2K
DanteAmor
DanteAmor
M
  • Question Question
ListBox values not changing to specified range
Replies
0
Views
508
MarkMagic
M
agerrard
  • Question Question
Help with Vlookups
Replies
8
Views
691
agerrard
agerrard
A
  • Question Question
Preserving Values from Formulas 2
Replies
0
Views
1K
aabeck
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top