Prev: 2003 Excel Pivot table data selection
Next: How can I open 2 excel files in two excel windows -using multi
From: JB Bates on 24 Dec 2009 12:46
I have a workbook that contains 100 worksheets. On each work sheet I have
the following code (see below) that runs and when a particular person is
selected from a drop down list a jpg of their signature displays at the top
of each sheet.

This was working fine but I am now getting the following error after I
select a person from the drop down and then when ever i try to input data
into any other cell. The error message is:

Runtime Error '1004'" Unable to set the top property of the picture class

It then allows me to END or debug. if i select debug it opens the VBA
window to the code and highlights this line

oPic.Top = .Top

But i don't know what to to do with that line of code to debug

PLEASE HELP

thanks in advance - JB


Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("ad1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

From: Ken Johnson on 24 Dec 2009 17:43
On Dec 24, 11:46 am, JB Bates <JBBa...(a)discussions.microsoft.com>
wrote:
> I have a workbook that contains 100 worksheets.  On each work sheet I have
> the following code (see below) that runs and when a particular person is
> selected from a drop down list a jpg of their signature displays at the top
> of each sheet.  
>
> This was working fine but I am now getting the following error after I
> select a person from the drop down and then when ever i try to input data
> into any other cell.  The error message is:
>
> Runtime Error '1004'" Unable to set the top property of the picture class
>
> It then allows me to END or debug.  if i select debug it opens the VBA
> window to the code and highlights this line
>
> oPic.Top = .Top
>
> But i don't know what to to do with that line of code to debug
>
> PLEASE HELP
>
> thanks in advance - JB
>
>     Private Sub Worksheet_Calculate()
>         Dim oPic As Picture
>         Me.Pictures.Visible = False
>         With Range("ad1")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = .Top
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>     End Sub

Hi JB,

I've seen this problem a few time before today. Every thing works fine
until a certain number of pictures is reached then that error 1004
stops it working.
See if this solution works on your sheets, it has worked in the
past...

Private Sub Worksheet_Calculate()
Dim Pic As Shape
For Each Pic In Me.Shapes
If Pic.Type = msoPicture Then
Pic.Visible = False
End If
Next Pic
With Range("AD1")
For Each Pic In Me.Shapes
If Pic.Name = .Text Then
Pic.Visible = True
Pic.Top = .Top
Pic.Left = .Left
Exit For
End If
Next Pic
End With
End Sub

As you can see I have avoided dimensioning the pictures as pictures
and instead used the Shape object. It does require an extra loop so
that only shapes of the type "msoPicture" are hidden.

Ken Johnson
From: Ken Johnson on 24 Dec 2009 19:28
On Dec 24, 11:46 am, JB Bates <JBBa...(a)discussions.microsoft.com>
wrote:
> I have a workbook that contains 100 worksheets.  On each work sheet I have
> the following code (see below) that runs and when a particular person is
> selected from a drop down list a jpg of their signature displays at the top
> of each sheet.  
>
> This was working fine but I am now getting the following error after I
> select a person from the drop down and then when ever i try to input data
> into any other cell.  The error message is:
>
> Runtime Error '1004'" Unable to set the top property of the picture class
>
> It then allows me to END or debug.  if i select debug it opens the VBA
> window to the code and highlights this line
>
> oPic.Top = .Top
>
> But i don't know what to to do with that line of code to debug
>
> PLEASE HELP
>
> thanks in advance - JB
>
>     Private Sub Worksheet_Calculate()
>         Dim oPic As Picture
>         Me.Pictures.Visible = False
>         With Range("ad1")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = .Top
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>     End Sub

Strange!
I replied and it appeared. An hour later my reply has disappeared.
I'll try again.
I've seen these problem a few time in the past and the solution was to
avoid dimensioning with the Picture object and using the Shape object
instead.
It does introduce the need for a loop to avoid hiding all shapes
though.
Try...
Private Sub Worksheet_Calculate()
Dim Pic As Shape
For Each Pic In Me.Shapes
If Pic.Type = msoPicture Then
Pic.Visible = False
End If
Next Pic
With Range("AD1")
For Each Pic In Me.Shapes
If Pic.Name = .Text Then
Pic.Visible = True
Pic.Top = .Top
Pic.Left = .Left
Exit For
End If
Next Pic
End With
End Sub

Ken Johnson
From: Ken Johnson on 25 Dec 2009 18:03
On Dec 24, 11:46 am, JB Bates <JBBa...(a)discussions.microsoft.com>
wrote:
> I have a workbook that contains 100 worksheets.  On each work sheet I have
> the following code (see below) that runs and when a particular person is
> selected from a drop down list a jpg of their signature displays at the top
> of each sheet.  
>
> This was working fine but I am now getting the following error after I
> select a person from the drop down and then when ever i try to input data
> into any other cell.  The error message is:
>
> Runtime Error '1004'" Unable to set the top property of the picture class
>
> It then allows me to END or debug.  if i select debug it opens the VBA
> window to the code and highlights this line
>
> oPic.Top = .Top
>
> But i don't know what to to do with that line of code to debug
>
> PLEASE HELP
>
> thanks in advance - JB
>
>     Private Sub Worksheet_Calculate()
>         Dim oPic As Picture
>         Me.Pictures.Visible = False
>         With Range("ad1")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = .Top
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>     End Sub

Try not using...
Dim oPic As Picture
Use instead...
Dim oPic As Shape
However, an extra loop is required so that Shapes other than Pictures
are not hidden...
Private Sub Worksheet_Calculate()
Dim oPic As Shape
For Each oPic In Me.Shapes
If oPic.Type = msoPicture Then
oPic.Visible = False
End If
Next oPic
With Range("ad1")
For Each oPic In Me.Shapes
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

I have seen this problem before and this solution has usually worked.
I have no idea why use the Picture object should result in the error.

Ken Johnson
From: Ken Johnson on 28 Dec 2009 10:03
On Dec 24, 11:46 am, JB Bates <JBBa...(a)discussions.microsoft.com>
wrote:
> I have a workbook that contains 100 worksheets.  On each work sheet I have
> the following code (see below) that runs and when a particular person is
> selected from a drop down list a jpg of their signature displays at the top
> of each sheet.  
>
> This was working fine but I am now getting the following error after I
> select a person from the drop down and then when ever i try to input data
> into any other cell.  The error message is:
>
> Runtime Error '1004'" Unable to set the top property of the picture class
>
> It then allows me to END or debug.  if i select debug it opens the VBA
> window to the code and highlights this line
>
> oPic.Top = .Top
>
> But i don't know what to to do with that line of code to debug
>
> PLEASE HELP
>
> thanks in advance - JB
>
>     Private Sub Worksheet_Calculate()
>         Dim oPic As Picture
>         Me.Pictures.Visible = False
>         With Range("ad1")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = .Top
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>     End Sub

Private Sub Worksheet_Calculate()
Dim oPic As Shape
For Each oPic In Me.Shapes
If oPic.Type = msoPicture Then
oPic.Visible = False
End If
Next oPic
With Range("ad1")
For Each oPic In Me.Shapes
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

Ken Johnson
 |  Next  |  Last
Pages: 1 2
Prev: 2003 Excel Pivot table data selection
Next: How can I open 2 excel files in two excel windows -using multi