+ Reply to Thread
Results 1 to 6 of 6

vba to limit the no of rows as per specified limit per day

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    vba to limit the no of rows as per specified limit per day

    I have workbook with tab sheet named workings , I would like to limit the no rows on 5 openings per day .

    The working area are from column A to S on that sheet.

    That is on first opening limit are 3000 rows .

    On second opening the limit are 2000 rows

    on 3rd opening the limit are 1000 rows.

    On 4th opening the limit are 400 rows.

    On 5th opening the limit are 100 rows

    With given message that limit is exceeded if more data is inserted.

    Thanks if anyone can assists

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: vba to limit the no of rows as per specified limit per day

    Open and close the attached file a few times, add some values etc and we can take it from there
    - key values are held in hidden sheet
    - unhide that sheet to reset the values manually

    Tell me what you want to do if limit of 5 is exceeded
    - authority to reset rows controlled by password possibly?
    - WARNING do not close from Workbook_Open without an escape hatch

    it's all too easy to close a workbook automatically as soon as it opens
    - take heed of this warning

    In workbook module:
    Please Login or Register  to view this content.
    In sheet module
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kev_; 01-14-2018 at 07:29 PM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: vba to limit the no of rows as per specified limit per day

    thank you Works well


    HTML Code: 
    Give warning limit has been reached , no data can be entered

    HTML Code: 

    Yes password required to reset rows

    HTML Code: 
    Yes

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: vba to limit the no of rows as per specified limit per day

    Try in attached file

    Some variable names have been changed to make the code easier to read
    The password to release rows is "abc"

    Entering the password currently makes 3000 rows available
    - method used is to reset the access count (ie between 1 and 5)
    - alter AccessCount value to match your requirements
    Please Login or Register  to view this content.
    Code has been kept very simple
    - closing file automatically is avoided
    - only one chance to enter password (closing\reopening is the second chance!)
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: vba to limit the no of rows as per specified limit per day

    Thank you worked as requested perfectly

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: vba to limit the no of rows as per specified limit per day

    Thanks for the rep

    Please go to ThreadTools at top of thread and mark the thread SOLVED
    Thank you

+ 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. sum to a limit then subtract to limit and go again
    By robk009 in forum Excel General
    Replies: 1
    Last Post: 07-29-2017, 08:37 AM
  2. Copy and insert row below within fixed upper limit and dynamic lower limit
    By droffel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-27-2015, 08:57 PM
  3. How to limit rows until you need them
    By simple? in forum Excel General
    Replies: 2
    Last Post: 08-26-2013, 11:55 AM
  4. Replies: 1
    Last Post: 04-13-2013, 05:19 AM
  5. [SOLVED] find the lower and upper limit if data is in between on that limit
    By clangeles in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-27-2012, 06:18 AM
  6. Replies: 1
    Last Post: 01-13-2011, 02:29 PM
  7. [SOLVED] Limit the Loop to 500 Rows
    By SteveF in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-26-2005, 11:05 AM

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