+ Reply to Thread
Results 1 to 7 of 7

Alter Worksheet Change Event at specific time after the data is manually changed..

  1. #1
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2,175

    Alter Worksheet Change Event at specific time after the data is manually changed..

    Dear Forum,

    I need to use the Worksheet Change event in a particular sheet in a specified column which works fine if the data is already there in the sheet and then changed however,the data is in this sheet is actually a Sub-Set of a Main sheet i.e certain filtered records are being copied from Main Sheet and then copied to this IBSL Sheet.

    After the data is copied I have to check each record manually and then categorize each record as Fresh , Rebooked , Cancelled , Tranch or On-Hold.....These 4 criterias are added in the Column 38 and the same thing has to be repeated in the column 40 , so when i change the data in the column 38 the same category has to be updated in the same row in the column 40..

    But the problem is that the data is first copied from the Main Data Sheet into the IBSL Sheet using a Macro so then this even t gets fired and goes in the DEBUG MODE...

    I need this to happen when i change the category manually..I am adding data validation at the same time while copying the data in to the TEMP sheet.

    So what can be done to achieve..


    Please Login or Register  to view this content.

    In the IBSL Sheet I make the changes in the Column 38 which needs to be repeated in the Column 40



    Regards
    e4excel

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,594

    Re: Alter Worksheet Change Event at specific time after the data is manually changed..

    Hi

    I always define a ChangeFlag at project level.
    This is a Public variable at the top of your Main Macro Module.

    Please Login or Register  to view this content.
    Then set this flag to zero at the start of your macro.

    Then before you import your data set this change flag to 1.

    At the Start of your work sheet change event, enter this line.

    Please Login or Register  to view this content.
    You will need to reset your changeFlag to zero at the end of the import process.

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    7,994

    Re: Alter Worksheet Change Event at specific time after the data is manually changed..

    Hi, e4excel,

    Please Login or Register  to view this content.
    at the beginning of any macro/procedure should turn off any Events. Donīt forget to turn them on again at the end of the macro or the errorhandler.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2,175

    Re: Alter Worksheet Change Event at specific time after the data is manually changed..

    Quote Originally Posted by HaHoBe View Post
    Hi, e4excel,

    Please Login or Register  to view this content.
    at the beginning of any macro/procedure should turn off any Events. Donīt forget to turn them on again at the end of the macro or the errorhandler.

    Ciao,
    Holger
    CAn you please explain which events are you referring ?

  5. #5
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2,175

    Re: Alter Worksheet Change Event at specific time after the data is manually changed..

    Dear mehmetcik

    In the below code, where should i define the variable ?

    Here :

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    or

    Here

    Please Login or Register  to view this content.

    And when do I make it 1 as initially the Sheet will receive the data using the code in the first post and then I will be manually working in this sheet

    Regards
    e4excel

  6. #6
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2,175

    Re: Alter Worksheet Change Event at specific time after the data is manually changed..

    Dear mehmetcik,

    I tried that but somehow I am not able to get the desired effect..

    Please Login or Register  to view this content.
    Then I put the line


    Please Login or Register  to view this content.
    in the Worksheet_Cahnge..

    But its still not working


    This was a dummy example as the other file is in the office where no internet access is provided

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    7,994

    Re: Alter Worksheet Change Event at specific time after the data is manually changed..

    Hi, e4excel,

    from what I understand you would need to copy over the data into another sheet without the event behind the sheet to be triggered. Thatīs what I suggested when I posted the csample ode line.

    You should consider to only post complete procedures here and even those codes being called from there (the main procedure is broken down somewhere down the lines and another procedure isnīt displayed here at all).

    This is what I could make up so far from my guesses:
    Please Login or Register  to view this content.
    This is one of the rare cases that I would cosnider to use
    Please Login or Register  to view this content.
    at the begin of the procedure as well to turn the event on at the end in case of any error.

    Ciao,
    Holger
    Last edited by HaHoBe; 07-20-2014 at 06:14 AM.

  8. #8
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,594

    Re: Alter Worksheet Change Event at specific time after the data is manually changed..

    Sorry Guys

    I've been away.

    Try declaring Changeflag as a public variable. I always declare it in the main macro module.

    this is an extract of some code I wrote for another user:-

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Event Change code to protect formula if data entered manually
    By Chron6767 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-03-2014, 06:52 PM
  2. Replies: 6
    Last Post: 08-10-2011, 07:19 AM
  3. Excel Worksheet Change Event in a nick of time
    By TKT-Tang in forum Excel General
    Replies: 2
    Last Post: 03-23-2006, 05:35 AM
  4. [SOLVED] change event on specific cell rather than worksheet
    By frendabrenda1 in forum Excel General
    Replies: 3
    Last Post: 09-20-2005, 10:05 AM
  5. Worksheet Change Event-when a cell with a certain range of cells) are changed?
    By Steph in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-25-2005, 07:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1