Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limitation for worksheet_properties?
Will it be the limitation for worksheet_properties? so I must activate the
function by manually pressing F2 and Enter in order to process the coding. Does anyone have any suggestions? The following code is stored under specific worksheet, whenever the worksheet is opened, firstly it will update all links and do all the calculation for each cells. For the given code, it requires to manually press F2 and enter to update and trigger the rest of coding. However, the value within cell A1 is calculated by formula, without manually pressing F2 and Enter within cell A1, the rest of coding will not be performed without triggering cell A1, so does anyone have any suggestions on how to trigger the rest of coding without manually update the A1 cell's value? Thanks in advance for any suggestions Eric Private Sub Worksheet_Change(ByVal Target As Range) Dim myPic1 As Object Dim myPic2 As Object Dim myPic3 As Object Dim dblTop As Double Dim dblLeft As Double Dim dblHeight As Double Dim dblWidth As Double If Target.Address = "$A$1" Then On Error Resume Next Set myPic1 = ActiveSheet.Pictures("PicAtB10") Set myPic2 = ActiveSheet.Pictures("PicAtE10") Set myPic3 = ActiveSheet.Pictures("PicAtH10") On Error GoTo 0 If Not myPic1 Is Nothing Then myPic1.Delete If Not myPic2 Is Nothing Then myPic2.Delete If Not myPic3 Is Nothing Then myPic3.Delete .... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limitation for worksheet_properties?
I'm not exactly sure what you're asking, but The following code is stored under specific worksheet, whenever the worksheet is opened, firstly it will isn't consistent with the code you posted. The Change event occurs only when a value on that worksheet is changed by the user or through other code. It is not triggered when the sheet is made active (use the Worksheet_Activate event to run code when a sheet is made active) and it is not triggered if a cell is changed as the result of a calculation. Your code is written such that nothing will happen unless cell A1 is changed by the user, and remember that Change doesn't run if the change is the result of a calculation. The Worksheet_Calculate event will immediately after the worksheet has been fully calculated, so perhaps you can use that event. However, there is no real way to test whether A1 was change as a result of the calculation. When you press F2 in cell A1, you initiate a Change event even if you don't actually change the value. Changing a cell to its own value is still considered a change. Perhaps you can express yourself more clearly about what you need to accomplish and what you have tried. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] n Fri, 12 Feb 2010 13:28:05 -0800, Eric wrote: Will it be the limitation for worksheet_properties? so I must activate the function by manually pressing F2 and Enter in order to process the coding. Does anyone have any suggestions? The following code is stored under specific worksheet, whenever the worksheet is opened, firstly it will update all links and do all the calculation for each cells. For the given code, it requires to manually press F2 and enter to update and trigger the rest of coding. However, the value within cell A1 is calculated by formula, without manually pressing F2 and Enter within cell A1, the rest of coding will not be performed without triggering cell A1, so does anyone have any suggestions on how to trigger the rest of coding without manually update the A1 cell's value? Thanks in advance for any suggestions Eric Private Sub Worksheet_Change(ByVal Target As Range) Dim myPic1 As Object Dim myPic2 As Object Dim myPic3 As Object Dim dblTop As Double Dim dblLeft As Double Dim dblHeight As Double Dim dblWidth As Double If Target.Address = "$A$1" Then On Error Resume Next Set myPic1 = ActiveSheet.Pictures("PicAtB10") Set myPic2 = ActiveSheet.Pictures("PicAtE10") Set myPic3 = ActiveSheet.Pictures("PicAtH10") On Error GoTo 0 If Not myPic1 Is Nothing Then myPic1.Delete If Not myPic2 Is Nothing Then myPic2.Delete If Not myPic3 Is Nothing Then myPic3.Delete ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row Limitation | Excel Discussion (Misc queries) | |||
Way around row limitation | Excel Discussion (Misc queries) | |||
DDE limitation? | Excel Programming | |||
IF Limitation? | Excel Programming | |||
Limitation | Excel Programming |