Locking the Data

[prazad82]

I cannot upload a sample file, but will try to explain my situation as clearer as possible.


We are creating a randomizer in excel 2007 and it has three sheets. First sheet has the raw data. Second sheet has the randomizer (using macro) that randomly generate a value (from the first sheet). Third sheet captures the record generated in the second sheet by the randomizer.


But, the third sheet data is still editable. Is it possible to lock the data that is (or) can be generated in this sheet?


Let me know, if you need any further explanation.


Thanks in advance

 

[kaushik03]

Hi prazad82,


I am not very sure what exactly you are after..


Did you try this?


Worksheets("your third sheet name").Protect


If you are trying to lock / protect entire sheet then above piece of code would be fine. But let us know if your requirement is something different; specific to certain Col /Rows..


Kaushik

 

[sgmpatnaik]

@prazad82


Hi


if you want to Protect the Third Sheet then you can use this code


right click on the Third sheet and select view code and paste the below code


Private Sub Worksheet_Activate()

Protect("password")

End Sub


with the above code when you visit the Third sheet then it is protected and no chance to Edit


Hope it will solve your problem other wise please give some more details


Thanks


SP

 

[prazad82]

Hello Kaushik and SP,


Thank you for understanding my situation and I appreciate your valuable help. I am waiting to get back to my office so that I can try your suggestions.


Earlier, we tried to lock the third sheet by right clicking on it and selected "Protect Sheet". But while running the Macro it showed an error. So if I use any of the codes above, will I be able to run the macro in the second sheet without any problems?


Bcoz, the randomizer is designed to generate a value and the same will be updated in the third sheet.


I will write back with the outcome of your suggestions, once I get back to office. thanks

 

[sgmpatnaik]

@prazad82


Hi


ok, if any problem please inform us with sample WorkBook


with that we can easily solve your problem


Thanks


SP

 

[prazad82]

Hello,


I tried both of your suggestions, but it is giving an error while running the macro.


So I managed to get a sample file where I removed the macro that was already there (so that we can start fresh). I have mentioned the requirements in each of the sheets.


http://www.2shared.com/file/xMkeuaNe/Randomizer1.html


Let me know if you need further information.


Thanks in advance

 

[sgmpatnaik]

@PRAZAD


Hi


you specified the request id will come in randomized what do you mean by random i think it will base some of the cells from the data sheet


2. assigned first name will match with request ID is it correct


sorry i cant understand properly


is it possible to post the code which is you used


Thanks


SP

 

[prazad82]

1. The request ID is the customer reference number. We will have to create a randomizer so that we get to monitor the requestID randomly. The randomizer basically pulls a random request ID from the data.


"2.assigned first name will match with request ID is it correct" - Yes.


I tried to upload the file with macro, but some error caused the file inaccessible


Thanks.

 

[sgmpatnaik]

@prazad


Hi


Thanks for your information and i will try to help you as my better and one more advise jut a blind shot that is


put the code before your macro


sheets("Capture").Unprotect Password:="password" 'replace the original password


and before the End Sub


sheets("Capture").Protect Password:="password" ' replace the original password


did you try the file with Drop Box it is better to upload all types of files


and try this file


http://www.2shared.com/file/EUMAVQCR/Randomizer1.html


Thanks


SP

 

[prazad82]

Hello SP,


I am so glad to see my initial request (locking the data) is working perfectly


Since, my macro file doesn't work (file not accessible), I request some more help in setting this randomizer completely.


In the "randomizer" sheet, the 'requestID' and 'assignedFirstName' fields needs to be generated automatically when we click on the "Randomizer" button. This randomly generated data should be copy-pasted in the "Capture" sheet as well (this part is working in the file that you have sent).


OR


If I get to access my original file with the Macros (that I have set), how will I incorporate the "Locking the 'Capture' Sheet" (which you have sent) macro in my file?


I hope to hear from you soon!!!

 

[sgmpatnaik]

@prazad


Hi


Glad i could solve your problem


in data sheet add the Sl. No. before the RequestID say it's coming from 1 to 28


now write the formula in randomizer sheet $i$3


=vlookup(randbeetween(1,28),'Data'!$a$2:$b$29,2)


it will randomly generate the numbers when you press the random Button


please check the file


http://www.2shared.com/file/mINWHkYN/Randomizer1.html


2. can you write the code here which set the macro in your original file with that i can assist you


Thanks


SP

 

[prazad82]

Hey,


We are almost there! This is working as I wanted


I have a few more questions.


1. We have a validation in the "Randomizer" with list NA, NRN and NM ( these are the types of monitoring). When I generate a requestID, I should be able to choose the type of monitoring manually. But when I do so, it changes the requestID and assignedFirstName. Actually, it should not change at all when we select the list in there.


2. I have provided only 28 line items in the sample, but in reality there will be more than 2000 rows. So how do I go from there?


You have done an excellent job in helping me. I was able to learn so many things in this session.


Thank you


Prasad

 

[sgmpatnaik]

@Prasad


Sure i will do for your 1st request tom because it's too late


and your 2nd request you should change the $a$2:$b$2000 that's all


about your 1st question why not you mention the NA,NRN etc in the data sheet it is also generate with the Request ID


Thanks


SP

 

[prazad82]

Sure, I will wait for your response.


NA, NRN cannot be included in the data sheet. We will have to do that manually. Coz, once a requestID generates we will check whether that sample can be audited. Based on that we will have to choose from the list in the validation (for eg: if the requestID can be audited, we will choose NA).


Thanks