creation of message box as per mentioned condition

**ss_bb_24** · 11-19-2008, 12:48 AM

I have attached one excel sheet. I have programmed this in such a way that it always asks the name of the person who opens this excel. Then the name & date with time autometically noted. I have used password "007" in this excel sheet.The code is below:-

Please Login or Register  to view this content.

Afterwards, I have used the below programme for command button. It shows that if anyone click this button then the name & date with time will be noted in the "approved by" .

Please Login or Register  to view this content.

Now I want a message box which should be appreared whenever we open this workbook & it should ask that whether "is it for new project?" we should select "yes" or "no". If "no" then E2 & F2 cells should be blank. If "yes" then it should follow as mentioned command button. This sheet is password protected "007".

I have given the screenshort of the sheet. Please help me.

**davesexcel** · 11-19-2008, 02:02 AM

Here's a similar thread using yes and no msgbox, also use excel vba help file on the topic
http://www.excelforum.com/excel-prog...yes-or-no.html

**ss_bb_24** · 11-19-2008, 02:37 AM

Thanks a lot for replying. I am unable to find correct code for my problem. I am not very good in VBA may be this could be a reson. Anyway,could you please make solution, please.

**ss_bb_24** · 11-19-2008, 04:35 AM

Can anyone help me.Hope I have clearly explained my problem.

**royUK** · 11-19-2008, 04:48 AM

The screen shot is a complete waste of time. You have all the basic code, why not attempt to adapt it to your needs and then ask for help with your code. You'll never learn if you rely on us to do all the work.

**ss_bb_24** · 11-19-2008, 08:02 AM

Dear Roy, Thanks for encouraging me to give efforts. I tried here by below code. As usual the error is coming. The worksheet is attached.

Now can I ask your advice please.

Please Login or Register  to view this content.

**davesexcel** · 11-19-2008, 08:21 AM

A slight alteration will get the code to work

Please Login or Register  to view this content.

**royUK** · 11-19-2008, 08:47 AM

You don't need to clear the cells for a new project, just write over them

Please Login or Register  to view this content.

**ss_bb_24** · 11-19-2008, 11:31 PM

Dear both,

Thanks a lot to both of you. I have used the code of Davesexcel & its working fine. This is exactly what I was looking for. But in my actual sheet the F2cell is marged with G2 & H2. So It shows error. It shows runtime error "1004 & says "cant change part a marged cell". Its hightlighting in the code :-

Please Login or Register  to view this content.

.

Could you guide me how can I solve this problem?

Also, thanks to Royuk as he encouraged me to start giving efforts rather crying for others help.

I have attached the sheet in case above is not enough to explain the problem correctly.

**davesexcel** · 11-20-2008, 02:25 AM

Changing r range to

Please Login or Register  to view this content.

should work in this situation

**ss_bb_24** · 11-20-2008, 02:34 AM

Dear Dave, Thanks a lot. Its working but some other error is coming. Run time error 13- Types mismatch.

Actualy I have used one more code in the same sheet. The code is just to hide/unhide of 2 nos rows. The code is in below:-

Please Login or Register  to view this content.

Could you please tell me what is the solution? Its making me "MAD"

**ss_bb_24** · 11-20-2008, 03:17 AM

any advice/suggestion, please ???? I am still looking for the same.

**ss_bb_24** · 11-20-2008, 07:08 AM

Can anyone suggest me about this problem? I need to solve this. Is it very tough? Or my problem is not valid?

**davesexcel** · 11-20-2008, 08:33 AM

Could be the way you have addressed the target

Please Login or Register  to view this content.

**ss_bb_24** · 11-21-2008, 12:12 AM

Thanks a lot Dave. Yes, my code was not "Ok". Just one small doubt, when we select "yes" its a new project then E2 & F2 cell are getting cleared. But I have simply linked other cell in other sheets with this E2 & F2. As an example, A2 & B2 Cell of Sheet2

Please Login or Register  to view this content.

&

Please Login or Register  to view this content.

& similarly... Now when I select "yes" these linked cells are showing "0" & date & time "1/0/1900, 00:00".

Is there any solution?

**davesexcel** · 11-21-2008, 02:34 AM

Originally Posted by ss_bb_24

Thanks a lot Dave. Yes, my code was not "Ok". Just one small doubt, when we select "yes" its a new project then E2 & F2 cell are getting cleared. But I have simply linked other cell in other sheets with this E2 & F2. As an example, A2 & B2 Cell of Sheet2

Please Login or Register  to view this content.

&

Please Login or Register  to view this content.

& similarly... Now when I select "yes" these linked cells are showing "0" & date & time "1/0/1900, 00:00".

Is there any solution?

When you have a 0 in a cell formatted for date and time it will become 0 days and 0 hrs because that is how date works.
Todays date, 11/20/08 in fact is 39772 days from 0 hrs 1/0/1900 and 39772.25 would be 11/20/08 6:00 AM .25 being 1/4 of a day.
Enter =now() in a cell and you will get the present date and time, change that cell format to general and the number displayed is the number of days from zero hour, 1/0/1900, the decimals being a portion of a day that gives us time.
With that being said.

This if statement would work.

=IF(Sheet1!F2="","",Sheet1!F2)