If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 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 |
#2
|
|||
|
|||
Runtime Error 1004
On Dec 24, 11:46*am, JB Bates
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 |
#3
|
|||
|
|||
Runtime Error 1004
On Dec 24, 11:46*am, JB Bates
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 |
#4
|
|||
|
|||
Runtime Error 1004
On Dec 24, 11:46*am, JB Bates
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 |
#5
|
|||
|
|||
Runtime Error 1004
On Dec 24, 11:46*am, JB Bates
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 |
#6
|
|||
|
|||
Runtime Error 1004
On Dec 24, 11:46*am, JB Bates
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 |
Thread Tools | |
Display Modes | |
|
|