Only want one Worksheet_[event] to run but still cover all actions

Hello again,

I have code ("Sub DoThis" and "Sub DoThisE") that ensures the format in the
sheet. It should run no matter what the end user does with the cells...which
is alot, we all know that...

I now use these Sub Worksheet_[events]: Worksheet_Change,
Worksheet_Activate, Worksheet_Calculate.

I want the code "Sub DoThis" OR "Sub DoThisE" to take place only once, when
an event take place. As it is now an user event start the routines several
times. I believe it is caused by two reasons:
- Worksheet_[event] subs makes an overlap ?
- Application.OnKey / OnEntry makes an overlap?

- How_can_I_ensure the code to run only once every time an event/action take
place of the user on the sheet?
- How_can_I_ensure I cover all actions - select a new cell,  paste etc?
- What event cover the action when a cell changes value using a drop down
choice with "Verification & List approach (not as code, used the excel menue
choices)"

The code for each Sub Worksheet_[event] looks the same:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdataing = False

    If Not Intersect(Target, Range(Cells(1, 1), Cells(200, 1))) Is Nothing
Then
             Call DoThis
    Else
            Application.OnKey "~", "DoThisE"
            Application.OnKey "^v", "DoThis"
            Application.OnKey "{ENTER}", "DoThisE"
            ActiveSheet.OnEntry = "DoThisE"

     End If
            Application.OnKey "~", ""
            Application.OnKey "^v", ""
            Application.OnKey "{ENTER}", ""
            ActiveSheet.OnEntry = ""

Application.ScreenUpdataing = False

 End Sub


/Regards


0
Nobody4592 (20)
5/9/2004 4:25:01 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
569 Views

Similar Articles

[PageSpeed] 48

You could set up a flag that checks before your routine does anything important:

Dim DoThisWasRun as boolean
sub DoThis()
 
  if dothiswasrun then exit sub
  dothiswasrun = true

  'the rest of your code here
end sub

This means that it'll run only once -- ever!  The first time through, the
default value for the boolean variable is False.  You're code changes it to
true.  (Actally, this will exit your sub right away--until you change that
boolean variable back to false.)

Is that what you meant?

Maybe dothis was doing things that caused the other events to fire--causing a
recursive loop that goes until excel gets tired.

if that's the case, you can tell excel to stop looking for event triggers with
code like this:

 application.enableevents = false
 'do as much as you want to whatever you want
 'then turn it event handling back on.
 application.enableevents = true



Nobody@all.se wrote:
> 
> Hello again,
> 
> I have code ("Sub DoThis" and "Sub DoThisE") that ensures the format in the
> sheet. It should run no matter what the end user does with the cells...which
> is alot, we all know that...
> 
> I now use these Sub Worksheet_[events]: Worksheet_Change,
> Worksheet_Activate, Worksheet_Calculate.
> 
> I want the code "Sub DoThis" OR "Sub DoThisE" to take place only once, when
> an event take place. As it is now an user event start the routines several
> times. I believe it is caused by two reasons:
> - Worksheet_[event] subs makes an overlap ?
> - Application.OnKey / OnEntry makes an overlap?
> 
> - How_can_I_ensure the code to run only once every time an event/action take
> place of the user on the sheet?
> - How_can_I_ensure I cover all actions - select a new cell,  paste etc?
> - What event cover the action when a cell changes value using a drop down
> choice with "Verification & List approach (not as code, used the excel menue
> choices)"
> 
> The code for each Sub Worksheet_[event] looks the same:
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.ScreenUpdataing = False
> 
>     If Not Intersect(Target, Range(Cells(1, 1), Cells(200, 1))) Is Nothing
> Then
>              Call DoThis
>     Else
>             Application.OnKey "~", "DoThisE"
>             Application.OnKey "^v", "DoThis"
>             Application.OnKey "{ENTER}", "DoThisE"
>             ActiveSheet.OnEntry = "DoThisE"
> 
>      End If
>             Application.OnKey "~", ""
>             Application.OnKey "^v", ""
>             Application.OnKey "{ENTER}", ""
>             ActiveSheet.OnEntry = ""
> 
> Application.ScreenUpdataing = False
> 
>  End Sub
> 
> /Regards

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/9/2004 11:46:30 AM
Reply:

Similar Artilces:

