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

0
Utf
2/11/2010 3:24:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
623 Views

Similar Articles

[PageSpeed] 46

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.
0
Phil
2/11/2010 5:04:06 PM
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.
> .
> 
0
Utf
2/11/2010 8:02:01 PM
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 <Eric@discussions.microsoft.com>
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.
>> .
>> 

0
Gord
2/11/2010 10:52:21 PM
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 <Eric@discussions.microsoft.com>
> 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.
> >> .
> >> 
> 
> .
> 
0
Utf
2/12/2010 1:25:01 AM
Reply:

Similar Artilces:

referencing to worksheet names in macro for each new worksheet inserted
Hi I created a code to insert new worksheets and rename them according t values on the new worksheet itself. Say in Cell D1, i have th worksheet name. My question is when i want to refer to this worksheet in subsequen coding, how should i code it? For eg, How should i write the ???? for Sheets("????").select? Would creatin the a variable to store the names help? Thanks in advance Ken -- Message posted from http://www.ExcelForum.com After inserting your new worksheet set it's name equal to a variable. For example SHEETS.ADD VWORKSHEET = ACTIVESHEET.NAME This method ...

automatically close second workbook 12-16-09
i have put the following code in to workbook 'A' to close workbook 'B' but it does not close book 'B' there is no error message Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Workbooks("B.XLS").Close SaveChanges:=False ActiveWorkbook.Close SaveChanges:=True End Sub Several of us have tested your code..........both workbook_open which opens B.xls when A.xls opens and the workbook_beforeclose code. Works fine for me. All I can think of is that you are opening B.xls in a separate instance of Excel. If t...

How do I modify an existing worksheet to remove columns & contents
I am working with a very large spreadsheet and want to modify it using only certain columns and data in order to keep from re-doing the entire worksheet. ANybody out there in cyberspace got suggestions or good reference sources I can use? Gil There are just too many scenarios to ponder without getting some more details on what you wish to do. Please be a little more specific in your description. Gord Dibben Excel MVP On Wed, 9 Feb 2005 18:15:06 -0800, "Gil Gray" <Gil Gray@discussions.microsoft.com> wrote: >I am working with a very large spreadsheet and want to m...

VB code for Macro
I have set up a rule on my InBox to check for specific words and move emails to my Work folder. Now I review emials in my Work folder and drag and drop them into 1 of 4 folders based on a number 1-4. After clicking on the folder, I need to perform the following on each of the four folders: Click on first email in the folder Clt+A (to select all the emails in the folder) Ctl+C (to copy) Drag the selections to a folder name HH Click #_Button (customized button set to send an email) Ctl+v (to paste the contents in the body of the email) Click Send Steps without the comments: Click folde...

Process all cells in a (user) selection
I am using this code to give me cell by cell access to a code defined selection; Dim aCell As Range For Each aCell In Sheet4.Range("A4:A34") ' Do Stuff Here Next aCell which processes every cell in the A4:A34 Range How do I do the same thing for a selection drawn by the user before pressing my 'Process' button ? Thanks On 05 May 2010 11:10:21 GMT, Isis <isissoft@NOSPAMbtinternet.com> wrote: >I am using this code to give me cell by cell access to a code defined >selection; > >Dim aCell As Range >For Each aCell In S...

Cannot open outlook at all
I have never used outlook, Now I was told that is better to forward all my e-mail addresses to outlook but the problem is that I cannot even open outlook. a popup window comes on and this is what it says... Unable to display the folder. Microsoft outlook could not access the specified folder location. The DLL file for the information service could not be found. MAPI was unable to load the information service PSTPRX. DLL. Be sure the service is correctly installed and configured. Please I really need help. thank you very much, Alicia Origel See if the information in the following MS...

No email stationery when through code
Hi I am creating email via code from access using below; Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Set objOutlook = CreateObject("Outlook.Application") Set objOutlookMsg = objOutlook.CreateItem(olMailItem) objOutlookMsg.To = "recepient@mydomain.com" objOutlookMsg.Subject = "Email Subject" objOutlookMsg.Body = "Dear abc..." objOutlookMsg.Display The problem is that the default signature that is set in outlook doe snot come up in the email. How can I fix it? Thanks Regards Am Sun, 17 Sep 2006 21:11:45 +0100 schri...

How to view the code for excel built-in functions?
Is it possible? -For example the function PMT(). thanks. No, the code is compiled, so it would likely be less than useful anyway. About the best you can do is check out the equations used in Help (see "PV"). In article <OSU3OXOBGHA.1676@TK2MSFTNGP09.phx.gbl>, "serdar" <s@s.com> wrote: > Is it possible? -For example the function PMT(). > thanks. ...

VBA- Application.Wait?
Does PowerPoint not have "Application.Wait" like Excel? Is there another way to pause code for a few seconds in PPT 2003/2007 while a small bat file runs? -Mel On 3/10/10 2:29 AM, Mel wrote: > Does PowerPoint not have "Application.Wait" like Excel? Is there > another way to pause code for a few seconds in PPT 2003/2007 while a > small bat file runs? > > -Mel From Example 8.4 on my Web site (http://www.PowerfulPowerPoint.com/): Sub Wait() waitTime = 5 Start = Timer While Timer < Start + waitTime DoEvents ...

Automatically Purging Transaction Logs
Hello, I am running Exchange Server 2003 and am looking for a method to aoutomatically purge transaction log files after succesful back up. Can anyone give me a good method of doing this? Thanks in advance. A successful backup automatically purges the transaction logs (if you're using Exchange-aware backup sw) -- Bharat Suneja MCSE, MCT -------------------------------- "Hunter" <Hunter@discussions.microsoft.com> wrote in message news:4DF7D5C6-5422-4FAB-BC45-E9284E8F0CB9@microsoft.com... > Hello, > > I am running Exchange Server 2003 and am looking for a ...

Automatically redirecting profiles to a new exchange server?
OK old exchange server was SERVEROLD and new one is SERVERNEW (for arguments sake). The pc has lots of different profiles on it and I dont wanna manually change each profile to reflect the new server. Running Outlook 2002 is there a way to automatically do this? I believe I can use a prf file created using Custom Wizards from the Office Resource Kit and deploy this to do this for me. Can anyone give any more detailed info on how this can be done? Thanks <carlosvenegas@jellybeandesign.co.uk> wrote in message news:1132785375.328184.274520@g47g2000cwa.googlegroups.com... > OK ol...

Can you delete and empty an open email
Hi there Can you delete and empty an open email? I know I can delete and empty a closed email by holding down shift and then deleting, but have not been able to figure out how to delete and empty from an open email. Is this even possible? Thanks for your help. Kylie On Wed, 18 May 2005 20:01:03 -1000, Kylie B"" <KylieB@discussions.microsoft.com> wrote: > Hi there > > Can you delete and empty an open email? I know I can delete and empty a > closed email by holding down shift and then deleting, but have not been > able > to figure out how to delet...

Windows XP: Open file in read-only: Word, Excel, Visio
I'm often sharing files on a network drive with someone. Often, one of us knows that we don't need to open a file for modification, and we only need read-access. Is there a way to open a file in read-only mode so that when someone does need to open it for modification, the read-only user is not blocking that? If there is not a way to do this native to Windows XP, is there a way to do it from the three applications that we use most, namely Word, Excel, Visio? All are 2003 versions. Thx. I used to save files like this in "read only recommended" mode. File|SaveAs|Tools|Ge...

Automatically generate a new record
Can I autumatically generate a new record adding information (future date) from a current record? Yes. In your form have Double-click event call an append query that pull data from your form fields to append one record. INSERT INTO [YourTableToAppendInto] ( Field1, Field2, Field3, Field4 ) SELECT TOP 1 [Forms]![YourForm]![1stField] AS [X], [Forms]![YourForm]![2ndtField] AS [Y], [Forms]![YourForm]![2rdtField] AS [Z], [Forms]![YourForm]![4thtField] AS [A]; -- KARL DEWEY Build a little - Test a little "Crown Jenny" wrote: > Can I autumatically generate a new record addin...

How to remove the /dde from Windows Explorer File Open on Install
I have an MDI app that I would like to open a file on a double click in Windows Explorer. Unfortunately the Explorer File Type (Tools>Folder Options... File Types tab) is set so that the command invokes dde. Ie. The "open" option under the "Advanced" button is C:\Path...\App.exe /dde and the DDE message is [open("%1")]. This never seems to work as my app never sees the dde message and the initialization of m_pCmdInfo via: m_pCmdInfo = (CCommandLineInfo*)(UINT_PTR)m_nCmdShow; sets the pointer to one and the app fails on exit. This is referred as a kn...

Open web page from Word 2007
I have the following code to open a web page: Dim IE Set IE = CreateObject("internetexplorer.application") mySTR = "http://MyWebPage" IE.Navigate mySTR The code works if I run it from vba - it opens the page on top of Word. But if I assign it to a QAT or Ribbon button, it opens the page but leaves it minimized. How can I get it to open on top of Word? ...

Publisher 2003 files try to open in Publisher 97 #2
Back again, but with a different Pub 2003/Pub97 issue Have Office 2003 with Publisher 2003 and Publisher 97 both on my XP Pro system Recently I find that files created in Pub 2003 are being saved as files with the Pub 97 Logo, and when you try to open them from the file listing, they open Pub 97, which correctly posts a box telling me that it cant open the files. If I use Publisher 2003 to open the same files from within the application the work fine. When I select a 2003 Pub file which contains the Pub 97 icon, the file list does not contain Publisher 2003, but when I browse and locat...

extraction code from celd
Hi :) I have a Excel problem :confused: MY QUESTION IS: if A5 = "JhoN FreD SmitH ChonG then A6 = "JNFDSHCG" I would like have got solution ;) please !!! thank you ver much nando4000@latinmail.co ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com You could create a User Defined Function: '=============================== Function GetCode(rng As Range) As String Dim i As Integer Dim str As String i = Len(rng.Value) For i = 1 To i str = Mid(rn...

Open excel file
Hi, I want to open excel file using a commnad button placed in a form. I have read the other questions to this to use runapplication. But, what I am looking for is as follows: i have tax database which contain the taxpayer name, taxyear and the Taxid number. I have placed this table in a form. The tax caluclations are made in excel and stored in a particular folder say named, Tax_comp and with filenames Taxpayer_year.xlsx. I want to place a command button on the form, and when the user clicks the command button, i want the particular tax payer excel tax computation file sto...

Can't open My Money 2000
Hello all, Version 4.90.3, Windows ME I am not sure if this is the proper newsgroup for this program, and if not, I do apologize. I have My Money 2000 on my computer, it came with the original install when I bought my computer. For some reason, I have not been able to open the program since last Sunday. I only use this for my personal check registry, and have all my records and such backed up to a floppy and a separate folder. I went to the folder that has the My Money files and tried to reinstall it by using the Setup, but, it says 'Setup could not open the file: C:\APPLZIP\MONEY\MONE...

Linking Drop-down list to worksheets
I need 20 dropdown list on the worksheet. some of drop-down lists has the same values. I need a list thats has one set of names and worksheet has another set of names. Here is a example want I need. LIST names Worksheet names Red - Black 770rb Black-White 770bw My list will have 15 items in each list. When user click on the color it would take them to that worksheet. What is best way to do this? ...

compare two columns with different ranges in two worksheets
I need to compare two columns of data in two different worksheets and display a third one. Here it is an example: -(worksheet1!A1:A10), (worksheet1!B1:B10) and (whorksheet2!C1:C25) -this is my query, if C5 is already in (A1:A10) I want to display B5 in worksheet2!D5 I think it is tricky because you need to identity which row in the A1:A10 is equal to C5 to display B5 and the range are different. you could save my day chris90 In worksheet2!D1: =if(isna(vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)), "", vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)) HTH Kostis Vezerides brilliant, ma...

