1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel- Inserting pic

Discussion in 'Business Applications' started by LeoBR, Dec 1, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. LeoBR

    LeoBR Thread Starter

    Joined:
    Aug 6, 2009
    Messages:
    31
    Hello Folks,

    A few time ago you guys helped me with a macro to insert pic depending of the value that I write down at some cell. Everything was doing great, but now I need some help again. the problem is, I need to have the "before" and the "after" picture, but when I dont have any of them, the excel give me a Visual Basic error saying that I dont have any, error 1004. I need to stop that, how can I do? See attached please
     

    Attached Files:

  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Hello again. :)

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$J$5" Then Exit Sub
    For Each Shape In ActiveSheet.Shapes
    If Left(Shape.Name, 3) = "Pic" Then
    Shape.Delete
    End If
    Next Shape
    Res = Application.Match(Target, Sheets("Pics").Columns(1), 0)
    If IsError(Res) Then
    Application.EnableEvents = False
    Range("G7:I17").ClearContents
    MsgBox "Invalid Cód. PDV!"
    Application.EnableEvents = True
    Else
    BeforePic = Application.Index(Sheets("Pics").Columns(2), Res)
    AfterPic = Application.Index(Sheets("Pics").Columns(3), Res)
    If Len(BeforePic) + Len(AfterPic) = 0 Then

    MsgBox "No Before OR After pic is listed."
    Else
    Range("C7").Select
    ActiveSheet.Pictures.Insert("C:\test\" & BeforePic & ".JPG").Select
    Selection.ShapeRange.IncrementLeft 0
    Selection.ShapeRange.Height = 280#
    Selection.ShapeRange.Width = 260#
    Range("J7").Select
    ActiveSheet.Pictures.Insert("c:\test\" & AfterPic & ".JPG").Select
    Selection.ShapeRange.IncrementLeft 0
    Selection.ShapeRange.Height = 280#
    Selection.ShapeRange.Width = 260#
    Range("J5").Select
    Range("C11,P11") = ""
    End If

    End If
    End Sub
     
  3. LeoBR

    LeoBR Thread Starter

    Joined:
    Aug 6, 2009
    Messages:
    31
    That didnt work for me :(...still give me an "1004" error when I dont have both pics, how can I solve this??
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Worked for me (tested).

    In the sheet module, click anywhere in the Res = Application.Match(Target, Sheets("Pics").Columns(1), 0) line and press F9. That'll insert a break (you'll see a small brown circle in the left margin).

    Go back to "Resumo". Change J5. The code will fire, then pause. Press F8 to execute each line sequentially. Once you're past the BeforePic = and AfterPic = lines, hover over BeforePic and AfterPic in the code to check they're both "= Empty".

    Once you've run If Len(BeforePic) + Len(AfterPic) = 0 Then, the "hover-over" for Len(BeforePic) and Len(AfterPic) should be "= 0".

    Since 0 + 0 does = 0, the condition for the [Len(BeforePic) + Len(AfterPic)] "Then" is satisfied, so the procedure from there should be:

    Run MsgBox "No Before OR After pic is listed."
    Run End If
    Run ("primary") End If
    Run End Sub

    ETA: if still stuck, reupload -- you may have modified the code incorrectly.
     
  5. LeoBR

    LeoBR Thread Starter

    Joined:
    Aug 6, 2009
    Messages:
    31
    Thats my code....anything wrong?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$J$5" Then Exit Sub
    For Each Shape In ActiveSheet.Shapes
    If Left(Shape.Name, 3) = "Pic" Then
    Shape.Delete
    End If
    Next Shape
    Res = Application.Match(Target, Sheets("Pics").Columns(1), 0)
    If IsError(Res) Then
    Application.EnableEvents = False
    Range("G7:I17").ClearContents
    MsgBox "Invalid Cód. PDV!"
    Application.EnableEvents = True
    Else
    BeforePic = Application.Index(Sheets("Pics").Columns(7), Res)
    AfterPic = Application.Index(Sheets("Pics").Columns(8), Res)
    If Len(BeforePic) + Len(AfterPic) = 0 Then
    MsgBox "No Before OR After pic is listed."
    Else
    Range("C7").Select
    ActiveSheet.Pictures.Insert("P:\VENDAS\Trade MKT\Melhorias\Leandro\Fotos\PDVs\todos\antesdepois\" & BeforePic & ".JPG").Select
    Selection.ShapeRange.IncrementLeft 0
    Selection.ShapeRange.Height = 280#
    Selection.ShapeRange.Width = 260#
    Range("J7").Select
    ActiveSheet.Pictures.Insert("P:\VENDAS\Trade MKT\Melhorias\Leandro\Fotos\PDVs\todos\antesdepois\" & AfterPic & ".JPG").Select
    Selection.ShapeRange.IncrementLeft 0
    Selection.ShapeRange.Height = 280#
    Selection.ShapeRange.Width = 260#
    Range("J5").Select
    Range("C11,P11") = ""
    End If
    End If
    End Sub
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    That is the code as I modified it. The problem, as I understand it, is that it works for me but not for you.

    Did you try stepping through it as suggested?

    ETA: Hang on.

    You have:

    BeforePic = Application.Index(Sheets("Pics").Columns(7), Res)
    AfterPic = Application.Index(Sheets("Pics").Columns(8), Res)


    Why columns 7 & 8 when on "Pics" the Before IDs are in column (B =) 2 and the After IDs are in column (C =) 3?
     
  7. LeoBR

    LeoBR Thread Starter

    Joined:
    Aug 6, 2009
    Messages:
    31
    Ok....I´ve changed the columns,now are those. When I execute line by line, the error appears on those lines like attached. take a look please. This example is when I have the first pic, but not the second.
     

    Attached Files:

  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Ah. I was going by what you said to begin with:

    "I need to have the "before" and the "after" picture, but when I dont have any of them"

    Maybe this:

    If Len(BeforePic) <> 0 Then
    Range("C7").Select
    ActiveSheet.Pictures.Insert("P:\VENDAS\Trade MKT\Melhorias\Leandro\Fotos\PDVs\todos\antesdepois\" & BeforePic & ".JPG").Select
    Selection.ShapeRange.IncrementLeft 0
    Selection.ShapeRange.Height = 280#
    Selection.ShapeRange.Width = 260#
    End If


    and:

    If Len(AfterPic) <> 0 Then
    Range("J7").Select
    ActiveSheet.Pictures.Insert("P:\VENDAS\Trade MKT\Melhorias\Leandro\Fotos\PDVs\todos\antesdepois\" & AfterPic & ".JPG").Select
    Selection.ShapeRange.IncrementLeft 0
    Selection.ShapeRange.Height = 280#
    Selection.ShapeRange.Width = 260#
    End If
     
  9. LeoBR

    LeoBR Thread Starter

    Joined:
    Aug 6, 2009
    Messages:
    31
    Thanks bomb, but I not good as you :), im uploading a file as an example, can you take a look??

    thanks
     

    Attached Files:

    • 40.xls
      File size:
      48.5 KB
      Views:
      56
  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Change the formulas in G and H on "Pics" to:

    =IF($B2="ok",A2&"_"&"a","")

    for example, so that if not OK then blank (rather than space).

    And you need an additional End If line before the final one. :)
     
  11. LeoBR

    LeoBR Thread Starter

    Joined:
    Aug 6, 2009
    Messages:
    31
    I did that, but still give me an error :(...the same 1004....and says that the error happens at the line of the second pic ( im trying that on a situation that I just have the first pic).

    thanks
     
  12. LeoBR

    LeoBR Thread Starter

    Joined:
    Aug 6, 2009
    Messages:
    31
    Another thing.....when I dont have any of the pics, the msgbox appears, but the "1004" error appears when I just have 1 pic, before or after.
     
  13. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I tried the one "before/no after" situation (Cód. PDV = 7). I had to supress a few lines because I don't have your directories or pics, all the same I got no error.

    Maybe you've mangled a line or two along the way. Try the whole thing from scratch.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$J$5" Then Exit Sub
    For Each Shape In ActiveSheet.Shapes
    If Left(Shape.Name, 3) = "Pic" Then
    Shape.Delete
    End If
    Next Shape
    Res = Application.Match(Target, Sheets("Pics").Columns(1), 0)
    If IsError(Res) Then
    Application.EnableEvents = False
    Range("G7:I17").ClearContents
    MsgBox "Invalid Cód. PDV!"
    Application.EnableEvents = True

    Else
    BeforePic = Application.Index(Sheets("Pics").Columns(7), Res)
    AfterPic = Application.Index(Sheets("Pics").Columns(8), Res)
    If Len(BeforePic) + Len(AfterPic) = 0 Then
    MsgBox "No Before OR After pic is listed."
    Else

    If Len(BeforePic) <> 0 Then
    Range("C7").Select
    'ActiveSheet.Pictures.Insert("P:\VENDAS\Trade MKT\Melhorias\Leandro\Fotos\PDVs\todos\antesdepois\" & BeforePic & ".JPG").Select
    'Selection.ShapeRange.IncrementLeft 0
    'Selection.ShapeRange.Height = 280#
    'Selection.ShapeRange.Width = 260#
    End If

    If Len(AfterPic) <> 0 Then
    Range("J7").Select
    ActiveSheet.Pictures.Insert("P:\VENDAS\Trade MKT\Melhorias\Leandro\Fotos\PDVs\todos\antesdepois\" & AfterPic & ".JPG").Select
    Selection.ShapeRange.IncrementLeft 0
    Selection.ShapeRange.Height = 280#
    Selection.ShapeRange.Width = 260#
    End If

    End If
    End If
    End Sub
     
  14. LeoBR

    LeoBR Thread Starter

    Joined:
    Aug 6, 2009
    Messages:
    31
    nice :)...now that worked. The only thing now, is that after the pic loads, that comes selected, so if I press down that moves the pic. Before this new code that never happened. What can I do?
     
  15. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Change:

    End If
    End If
    End Sub


    to:

    End If
    End If
    Range("A1").Select
    End Sub


    (make it somewhere other than A1 if you prefer)
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/881936

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice