Prev: 2003 Excel Pivot table data selection
Next: How can I open 2 excel files in two excel windows -using multi
From: Ken Johnson on 29 Dec 2009 09:40
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 Dim oPic As Shape instead.
It means an extra loop to hide Shapes that are Pictures without hiding
any other Shapes...

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
First  |  Prev  | 
Pages: 1 2
Prev: 2003 Excel Pivot table data selection
Next: How can I open 2 excel files in two excel windows -using multi