copy of some cell details to other sheet

**ss_bb_24** · 11-27-2008, 01:08 AM

Dear friends, Thanks to all for guiding & helping to each other. I need a small help. My worksheet is attached. It asks the name whenever we will open this workbook. The same would be written autometically in open by cell with date & time.Then it ask "whether its new project or not"? If yes then then approved by cell & date-time would be blanks or if no then it will show the last approver's name, date & time. I need whenever anyone approve this in case of NOT NEW Project so the name of the approver & Date & time should be recorded in the another sheet name "record" along with the sr. nos. But the record will be blank whenever we will select "yes" in "whether its a new project". I want to make it password protected & password is "007".

I really cant get any clue of code. I cant find any suitable title for this post.The title what I have given is not exactly I am looking for. Even my above is more complecated than the simple title of the post.

please help.

**royUK** · 11-27-2008, 03:03 AM

You have already sked a similar question and received code that you ar using. You only need to adpt that code to write the what you want to a dofferent shet. Try to help yourself or you will never learn.

**ss_bb_24** · 11-27-2008, 03:29 AM

Dear roy, Yes, I have already thanks to the members of this forum. I often get help from all of you. But unfortunately, there are new new requarement are coming from my seniors in my workplace. This is one of the same. I really dont have any clue. If you can give little bit, then I can atleast try. Please dont be so rude.

**royUK** · 11-27-2008, 03:42 AM

I'm not being rude, I said you have the basis of the code yo merely need to adapt it to your new requirements.

**ss_bb_24** · 11-27-2008, 05:56 AM

Well!! I keep giving efforts myself even I am clueless. Anyway, I am still looking for help from anyone from this forum.

Thanks in advance.

best regards

**ss_bb_24** · 12-08-2008, 07:52 AM

I am unable to find any suitable solution so I still looking help from anyone. Could I request to all who will see this thread kindly help me to find solution if its known to you. It seems its very tough to me.

**royUK** · 12-08-2008, 08:50 AM

I'm not sure exactly what you want, but maybe this

Please Login or Register  to view this content.

**ss_bb_24** · 12-09-2008, 05:32 AM

I'm not sure exactly what you want, but maybe this

I hope you are in the right assumption. I applied the same code in the excel which is attached here. It has not effect anything so I cant understand whether you are the same way what is my requarement or not.

I mainly need the the name & date with time will be autometically written in the sheet name "record" whenever anyone press the "approved" button. It will keep recording the same. As an example if Mr. "X" press the "approved button so it will show the name of him & date with time. Similarly if I will press the approved button again it will show my name with date & time (of approving) just after "X" name. BUT NOTHING WILL SHOW IN CASE WE WILL SELECT ITS A NEW PROJECT DURING OPENING OF THIS EXCEL SHEET.

Then it ask "whether its new project or not"? If yes then then approved by cell & date-time would be blanks or if no then it will show the last approver's name, date & time. I need whenever anyone approve this in case of NOT NEW Project so the name of the approver & Date & time should be recorded in the another sheet name "record" along with the sr. nos. But the record will be blank whenever we will select "yes" in "whether its a new project".

Thanks to you again & again ...cheers!!!!!

**ss_bb_24** · 12-09-2008, 07:25 AM

hi roy,

the code is not working....could you pls have a look !!!!!!!!!!

I have attached the sheet with your given code. you can see its not working as nothing is being written when we press "approved" button.

**royUK** · 12-09-2008, 07:32 AM

Originally Posted by ss_bb_24

hi roy,

the code is not working....could you pls have a look !!!!!!!!!!

I have attached the sheet with your given code. you can see its not working as nothing is being written when we press "approved" button.

What isn't working? What is it not doing that you want it to?

Try attaching the workbook that you have added the code to with a clear explanation of what you want

**ss_bb_24** · 12-09-2008, 08:13 AM

I think I was unable to explain what is needed by me. Anyway, let me try again:-

