Run-time error 1004: Application-defined or object-defined error?

[Pecco75]

Hello,
the idea is to center an image in the middle of a cell where the cell's size is variable. This shall be done for a column of images if a certain cell in the same row contains content different from 0. If not the image shall be invisible.
Can you please help me to identify the problem here:

Sub Center()

Dim Position As Integer
Dim Picture As Integer
Picture = 6
For Position = 7 To 320​

If Sheets("List of Measures").Cells(Position, 2).Value = "" Then​

Sheets("List of Measures").Shapes(Picture).Visible = msoFalse​

Else​

Call CenterMe(Sheets("List of Measures").Shapes(Picture), Sheets("List of Measures").Range(Cells(Position, 9)))​

Picture = Picture + 1
End If​

Next Position​

End Sub


Private Sub CenterMe(Shp As shape, OverCells As Range)

With OverCells​

Shp.Left = .Left + ((.Width - Shp.Width) / 2)​

Shp.Top = .Top + ((.Height - Shp.Height) / 2)​

End With​

End Sub



Thank you =)

 

[patel45]

please upload a sample file on a share site then paste the link here

 

[Pecco75]

please upload a sample file on a share site then paste the link here

Here you go: https://hotfile.com/dl/202672210/d53c27b/SampleError1004.xlsm.html

I hope this helps. Please excuse me for not uploading the original file. It contains sensitive data.

 

[AndyPandy27]

Hi Pecco75,

I have to admit that I am brand new to VBA, so my suggestion is based on very limited knowledge. However, I recently had the same error message, and the answer to my issue was the same as the one I am about to offer - so you never know.

I think that you may need to alter your line:

If Sheets("List of Measures").Cells(Position, 2).Value = "" Then

To have doubled-up quotation marks - so it would be:

If Sheets("List of Measures").Cells(Position, 2).Value = [COLOR=#0000cd][B]""""[/B][/COLOR] Then

But like I say, not 100% sure - but might be worth a try!

Good luck,

Andy

 

[Pecco75]

Hi Pecco75,

I have to admit that I am brand new to VBA, so my suggestion is based on very limited knowledge. However, I recently had the same error message, and the answer to my issue was the same as the one I am about to offer - so you never know.

I think that you may need to alter your line:

If Sheets("List of Measures").Cells(Position, 2).Value = "" Then

To have doubled-up quotation marks - so it would be:

If Sheets("List of Measures").Cells(Position, 2).Value = [COLOR=#0000cd][B]""""[/B][/COLOR] Then

But like I say, not 100% sure - but might be worth a try!

Good luck,

Andy

Thank you for your reply, Andy. Unfortunately I still receive the same error message =/

 

[patel45]

Sub Center()
Dim Position As Integer
Dim Picture As Integer
Picture = 2
For Position = 7 To 320
    If Sheets("List of Measures").Cells(Position, 2).Value = "" Then
        Sheets("List of Measures").Shapes(Picture).Visible = msoFalse
    Else
        Call CenterMe(Sheets("List of Measures").Shapes("Picture " & Picture), Sheets("List of Measures").Cells(Position, 9))
        Picture = Picture + 1 ' there is only picture 2
    End If
Next Position

End Sub
 
Private Sub CenterMe(Shp As Shape, OverCells As Range)
     
    With OverCells
        Shp.Left = .Left + ((.Width - Shp.Width) / 2)
        Shp.Top = .Top + ((.Height - Shp.Height) / 2)
    End With
End Sub

 

[Pecco75]

Sub Center()
Dim Position As Integer
Dim Picture As Integer
Picture = 2
For Position = 7 To 320
    If Sheets("List of Measures").Cells(Position, 2).Value = "" Then
        Sheets("List of Measures").Shapes(Picture).Visible = msoFalse
    Else
        Call CenterMe(Sheets("List of Measures").Shapes("Picture " & Picture), Sheets("List of Measures").Cells(Position, 9))
        Picture = Picture + 1 ' there is only picture 2
    End If
Next Position

End Sub
 
Private Sub CenterMe(Shp As Shape, OverCells As Range)
     
    With OverCells
        Shp.Left = .Left + ((.Width - Shp.Width) / 2)
        Shp.Top = .Top + ((.Height - Shp.Height) / 2)
    End With
End Sub

Thank you for your time and help. Unfortunately now the error "Invalid call procedure or argument" occures =/

 

[patel45]

Need a good file, in the attached ther'is only one picture