|
Option Explicit
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim LR As Long
Dim i As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
If LR < 2 And Cells(1, 1) = "" Then Exit Sub
Dim RngShp As Shape
For Each RngShp In Sheet1.Shapes
If RngShp.Name Like "*Picture*" Then
RngShp.Delete
End If
Next
Dim RangeWidth As Single
Dim RangeHeight As Single
Dim RangeDim As Single
For i = 1 To LR
Dim Result As Boolean
Result = Application.Run("GetQRCode", "QR Code", Cells(i, 1).Value, 200, 200)
If Result Then
On Error Resume Next
Cells(i, 2).Select
RangeWidth = Cells(i, 2).Width
RangeHeight = Cells(i, 2).Height
If RangeWidth < RangeHeight Then
RangeDim = RangeWidth
Else
RangeDim = RangeHeight
End If
Paste
With Selection
.ShapeRange.Width = RangeDim * 0.8
.ShapeRange.Height = RangeDim * 0.8
.ShapeRange.Left = Cells(i, 2).Left + (RangeWidth - .ShapeRange.Width) / 2
.ShapeRange.Top = Cells(i, 2).Top + (RangeHeight - .ShapeRange.Height) / 2
End With
End If
Next
Cells(1, 1).Select
Application.ScreenUpdating = True
End Sub
|
|