Prev: DateDif?
Next: Working with Both Excel 2003 and 2007
From: jennifer on 5 Mar 2010 15:54
Hi Barb

I tired this, but the error kicks in, it is when i select the name of a person on worksheet three (as example) that has also been selected on worksheet one.

with the code you suggested, i still get the same error.

any thoughts?



Barb Reinhardt wrote:

Try thisOption ExplicitPrivate Sub Worksheet_Calculate()Dim oShape As ShapeDim
28-Dec-09

Try this

Option Explicit

Private Sub Worksheet_Calculate()
Dim oShape As Shape
Dim myRange As Excel.Range

Set myRange = Me.Range("ad1")

For Each oShape In Me.Shapes
oShape.Visible = msoFalse
Next oShape

For Each oShape In Me.Shapes
If oShape.Type = 13 And _
oShape.Name = myRange.Text Then
oShape.Visible = True
oShape.Top = myRange.Top
oShape.Left = myRange.Left
Exit For
End If
Next oShape
End Sub

--
HTH,

Barb Reinhardt



"JB Bates" wrote:

Previous Posts In This Thread:

On Monday, December 28, 2009 12:13 PM
JB Bates wrote:

debug runtime error 1004
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 do not 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

On Monday, December 28, 2009 7:43 PM
Barb Reinhardt wrote:

Try thisOption ExplicitPrivate Sub Worksheet_Calculate()Dim oShape As ShapeDim
Try this

Option Explicit

Private Sub Worksheet_Calculate()
Dim oShape As Shape
Dim myRange As Excel.Range

Set myRange = Me.Range("ad1")

For Each oShape In Me.Shapes
oShape.Visible = msoFalse
Next oShape

For Each oShape In Me.Shapes
If oShape.Type = 13 And _
oShape.Name = myRange.Text Then
oShape.Visible = True
oShape.Top = myRange.Top
oShape.Left = myRange.Left
Exit For
End If
Next oShape
End Sub

--
HTH,

Barb Reinhardt



"JB Bates" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Creating a WPF Custom Control
http://www.eggheadcafe.com/tutorials/aspnet/32d63678-2798-465e-ad29-c0b45fb68b78/creating-a-wpf-custom-con.aspx
 | 
Pages: 1
Prev: DateDif?
Next: Working with Both Excel 2003 and 2007