simple guide to merging 2 worksheets into one request
Hi, I am a basic excel user with little knowledge! I have been asked to merge 2 worksheets together using a unique ID as the method to combine data, for example, WS 1 - membership no - address - phone no WS 2 - membership no - name - date of birth Required WS3 - membership no - name -address -phone no - date of birth I have found lots of VB code but to be honest I have no idea what it is, where it needs to go on the workbook or how to even start to use it. Is there a very simple way to achieve this other than cut and paste (I have over 10k records)? If someone code provide an idiot...

Running out of space #2
Using exch 2000 server. I had 100 GB on a RAID card, but now I am only left with 4 GB, after 6 months of usage? When I look at the mail box usage, it does not add up to that. When I use exmerge, it only needs 27GB to backup all data. I am not using too much public folders etc. MDBData folder is 95.5 GB? How can I control this crazy swelling of data? And how do I delete the data that is not required anymore? Thanks Check the application event log. There will be some Event ID 1221 entries. 4 per night for the public and 1 per night for the private. How much free space does it report? If...

how does one become an excel beta testor
i herd the new version of office in beta is do out next month - how cqn I sign up to be a testor. I am really only interested in excel - If I could get a hold of this new version of excel with it's 1.1 million rows i could shut down allot of plans to go to third party software at my firm and make much better use of excel. ( excel & access combined) I woul dprimarilyy be makin gextensive and advanced use of pivot tables. I would be glad to particpate in any feedback programs that thi sopportunity would require. Thanks for your time ...

I want to set up a form in excel where I can place a jpeg or pic.
The goal is to set up an excel form where I can place a jpeg or pic. I am very low level excel user but this is a big deal document for me. What do you plan to do with it Karl? Could you give us more information? I mean... you can just use Insert-->Picture-->From file to insert a jpg. What do you want to do? ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "karl" <karl@discussions.microsoft.com> wrote in message news:787A30AE-3809-4466-85DE-DA56A0E99616@microsoft.com... > The goal is to set up an excel form where I can place a jpeg o...

running a Macro in 2007 which was created in 2003
Hi there, I hope you can help, I am not VB literate unfortunately but I am trying to run a macro with the following line of code which creates an error: LastRow = Master_Wksht.Range("N" & Rows.Count).End(xlup).Row Is there a change between versions which means this line won't work any more? Simon, That line is fine in E2007 provided you have 'SET' Master_Wksht but if you hadn't done that it wouldn't have run in earlier versions of Excel Set Master_Wksht = Sheets("Sheet1") What have you dimmed LastRow as it should be long. ...

Dating 6 events for 5 groups on a 18 month cycle
I have been trying tocalendarise 6 key dates in a cycle for 5 groups Each group A,B,and C all have different start dates to their cycle in the 18 month period - after that time it all repeats itself. Is there anyway this can be done I also need to find some way of labelling these key dates - Thanks no Denise - Despite Colin's certainty that it can't be done, I suspect all it requires is a better statement of your problem. Are you talking about a Gantt chart? If so, check out some of the links on this web page: http://peltiertech.com/Excel/Charts/GanttLinks.html - Jon ------- ...

Need to verify cell location before running a macro, how?
I am requesting users to click on a cell in column B (in which they are actually selecting a row of reference information), then to click on an icon which launches a macro that runs relevant to the row selected in column B. I would like Excel to verify that the selected cell is actually in column B prior to running the macro. If a cell in column B is not selected, I want to present a pop up message reminding them to select a cell in column B. Please help! If ActiveCell.Column <> 2 Then MsgBox "Don't Do That" Exit Sub End If -- Jim Cone Portland, Oregon USA http:...

Salesprocess not running after migration from CRM 3.0
Hi, We are trying to perform a test migration from 3.0 to 4.0. The migration goes OK, but after the migration all the sales process workflows have an error. The wfl's have the status "waiting", which is OK, because they are all waiting for an earlier created activity to reach the status "completed". Already found some info on an issue where workflow problems could occur when the website points to an IP adress in IIS, so changed that to "All unassigned". This didn't make any difference. After turning on tracing the tracefile for the AsyncService ther...