Slow Open of WORD
I am running MS Office XP Pro under Windows XP Pro. After downloading a Word file from the Internet and openning it with Word, Word began openning very slowly and giving the message "requesting virus scan ..." at the bottom of the Word screen. Excel started doing the same thing. All other programs on my computer continued to open normally. I run Norton Systemworks with Antivirus. I also began to notice that Windows would boot slowly and Systemworks would also open slowly. I ran a virus scan (with latest definitions) on my computer and no files were infected. After r...

How do open a scanned doc with Word?
The scanned doc shows up when I launch My Pictures or when I am attaching a doc to send in an email with Outlook, but when I jsut launch word and go to the folder the scanned docs, whether JPG's or PDF's, do not appear. Thanks. You'll need OCR software so that you can convert the document into a format that Word can read. You may find such software on the disc that came with your scanner. -- Stefan Blom Microsoft Word MVP "vwj" <vwj@discussions.microsoft.com> wrote in message news:ED74F5BD-3241-411D-AF93-1846E14BABC7@microsoft.com... > ...

opening a powerpoint document which is protected by Information Rights Management
We have a Powerpoint 2007 document which some former employee in our office created before he left a year ago. Now, we need to open it and when we try it, the document mentions you don't have read permission for this. Do you want to obtain permission from that person's organizational email address which does not exist as he has left. We tried to contact the former employee who does not have that document with him at present. Is there any way we can open the document? I don't know enough about Information Rights Management(IRM), but understand like PGP it is a tool ...