+ Reply to Thread
Results 1 to 12 of 12

Increase cell value with one on worksheet activate

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Increase cell value with one on worksheet activate

    Hello everyone
    I have two sheets .. Sheets("INF") and Sheets("List")
    I need to put a code in worksheet activate event in List sheet ...every time I activate the List sheet ,range("E1") would increase by one till specific number (according to criteria)

    The criteria would be the value of range("B8") in Sheets("INF")
    If sheets("INF").range("B8")="Pre" then >> when activating sheets("List") ,range("E1") would be 1 for the first visit ..if I activate another sheet then activate Sheets("List") the range ("E1") would be increased again by one so the new value would be 2 .. then again if I deacativate the List sheet and activate it again the value of range ("E1") would be 3 and this is the end point ...if activated List sheet again the value would be 1 again ..

    In other words >>my target is to increase sheets("List").range("E1")
    If sheets("INF").range("B8")="Pre" so the changes would be between 1 to 3 only
    If sheets("INF").range("B8")="Pri" so the changes would be between 1 to 6 only
    Hope it is clear
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Increase cell value with one on worksheet activate

    like this ?


    Kind regards
    Leo
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Increase cell value with one on worksheet activate

    Great great
    Very nice and easy
    Can I change my request a little?
    I need to change the range("E1") value every 5 seconds in the same way (as another approach)
    this action would be in worksheet activate event and when deactivating the sheet the timer stops ...
    After activating again the values start over again beginning with the value 1
    Thank you very much

  4. #4
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Increase cell value with one on worksheet activate

    This I leave to the specialists, it is a little bit much beyond my knowledge

    Kind regards
    Leo

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Increase cell value with one on worksheet activate

    Thanks a lot anyway ...you have helped me a lot
    Waiting for solutions from experts

  6. #6
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Increase cell value with one on worksheet activate

    Try the attached. This isn't something I do often, but I just helped someone with it the other day. This seems to work, don't know if it's a best-practice way to get it done.

    In the List WORKSHEET code module put this.
    Please Login or Register  to view this content.
    In a separate (non-object) code module put this:
    (Also I gave the "List" worksheet a VBA project 'codeName' of W_LIST)

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Increase cell value with one on worksheet activate

    Great and wonderful ..
    Just I need to put an end point as I tested it and noticed it is increased all the time ..
    As for "Pre" I need to start at 1 and end at 3
    As for "Pri" I need to start at 1 and end at 6
    Thanks a lot for help

  8. #8
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Increase cell value with one on worksheet activate

    As for "Pre" I need to start at 1 and end at 3
    As for "Pri" I need to start at 1 and end at 6
    Ok - I thought you had changed your requirements.

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Increase cell value with one on worksheet activate

    I haven't changed it
    In other words >>my target is to increase sheets("List").range("E1")
    If sheets("INF").range("B8")="Pre" so the changes would be between 1 to 3 only
    If sheets("INF").range("B8")="Pri" so the changes would be between 1 to 6 only
    As for "Pre" I need to start at 1 and end at 3
    As for "Pri" I need to start at 1 and end at 6

  10. #10
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Increase cell value with one on worksheet activate

    Code of normal module :

    Please Login or Register  to view this content.

    Code of Sheet "List" :
    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Increase cell value with one on worksheet activate

    Thank you very very much Mr. Karedog
    It works like charm ..
    Regards

  12. #12
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Increase cell value with one on worksheet activate

    You are welcome, glad I can help.


    Regards

+ 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] Use cell value to determine which worksheet to open or activate.
    By r2fro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-06-2014, 08:14 AM
  2. Replies: 3
    Last Post: 10-10-2012, 05:10 PM
  3. [SOLVED] Worksheet activate place text in cell.
    By Lensmeister in forum Excel General
    Replies: 5
    Last Post: 10-06-2012, 11:52 AM
  4. Click on a cell and have that worksheet with the same name activate
    By grimston in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-30-2012, 03:17 PM
  5. [SOLVED] Find last row in a worksheet and activate cell in column H
    By LiLi1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-23-2012, 02:20 PM
  6. code for selecting cell A1 on activate worksheet.
    By Blake 7 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-08-2012, 03:26 AM
  7. Activate Worksheet from a specific Cell
    By jbear01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-03-2010, 04:15 PM
  8. Replies: 3
    Last Post: 10-24-2006, 01:32 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