Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automatically trigger the vba coding by opening worksheet?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automatically trigger the vba coding by opening worksheet?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automatically trigger the vba coding by opening workshe
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 "Phil Hibbs" wrote: 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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automatically trigger the vba coding by opening workshe
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 On Thu, 11 Feb 2010 12:02:01 -0800, Eric wrote: 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 "Phil Hibbs" wrote: 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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to automatically trigger the vba coding by opening workshe
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 "Gord Dibben" wrote: 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 On Thu, 11 Feb 2010 12:02:01 -0800, Eric wrote: 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 "Phil Hibbs" wrote: 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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with automatically opening another worksheet | Excel Discussion (Misc queries) | |||
Macro To Run Automatically For Any Worksheet Upon Opening Of THe F | Excel Discussion (Misc queries) | |||
One particular worksheet always automatically opens when opening E | Excel Discussion (Misc queries) | |||
Opening a worksheet automatically at start up of computer | Excel Worksheet Functions | |||
Run macro automatically on opening of worksheet | Excel Worksheet Functions |