Event 9646 on object session
First post via Outlook Express never appeared, so retrying via html, sorry if ends up duplicate. Exact error: Mapi session "acbf5966-ad7a-4fc4-8641-badb0b0bc47d" exceeded the maximum of 32 objects of type "session". Search on KBs and EventID.net for 9646 don't mention object type session, can't find any reference for a regedit fix. No reference in error to a particular user, so don't know how to trace who might be causing error. Could the cause possibly be related to installing ESM on an XP workstation with Outlook 2000 SP3 installed, and then opening Ou...

Who Wants ADSL (BROADBAND)
This I think will work for most people. 1. Change your Telephone Company Back To Telstra 2. Check will Telstra how much demand their is in you area. That you can do on the bigpond website by typing in the search up the top ADSL DEMAND. 3. Check if you then can get it. That's on the bigpond website. 4. If you can get it then apply for it. 5. (OPTIONAL) If you don't want bigpond as your internet provider just change your ISP. Internet service providers The bigpond website is www.bigpond.com The IINET website is www.iinet.net.au The Optus website is www.optus.com.au The iprimus website...

how do i merge one workbook with another in excel?
hi i am wanting to merge data from 1 worksheet with another how do i do this? Copy/paste then manipulate. OR provide more detail on your layout and type of data to merge. Gord Dibben Excel MVP On Wed, 16 Mar 2005 11:33:04 -0800, "dd" <dd@discussions.microsoft.com> wrote: >hi i am wanting to merge data from 1 worksheet with another how do i do this? hi there thanks for your reply. i do not have exact data. it is for an interview question how do you merge spreadsheet A and spreadsheet B using excel ? and then how do you identify say for instance someones name who is ...

How do I take two pictures put them side by side to create one
I am trying to create one picture by placing 2 of them side by side. Use a photo editing program then insert the picture into Publisher. -- JoAnn Paules MVP Microsoft [Publisher] "coopskat679" <coopskat679@discussions.microsoft.com> wrote in message news:6EEB18D4-FB70-40AE-9CB6-AD40F8FD80A7@microsoft.com... >I am trying to create one picture by placing 2 of them side by side. coopskat679 wrote: > I am trying to create one picture by placing 2 of them side by side. Insert two pictures. Turn on the Snap to Objects option (Arrange > Snap > To Objects). ...

confusing error in access2003 db running in access2007
have opened a 2003 db in 2007...opens fine, but when adding records I am getting an error as follows: Microsoft Office Access -2147352567 Method ‘Nz’ of object ‘_Application’ Failed I can perform the exact same actions on the dB in 2003, and do not have a problem...I am wondering if the NZ method in conjuction with data on an sql server is invalid...I feel as if this particular dB is getting corrupted when being used in Access 2007...I have been unable to convert this dB to 2007....the error message is that the dB is already opened exclusively by another user...which is me...the dB si...

What causes event id 7031's
My exchange server's IISadmin service has (and other services bound to it) failed and restart 51 times. All other critical exchange services were restarting as well. I've stablized the server now (by deleting messages that may have been corrupted), but I'm really freaked out on how this happened. I've never had any errors on my exchange server untill today. Here are my server stats: Windows 2000 server SP4. Exchange 2000 ENT SP3 with all rollups. ALL hotfixes applied. Group Shield 5.2. GFI ME 9.0. 2 XEON's. 1 GB ram. 200 gb for Stores. 201 Mailboxes. Check this arti...

splitting text from one cell
I need to remove specified data from a single cell and place on the same row in another column. Not all data will be the same eg 100mg, 200mg etc Not too clear; what data? Do you want to extract the number from the text? This will extract 100 from 100mg and 5 from 5mg: =--LEFT(A1,FIND("m",A1)-1) Any help? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "rogera" <rogera@discussions.microsoft.com> wrote in message news:98106D4C-04D2-4A2D-B293-9F5BBF0F04EB@microsoft.com... >I need to remove specified data from a single cell and place on th...

How to determine what unkown procedure is running
I have a form that (with module) that is "misbehaving". In trying to diagnose the problem/s I've noticed the VBE displays "running" in the window title bar, after any open/load events have been processed.=20 The locals window shows "<Running>" but there are no expressions listed. How can I determine what procedure is causing this behavior? THANKS! David G. "David G." wrote in message news:5e9sk5h4vtgf81eeuvostv2g8u4b54g1gp@4ax.com... > I have a form that (with module) that is "misbehaving". In trying to > diagnose ...

