Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Worksheet Code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Worksheet Code
Hello Jim, It looks like the sheet maybe password protected. If if is different from the original sheet "Profit", you will need to change it to match in your code. -- Leith Ross Sincerely, Leith Ross 'The Code Cage' (http://www.thecodecage.com/) ------------------------------------------------------------------------ Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120502 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Worksheet Code
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. "Leith Ross" wrote: Hello Jim, It looks like the sheet maybe password protected. If if is different from the original sheet "Profit", you will need to change it to match in your code. -- Leith Ross Sincerely, Leith Ross 'The Code Cage' (http://www.thecodecage.com/) ------------------------------------------------------------------------ Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120502 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Worksheet Code
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 "Jim" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Worksheet Code
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" wrote: 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" a écrit dans le message de groupe de discussion : ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Worksheet Code
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" a écrit dans le message de groupe de discussion : ... 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" wrote: 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" a écrit dans le message de groupe de discussion : ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SELECT THE FIRST CELL IN ADVANCE FILTERed worksheet vba code | Excel Programming | |||
VBa code to select from worksheet | Excel Programming | |||
VB select code for a worksheet | Excel Programming | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) |