Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2012
    Posts
    8

    Unanswered: Close excel with wrong data

    Hi

    I am new to this forum, can somebody help in the following query

    I have a database with almost 2000 records; with data validation i prepared a statement for some confidential data and with a particular code each individual can see their related data (for example with their login id they can check their records).

    Now is it possible to close the excel file if they enter any random data (maximum tries should be limited to 2-3)

    Please guide me.

  2. #2
    Join Date
    Jan 2012
    Posts
    97
    Tough to answer this question without being able to see the 'statement' that you mention? Can you post the statement here so that we can understand your situation?
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  3. #3
    Join Date
    Feb 2012
    Posts
    8
    My database consists two woksheets; sheet1 consists all the data in sheet2 if the person enters his "SMPID" all the relevant data appears in the rows accordingly. what i want is if a person enters "SMPID" randomly (to view others information) the workbook should close after 2-3 attempts.

    I dont know whether it can be done or not, I am just trying to protect the personal information of the people.
    Attached Thumbnails Attached Thumbnails screenshot.jpg  

  4. #4
    Join Date
    Jan 2012
    Posts
    97
    Slightly unsure about how this will provide security but have tried to answer your question as best I could.

    I am assuming from your screen shot that on 'sheet2' if the person enters their SMPID that there is some sort of lookup formula that uses this value to retrieve data from 'sheet1'.

    In the attached workbook there is an example whereby you can control the entry into a cell through an input box. Input box data entry can also be limited to a specified number of attempts (3 in the case of the example I have provided). If the user exceeds 3 attempts, i.e. on the fourth, the workbook will close. Upon entering a SMPID the code checks for a match (you will need to include all valid SMPID's in the code as I have only used '1234' as an example) and if there is a match the SMPID is entered into cell B2. If not the user is prompted again for their SMPID and the number of attempts changes to reflect the incorrect entry.

    In terms of security I'm unsure if 'sheet1' is hidden or how it is protected? You should also protect the VBA project itself. Also by disabling macros on open the user is free to enter whatever they want into the SMPID cell, this would need to be locked with worksheet protection to prevent uncontrolled data entry.
    Attached Files Attached Files
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  5. #5
    Join Date
    Feb 2012
    Posts
    8
    Dear Sir

    its working fine, but i have almost 2000 records in my database, how can I declare all the "SMPIDs" in the code.

    As you told sheet1 consists all the data and with vlookup i am trying to display all the data on sheet2 with the help of "SMPID" (as its unique in our records). I put the sheet in invisible mode thru VBA and its protected.

    Is it possible to declare all the 2000 records' SMPIDs in the code

    please guide me

  6. #6
    Join Date
    Jan 2012
    Posts
    97
    You wouldn't have to declare all the SMPID's in the code. Providing you have them in a location in the file then you can check to see if the input box entry matches an entry in a specified list.

    See attached file with modified function.
    Attached Files Attached Files
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  7. #7
    Join Date
    Feb 2012
    Posts
    8
    Its working perfectly sir, I am trying to incorporate the code to my original database. Thank you very much for your support

  8. #8
    Join Date
    Jan 2012
    Posts
    97
    Your welcome.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  9. #9
    Join Date
    Feb 2012
    Posts
    8
    Sir,

    Is it possible to hide the rows of the entire sheet containing database

    Please help me

    Regards

  10. #10
    Join Date
    Jan 2012
    Posts
    97
    You can just hide the entire worksheet if you'd prefer:

    To hide...

    Code:
    SheetName.Visible = xlSheetVeryHidden
    To unhide...

    Code:
    SheetName.Visible = True
    NB: If you hide your sheets using the code above it is not possible for users to unhide them using the menu option Format>Sheet>Unhide. Only using code will they be able to display the sheets again.

    You can alternatively hide the tabs at the bottom of the screen (users will still be able to access sheets by right clicking on the bottom right arrows). To do this...

    Tools>Options>View and uncheck Sheet Tabs check box

    To hide particular rows in an active sheet...

    Code:
    ActiveSheet.Rows("n:n").Hidden=True
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  11. #11
    Join Date
    Feb 2012
    Posts
    8
    sheet is not appearing in the workbook, if try to hide all the rows in the sheet and with above code if somebody enters their SMPID its showing wrong ID. without hiding rows its working perfectly. i can hide the sheet but i want to hide the rows containing data also. if somebody tries to unhide the sheet they can see the whole data, for that reason i tried to hide all the rows and protect the sheet.

    can you help me in this please sir

  12. #12
    Join Date
    Jan 2012
    Posts
    97
    I would suggest naming a range for the cells that contain the data...


    Insert -> Name -> Define


    Then to hide the rows in that range use...

    Code:
    Range("YourRange").EntireRow.Hidden = True
    Or to hide the columns in that range use ...

    Code:
    Range("YourRange").EntireColumn.Hidden = True
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  13. #13
    Join Date
    Feb 2012
    Posts
    8
    Sir,

    after define the data range how can i incorporate the same in the code given

    Regards

  14. #14
    Join Date
    Jan 2012
    Posts
    97
    Replace YourRange in the code below with the name of your range...

    Code:
    Range("YourRange").EntireRow.Hidden = True
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    store data in a database
    present in or analyse the data (if you need to) in a spreadsheet
    trying to store data in a spreadsheet, especailly if you have concerns over data privacy (or perhaps more likely legal issues of data privacy) is asking for trouble

    don't do it...
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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