How to automatically trigger the vba coding by opening worksheet?

[Eric]

The following code is stored under specific worksheet, whenever the worksheet
is opened, it required to 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, 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

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

 

[Phil Hibbs]

If I understand you right, you want the Worksheet_Change sub to be
called when the workbook opens? Just remove the "Private" from the
declaration, then make a Workbook_Open sub in the ThisWorkbook code
area like this:

Private Sub Workbook_Open()
Sheet1.Worksheet_Change ([A1])
End Sub

Phil Hibbs.

 

[Eric]

Do you mean to change the following coding like this?
I try it, but error message occurs about "Cannot find method or data ..."
Do you have any suggestions?
Thank you very much for any suggestions
Eric

Private Sub Workbook_Open()
Sheet3.Worksheet_Change ([A1])
End Sub

'---------------------------------------------------------
'---Private in front of Sub Worksheet_Change has been removed.
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

 

[Gord Dibben]

Eric

If A1 is a calculated value then maybe you should be using
worksheet_calculate method.

When A1 reaches a certain value the event will be triggered.

Private Sub Worksheet_Calculate()

Application.EnableEvents = False
With Me.Range("A1")
If .Value <> "" Then
or
If .Value = 132.6 Then

Or use the woksheet_activate event with the same rule for A1


Gord Dibben MS Excel MVP

Do you mean to change the following coding like this?
I try it, but error message occurs about "Cannot find method or data ..."
Do you have any suggestions?
Thank you very much for any suggestions
Eric

Private Sub Workbook_Open()
Sheet3.Worksheet_Change ([A1])
End Sub

'---------------------------------------------------------
'---Private in front of Sub Worksheet_Change has been removed.
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

If I understand you right, you want the Worksheet_Change sub to be
called when the workbook opens? Just remove the "Private" from the
declaration, then make a Workbook_Open sub in the ThisWorkbook code
area like this:

Private Sub Workbook_Open()
Sheet1.Worksheet_Change ([A1])
End Sub

Phil Hibbs.
.

 

[Eric]

I try it on sheet5, but it does not work, and I have previous coding on
sheet3, which work manually, but after activate following coding, previous
coding on sheet3 does not work too. When I close this file, and reopen it,
then the previous coding on sheet3 work again, I find out once I activate the
following code, the previous coding on sheet3 will not work.
Do you have any more suggestions?
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

Eric

If A1 is a calculated value then maybe you should be using
worksheet_calculate method.

When A1 reaches a certain value the event will be triggered.

Private Sub Worksheet_Calculate()

Application.EnableEvents = False
With Me.Range("A1")
If .Value <> "" Then
or
If .Value = 132.6 Then

Or use the woksheet_activate event with the same rule for A1


Gord Dibben MS Excel MVP

Do you mean to change the following coding like this?
I try it, but error message occurs about "Cannot find method or data ..."
Do you have any suggestions?
Thank you very much for any suggestions
Eric

Private Sub Workbook_Open()
Sheet3.Worksheet_Change ([A1])
End Sub

'---------------------------------------------------------
'---Private in front of Sub Worksheet_Change has been removed.
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

If I understand you right, you want the Worksheet_Change sub to be
called when the workbook opens? Just remove the "Private" from the
declaration, then make a Workbook_Open sub in the ThisWorkbook code
area like this:

Private Sub Workbook_Open()
Sheet1.Worksheet_Change ([A1])
End Sub

Phil Hibbs.
.

.