The workbook is attached in my previous post with your code. Its already "OK" except only one matter. There is a "approved" button in the "sheet1", if anyone press this approved button the name of the approver & date & time will come in E2 & F2 cell of Sheet1. I want the same thing should be appreared in the B (B2)& C (C2)column of "record' sheet. This should continue again when another person will approve this again. This time the details should come in the B3,C3 of "record" sheet. My main intension to do this is make note of who does the "APPROVE" & name with date & time. But these data in "record" sheet will be cleared when we select "YES" during pop-up window appear (Is it the new project?") at the time of opening this file.

Please let me know if again its hard to understand...

thanks in advance

**royUK** · 12-09-2008, 08:30 AM

Does this do what you want?

**ss_bb_24** · 12-09-2008, 11:22 PM

Dear roy,
1st let me convey my thanks to you for spending your valuable time. I am really happy to say that its best excel forum I have ever seen. Whenever I post any question/problem, experts solved that & guided me fast. I appreciate this & proud to be a member of this forum.

Now related about my problem, the file attached in your last reply is NOT WORKING. Its giving error for pressing "approved" button & says "COMPILING ERROR, VARIABLE NOT FOUND". So I cant check whether this is my requairement unless the code works properly. But one more point I should say that when we select "NO" during opening of pop-up window (when opening of this workbook) contains "IS IT NEW PROJECT?", the datas (datas in sr. no, approved by & date & time columns) in the "record" sheet should be cleared which is not happening.

Hope I shall be able to get solution from you soon. Thanks in advance.

**royUK** · 12-10-2008, 04:38 AM

Change the button code to this

Please Login or Register  to view this content.

**ss_bb_24** · 12-10-2008, 08:18 AM

Thanks a roy.

I tried all the way to make understand what exactly I want. But its not the same needed by me. Let me tell my requairement again:-

1) whenever anyone open the file it will ask the name of the person in a pop up window. onece you give name the same sould come B2 & D2 :- Its done

2) Then the another window come. "Is it new project?" If "yes then E2 & F2 will be void & the details in "record" sheet also will be void. If no then it will show the last entry(E2 & F2) & record sheet will show all the persons who pressed approved button for selecting the "NO" in the question of "IS IT NEW PROJECTS?"

3) Now the button "approved". Anyone press this button , the details will be written E2 & F2 autometically. & the same will be written in "record" sheet.If again someone open the file & choose "NO" for the question of "IS IT NEW PROJECT" & press "approved" button then his/her(who opens now) details will come in E2 & F2 & the same will be written in "record" sheet.

4) In your code whenever I press "approved" button its asking name again.It should not ask name rather put the same name which I fed during opening the file into E2 & F2 cells.

Hope this time I have explained the outmost way.

plz revert back in case its not clear.

**royUK** · 12-10-2008, 08:20 AM

It cannot store the name like that, you would need to get it from the sheet

**ss_bb_24** · 12-11-2008, 01:33 AM

It cannot store the name like that, you would need to get it from the sheet

Why cant it show the names with date & time whosever press the "approved" button before unless it is NOT the NEW PROJECT. In case of new project lets show no datas in "record" sheet.

I am still doubt whether I could able to make it clear or not.

**royUK** · 12-11-2008, 04:22 AM

Where would the button get the name from?

**ss_bb_24** · 12-11-2008, 04:43 AM

Where would the button get the name from?

The button will get the same name what we fed during opening the file.

As an example, if I open that file feeding the name "ss_bb_24" in the input box (& selecting the "YES" in "IS IT NEW PROJECT"during opening the file. Then it will show the "ss_bb_24" & today's date & time in B2 & D2 cell of "sheet1" sheet. Now if I press the "approved" button then it should write my name,date (date when I press button) & time details(when I press button) in the E2 & F2 cell. The same would be noted in B2 & C2 cells of "record" sheet & then I saved it.

Now if you open the file again by giving "roy" name. You will have two option

1) If you select "yes' for the question "IS IT NEW PROJECT?" then the E2 & F2 cells in "sheet1" will be blank & "B" column & "C" column of "record" sheet also will be blanks. Now you can press "approved" button or not. If you press then again the same activities in the previous paragraph would be repeated."Roy" would be appeared in E2, & date with time details in F2 cells of "sheet1" & the same will be appeared in B2,C2 cells of "record" sheets.