still problems with Exchange 2010 and sending to certain domains
We have done the following: made sure the PTR records matches the IP address of the send connector created an SPF record we are still having issues sending to certain domains and we are at a loss. I've done plenty of 2007 systems and never had this issue. What are we doing wrong? It simply doesn't make sense. Do you get an error message back from the receiving server? JE. On 1/26/2010 10:14 AM, Gene Whitley wrote: > We have done the following: > > made sure the PTR records matches the IP address of the send connector > > created an...

I want to update a status reason in an email activity using Workfl
Hi, In 4.0 I am doing a workflow that changes status reason in an email activity .. I want to update it on demand, ie I am going to open an email and run the Workflow to change the status reason. I created a workflow for email entity and enable only on demand, I then added the step for updating EMail. When I click Set Properties Email Form is blank. I then change the only Status Reason to the some value which is allowed in this variable. Do I need to care about other values on this Form. At present I change the value only for status reason. But this workflow does nothing when I run on...

Worksheet event help #2
Have the following in a sheet code that i would like to activate when the enter key is pressed in B5. tried the following, but no success, the 1st part i was hoping would be the change event that would run a sorting macro in the 2nd part. any help??? (1st Part) Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address(0, 0) = "b5" Then (2nd Part) Application.ScreenUpdating = False ActiveWindow.FreezePanes = False Range("A4").Select ActiveWindow.SmallScroll Down:=234 Range("A4:B273").Select Selection.Sort Key1...

FORM and ACTION stripped in OWA 2003
Our company has a product that sends out form-like emails. Users fill out the information in the email, press a submit button, and their responses are recorded on our server. This works find with most mail clients, including Outlook 2000 and 2003. However, in OWA 2003, it appears that the FORM and ACTION elements are stripped from the email, so the forms don't work. Is there any way to modify the behavior of OWA2003 with respect to which html verbs are blocked? Sample code from the emails is shown below: <form name="payments" method="get" action="htt...

Transfer from one bank to another
I'm a new MSMoney user and just set up a new bank account. I wrote a check from one existing account to the new account in a different institution. How should I catagorize the transfer of funds? MSMoney, in the budget tracking, etc. seems to show the the transfer as income (but to me it's not new dollars). Any suggestions? In microsoft.public.money, PMHicks wrote: >I'm a new MSMoney user and just set up a new bank account. I wrote a check >from one existing account to the new account in a different institution. How >should I catagorize the transfer of funds? ...

Multiple types of charts in one chart
Is it possible to do the following: Stacked bar chart for 3 data series. Regular bar chart for 1 data series. Line chart for 1 data series. I've used the custom "line-bar chart 2 axes" combination chart which nearly solves the issue, except that it won't allow me to split up the bar charts. Alternatively, I've also managed to get it "working" except that the bar charts block each other. Is there a way of shifting the bar charts so they do not overlap? I am using Office 2003. Thanks! Alan wrote: > Is it possible to do the following: > > Stacked...

Some users are not in the Global address list of one of the servers
Hi, We have several exchange servers (5.5) running on Win NT 4.0 with identical configuration. However, each server is located in different cities. All mails coming to/from the servers pass thru a central main mail server. The problem is, two of the servers does not show some mailboxes / recepients that are present in the other servers. This has caused problems when a user from the main central server sends an e-mail to one of the servers wherein the user from the main central server gets the "Unknown Recipient" error. See below for an illustration of how the servers are configured:...

Finding Similar entries in one column in another
Hi, I am relatively new to using Excel with vast amounts of data and was after any suggestions which could help me complete a problem that I have.. I have two columns with descriptions in (approximately 10 words). I need to find descriptions from one column (Sheet 1,column C) which are similar to the other column (Sheet 2,Column A).For example,if I could display the number of words which are similar to both columns I could then find the ones with the largest number of similar words. For example, the two columns could look like... Sheet1,column C.... Description 300g Fish fillets She...

Publisher 2000 will not run
I had problems with Publisher 98 not running which we=20 never did solve so I installed office 2000 to see if=20 Publisher 2000 would function. Same problem: The flash=20 screen pops up and then disappears. No program. Microsoft=AE Publisher 2000 Version 6.0 has encountered a=20 problem and needs to close. We are sorry for the=20 inconvenience. Howard, hi again, Have you tried opening Publisher in safe mode? Publisher retains all printer information within its publications. If you can open Publisher in safe mode, either regress or update your printer and video drivers. -- Mary Sauer MS MV...