Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automatically activate VBA coding within worksheet?
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? Thank everyone very much for any suggestions Eric Private Sub Worksheet_Activate() Application.EnableEvents = False 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 With Me.Range("A1") If .Value = 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
|
|||
|
|||
How to automatically activate VBA coding within worksheet?
Hi Eric,
Try : Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Address = "$A$1" Then .... End if End Sub instead of Worksheet_Activate -- Normek "Eric" wrote: 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? Thank everyone very much for any suggestions Eric Private Sub Worksheet_Activate() Application.EnableEvents = False 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 With Me.Range("A1") If .Value = 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 ... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automatically activate VBA coding within worksheet?
I try it, but it does not work at all, even I manually press F2 and enter, I
don't understand by requiring (ByVal Target As Excel.Range) as input, since I don't call this function with parameter, the cell position $A$1 is hard code within function. Does anyone have any more suggestions? Thank everyone very much for any suggestions Eric "Normek" wrote: Hi Eric, Try : Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Address = "$A$1" Then ... End if End Sub instead of Worksheet_Activate -- Normek "Eric" wrote: 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? Thank everyone very much for any suggestions Eric Private Sub Worksheet_Activate() Application.EnableEvents = False 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 With Me.Range("A1") If .Value = 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 ... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automatically activate VBA coding within worksheet?
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? Thanks in advance for any suggestions Eric "Normek" wrote: Hi Eric, Try : Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Address = "$A$1" Then ... End if End Sub instead of Worksheet_Activate -- Normek "Eric" wrote: 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? Thank everyone very much for any suggestions Eric Private Sub Worksheet_Activate() Application.EnableEvents = False 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 With Me.Range("A1") If .Value = 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 | |||
How to automatically trigger the vba coding by opening worksheet? | Excel Programming | |||
How to automatically covert excel version by coding | Excel Programming | |||
Run Time Error on activate coding | Excel Programming | |||
Change Worksheet Button Caption on Worksheet.Activate | Excel Programming | |||
Why wouldn't calling the Activate sub for a sheet automatically call that sheet's Worksheet_Activate() sub? | Excel Programming |