spprtpplcm
New Member
- Joined
- Dec 5, 2022
- Messages
- 5
- Office Version
- 2013
- Platform
- Windows
Hey,
I have this VBA code that works as a Vlookup with images. However, it only works when I manually enter data in the cell. When pasting values of a whole range of data (or even a single cell value), the Vba code doesn't trigger. Kindly help. I am not much experienced in VBA, and I got this code from stackoverflow, so it's really hard to figure out.
Excel version - Microsoft Office Professional Plus 2013
OS - Windows 11 Home
I have this VBA code that works as a Vlookup with images. However, it only works when I manually enter data in the cell. When pasting values of a whole range of data (or even a single cell value), the Vba code doesn't trigger. Kindly help. I am not much experienced in VBA, and I got this code from stackoverflow, so it's really hard to figure out.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsM As Worksheet, wsOf As Worksheet, sh As Shape, shp As Shape, strSKU As String
Dim sHeight As Double, sWidth As Double, rngProduct As Range, i As Long
On Error Resume Next
If Target.Value = "" Then Exit Sub
If Target.Column = 1 Then
If Target.Cells.Count > 1 Then MsgBox "This code works only for a single cell (in column A:A) modification)!": Exit Sub
Set wsM = Worksheets("Sheet1") 'use here your master sheet
Set wsOf = Me 'the active sheet (this one)
'find the product code introduced in the offer sheet:
Set rngProduct = wsM.Range("A:A").Find(What:=Target.Value, After:=wsM.Range("A1"), LookIn:=xlValues, LookAt:=xlWhole)
If rngProduct Is Nothing Then MsgBox "No product """ & Target.Value & """ found in the master sheet": Exit Sub
'Copy the fields brought until now using Vlookup:
Application.EnableEvents = False
For i = 2 To 21 'it copies the next three columns after B:B. If more columns necessary to be copied, increas from 4 to necessary
Target.Offset(, i).Value = rngProduct.Offset(, i).Value
Next i
Application.EnableEvents = True
'format C:C column as text (even aleready having numbers formatted as scientifique:
Me.UsedRange.Columns(3).EntireColumn.TextToColumns FieldInfo:=Array(1, 2)
For Each sh In wsM.Shapes 'iterate between master sheet shapes:
If TypeName(sh.OLEFormat.Object) = "Picture" And sh.Name = Target.Value Then 'if its name is the searched SKU and is a Picture
sh.Copy: 'copy the necessary shape
Application.Wait Now + TimeValue("00:00:01")
wsOf.Paste
Set shp = wsOf.Shapes(wsOf.Shapes.Count) 'set the last copied/created shape
sHeight = shp.Height: sWidth = shp.Width 'extract initial height and width
'determine which dimension should be diminished, to be sure that both of them are inside the cell:
If shp.Height < Target.Offset(, 1).Height And shp.Width < Target.Offset(, 1).Width Then
If shp.Height > shp.Width Then
shp.Height = Target.Offset(, 1).Height - 2
If shp.Width > Target.Offset(, 1).Width Then shp.Width = Target.Offset(, 1).Width
sWidth = shp.Width: sHeight = shp.Height
Else
shp.Width = Target.Offset(, 1).Width - 2
If shp.Height > Target.Offset(, 1).Height Then shp.Height = Target.Offset(, 1).Height
sWidth = shp.Width: sHeight = shp.Height
End If
ElseIf shp.Height < Target.Offset(, 1).Height And shp.Width > Target.Offset(, 1).Width Then
shp.Width = Target.Offset(, 1).Width - 2: sWidth = shp.Width: sHeight = shp.Height:: sWidth = shp.Width
ElseIf shp.Height > Target.Offset(, 1).Height And shp.Width > Target.Offset(, 1).Width Then
If shp.Height > shp.Width Then
shp.Height = Target.Offset(, 1).Height - 2
If shp.Width > Target.Offset(, 1).Width Then shp.Width = Target.Offset(, 1).Width
sWidth = shp.Width: sHeight = shp.Height
Else
shp.Width = Target.Offset(, 1).Width - 2:
If shp.Height > Target.Offset(, 1).Height Then shp.Height = Target.Offset(, 1).Height
sWidth = shp.Width: sHeight = shp.Height
End If
End If
'set the correct top and left, to be centered on cell:
shp.Top = Target.Offset(, 1).Top + (Target.Offset(, 1).Height - sHeight) / 2
shp.Left = Target.Offset(, 1).Left + Target.Offset(, 1) + (Target.Offset(, 1).Width - sWidth) / 2
Exit For
End If
Next sh
End If
End Sub
Excel version - Microsoft Office Professional Plus 2013
OS - Windows 11 Home