Select Worksheet Code

[Jim]

I'm not a VBA code person, however I have been mostly succesfull with copying
and pasting code from everyone's help here, much appreciated. I'm having
trouble with the following code:

Option Explicit

Private Sub Worksheet_Calculate()
ActiveSheet.Unprotect Password:="Profit"

Dim oPic As Picture
Me.Pictures.Visible = False
With Range("C2")
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
ActiveSheet.Protect Password:="Profit"
End Sub

The code works great on the sheet (Loan Package ENG) I want it to. On the
VBA editor this code is shown on Sheet4 (Loan Package ENG). However when I
enter data on a seperate sheet, I get:

Run-time error '1004':

Unable to set the Top property of the Picture class

When I click the Debug option, it takes me to the code above with 'oPic.Top
= .Top' highlighted.

I would like some help fixing this with correct code, but also a bit of an
explanation of why the error is occuring to help me understand VBA better.

Thanks

 

[Jim]

Leith,

All sheets are password protected and use the same password. The code works
find when used on that sheet. It's when an event on another sheet happens
that I have a problem with this code. Somehow I want only this code to
'fire' when I'm in that sheet only.

 

[MichDenis]

Hi,

Try like this :

Private Sub Worksheet_Calculate()
Me.Unprotect Password:="Profit"

Dim oPic As Picture
Me.Pictures.Visible = False
With Range("C2")
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
Me.Protect Password:="Profit"
End Sub


"Jim" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
I'm not a VBA code person, however I have been mostly succesfull with copying
and pasting code from everyone's help here, much appreciated. I'm having
trouble with the following code:

Option Explicit

Private Sub Worksheet_Calculate()
ActiveSheet.Unprotect Password:="Profit"

Dim oPic As Picture
Me.Pictures.Visible = False
With Range("C2")
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
ActiveSheet.Protect Password:="Profit"
End Sub

The code works great on the sheet (Loan Package ENG) I want it to. On the
VBA editor this code is shown on Sheet4 (Loan Package ENG). However when I
enter data on a seperate sheet, I get:

Run-time error '1004':

Unable to set the Top property of the Picture class

When I click the Debug option, it takes me to the code above with 'oPic.Top
= .Top' highlighted.

I would like some help fixing this with correct code, but also a bit of an
explanation of why the error is occuring to help me understand VBA better.

Thanks

 

[Nigel]

I wonder if it is because you have the code on the specific sheet. Try
moving the code to the workbook event ( on ThisWorkbook) You can then refer
to the specific sheet triggering the code using the Sh object.
Notes:
If the sheet has no picture collection then you will get the error you have
seen, you could add a test to see there are pictures using
You do not fully qualify Range C2 if this should relate to the specific
sheet then change this as shown.



Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
' check if there are pictures on sheet
If Sh.Pictures = 0 then Exit Sub

Sh.Unprotect Password:="Profit"
Dim oPic As Picture
Sh.Pictures.Visible = False
With Sh.Range("C2")
For Each oPic In Sh.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
Sh.protect Password:="Profit"
End Sub

--

Regards,
Nigel
(e-mail address removed)

 

[Jim]

Leith,

That did the trick, thank you.

Can you please explain what the 'me' code means versus the ActiveSheet to
help me understand better. Thanks.

 

[MichDenis]

Me : represents the worksheet itself where the code is written.
ActiveSheet and the object "Me" do not necessarily represent the same sheet
so, it can happen you unprotect the activesheet but execute the code
located on a different sheet.



"Jim" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
Leith,

That did the trick, thank you.

Can you please explain what the 'me' code means versus the ActiveSheet to
help me understand better. Thanks.