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.

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?

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.

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.

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

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.

Its working perfectly sir, I am trying to incorporate the code to my original database. Thank you very much for your support

Your welcome.