Excel Vba

Macro
1

Hi everyone.

 

A little bit out but I'll be back soon! =)

 

I have a little smile with an excel macro.

I know there are pros here ^^ (@Lucas) :p

It's not too much about the drawing itself, but it's still technique.

 

I'd like to type an article code in a column, so that in the column next to it there is an image associated with the text on the left.

 

I found a VBA macro that allows you to do this via a photo library that is in the same folder as the excel workbook.

 

But hey, the layout wasn't working, so I changed the code a bit, but I'm not getting by.

 

 

 

 

 

 

2 Likes
2

There he is:

 

Option Explicit


Sub Affiche_Image()
Dim Ws As Worksheet                   ' Used to manipulate the leaf object more easily
Dim Image As String                  ' Will contain the name of the image
Dim Lg As Long                        ' Number of the last row column B

  Set Ws = Sheets("Purchase Order")                                           ' Sheet Name

  Application.ScreenUpdating = False                                  ' Prohibits screen refresh
 
  Efface_Images
 
  With Ws
 
    For lg = 2 TB . Range("B65536"). End(xlUp). Row                     ' Traverses all column B
    
      Image = ThisWorkbook.Path & "\Catalog\" & . Cells(Lg, "A")        ' Directory to refresh
        
      On Error Resume Next                                            ' We get rid of errors
      With . Pictures.Insert(Image). ShapeRange                         ' We insert the image whose name is in column B 

2 Likes
3

        . LockAspectRatio = msoFalse                                   ' You can resize it as you want
        . Left = Ws.Cells(Lg, "B"). Left                                ' Left Position
        . Top = Ws.Cells(Lg, "B"). Top                                  ' Position High
        . width = ws.cells(lg, "b"). Width                              ' Width
        . height = ws.cells(lg, "b"). Height                            ' height
      End With
      If Err.Number > 0 Then                                          ' If an error (image not present)
        MsgBox . Cells(Lg, "A") & vbCr & "Non-existent image"           ' It is reported
      End If
    Next Lg
  End With
End Sub


Sub Efface_Images()
Dim Ws As Worksheet                   ' Used to manipulate the leaf object more easily
Dim Sh As Shape                       ' Used to manipulate shapes (images) already displayed

  Set Ws = Sheets("Purchase Order")                                           ' Sheet Name

  With Ws
 
    For each sh in . Shapes                                            ' Browse the entire shapes collection (images)
      If Not Intersect(. Columns(1), Sh.TopLeftCell) Is Nothing Then   ' if it is in column 1
        Sh.Delete                                                     ' We delete it
      End If
    Next Sh
  End With
 
End Sub

 

2 Likes
4

Sorry, it looks a bit like a "flood"...

 

So in summary, I have an "A" column where I put the article number (starting with line 2)

 

And in column "B" The image corresponding to the article number is displayed when I click on the button (there are 2 buttons with the macro, one to display the images one to delete)

2 Likes
5

Well actually.....

 

Post to the ... I found on my own like a great :p

 

I think it can be erased.

 

Have a good day to you!

3 Likes
6

Give us the procedure to follow via a mini tutorial if possible

it can be used by others

thank you @ + ;-)

1 Like