2) If you select "no" for the question "IS IT NEW PROJECT?" then E2 & F2 cells in "sheet1" will show previous person details (ss_bb_24) who pressed "approved" button earlier. Now if you press the "approved" button then your name will show in the E2 & F2 buttons in "sheet1" & the same will come in B3 & C3 cells in "record" sheet.

Hope its possible now.

**royUK** · 12-11-2008, 06:37 AM

When you open the file the variable ans is used to to get the name, this variable will not have a value when the button is clicked

**royUK** · 12-11-2008, 06:46 AM

I have changed ans to a global variable

**ss_bb_24** · 12-11-2008, 06:58 AM

Thanks a lot roy. Its ok but except one matter. The datas (B2 & C2 & B3, C3,B4 & C4 etc cells) in "record" sheet must be cleared when we select "yes" against the answer of "IS IT NEW PROJECTS" during opening of file in the pop-up input window. This is not happening here. Could you please fix it.

I am sure it would not be tough for you as this problem is almost get solved except this point.

**royUK** · 12-11-2008, 07:20 AM

Change the code to this

Please Login or Register  to view this content.

**ss_bb_24** · 12-11-2008, 07:31 AM

Change the code to this

I have changed your latest given code into my "This workbook" project. But its giving error. It says -"compiling error, case without select case".
what to do?

One more point, why its asking name whenever we press "approved" button. The excel file which you attached in your 21 no reply was working nicely. It did not ask the name again during pressing the "approved" button. We must give the name during opening the file then why it should ask again the same even we press the "approved" button as many times as possible.

**royUK** · 12-12-2008, 04:49 AM

There was a missing line

Please Login or Register  to view this content.

**ss_bb_24** · 12-17-2008, 03:05 AM

This is not the thing what exact I was looking for. Anyway, I explained many times about the desired output & assume this the best possible code from your end.

I will look forward to someone else who can send me the code what exactly mentioned before. My previous post are carrying details about the desired output. Untill then I dont want to mark this thread as "SOLVED".

Thanks.

**royUK** · 12-17-2008, 04:44 AM

I've spent a lot of time trying to follow what you want, if it is not exactly right, try understanding the code & changing to suit yourself!

**ss_bb_24** · 12-19-2008, 06:54 AM

To roy:-Thanks a lot Roy for spending your time for contributing here. I have got the best possible code from your end so I dont expect any more in this thread. Thanks again to you.

To others:- I am giving my requirement again by systemetic wise & I have asked some more peaple in this forum to help me.

My requairement:-
1) A pop up window will be opend during opening this file.
2) we should feed our name else it will not open the file. The same name will be copied in B2 & D2 of Sheet1.
3) Also one more pop up window will be opened asking "is it new project?"
4) If "yes" then all the data entries in "record" sheet would be blank if "no" then nothing change.
5) If anyone press "Approved" button then his name (B2 cell) & Date with time of pressing "Approved" button will will be copied in "B" & "C" columns in "record" sheet.
6) All sheets should be password protected of "007".

A) Except point no. 4 all the points are working in the attached sheet.
B)But one more problem is happening here as:- When we select 'NO' against the question in pop-up window "IS IT NEW PROJECT?" then the data of B2 cell of sheet1 & date with time are getting copied in "record". Its not needed.

Thanks to all.

**rylo** · 12-31-2008, 04:52 PM

Hi

Try commenting out the actions for case vbno in the workbook open event. That way nothing will action when you select no. Is that what you want it to do?

rylo

**ss_bb_24** · 01-05-2009, 05:55 AM

thanks to Rylo to keep my thread "LIVE". It must be unless perfect solution is coming from various exparts.We cant just leave without solving any problem.

YES. You are right. When we select "no" (see in my last post in the point no 4) then nothing should be changed.

Please let me know if more explanations are needed.