creation of message box as per mentioned condition

[ss_bb_24]

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:-

Private Sub Workbook_Open()
    'Prompt for name
    Dim Ans As Variant
    Ans = InputBox("Please enter your name.")
 
    'Close the file if no name entered
    If Ans = "" Then ThisWorkbook.Close False
  Sheets("Main Sheet").Unprotect Password:="007"
    'Enter name and date in the sheet
    With Worksheets("Main Sheet")
        .Range("B2") = Ans
        .Range("C2") = Format(Now, "m/d/yyyy h:mm")
    End With
    Sheets("Main Sheet").Protect Password:="007"
End Sub

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" .

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="007"
Range("E2") = Range("B2")
Range("F2") = Format(Now, "m/d/yyyy h:mm")
 ActiveSheet.Protect Password:="007"
End Sub

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]

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]

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]

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

[royUK]

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]

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.

Private Sub Workbook_Open()
    'Prompt for name
    Dim Ans As Variant
    Ans = InputBox("Please enter your name.")

    'Close the file if no name entered
    If Ans = "" Then ThisWorkbook.Close False
  Sheets("Sheet1").Unprotect Password:="007"
    'Enter name and date in the sheet
    With Worksheets("Sheet1")
        .Range("B2") = Ans
        .Range("D2") = Format(Now, "m/d/yyyy h:mm")
    End With
    Sheets("Sheet1").Protect Password:="007"


Dim msg As String
Dim title As String
Dim response As String

msg = "Is it new project?"
response = MsgBox(msg, vbYesNo)
If response = vbYes Then E2 = " " & F2 = " "

  Elseif
  No Change
End If

End Sub

[davesexcel]

A slight alteration will get the code to work

Private Sub Workbook_Open()
'Prompt for name
    Dim Ans As Variant
    Dim msg As String
    Dim title As String
    Dim response As String
    Dim r As Range
    Set r = Range("E2:F2")

    Ans = InputBox("Please enter your name.")

    'Close the file if no name entered
    If Ans = "" Then
        ThisWorkbook.Close False
    End If

    Sheets("Sheet1").Unprotect Password:="007"
    'Enter name and date in the sheet
    With Worksheets("Sheet1")
        .Range("B2") = Ans
        .Range("D2") = Format(Now, "m/d/yyyy h:mm")
    End With


    msg = "Is it new project?"
    response = MsgBox(msg, vbYesNo)
    If response = vbYes Then
        r.ClearContents
    End If
    Sheets("Sheet1").Protect Password:="007"

End Sub

[royUK]

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

Option Explicit

Private Sub Workbook_Open()
    Dim msg    As String
    Dim title  As String
    Dim ans    As String

    'Prompt for name

    ans = InputBox("Please enter your name.")

    'Close the file if no name entered
    If ans = "" Then ThisWorkbook.Close False
    msg = "Is it new project?"
    ans = MsgBox(msg, vbYesNo)
    If ans = vbYes Then
        With Sheets("Sheet1")
            .Unprotect Password:="007"
            'Enter name and date in the sheet
            .Range("B2") = ans
            .Range("D2") = Format(Now, "m/d/yyyy h:mm")
            .Protect Password:="007"
        End With
    End If

End Sub

[ss_bb_24]

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 :-

 r.ClearContents

.

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.

[ss_bb_24]

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

[davesexcel]

Could be the way you have addressed the target

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$C$39" Then

        ActiveSheet.Unprotect Password:="007"
        Select Case LCase(Target.Value)
        Case "yes"
            Rows("40:41").EntireRow.Hidden = True
        Case "no"
            Rows("40:41").EntireRow.Hidden = False
        End Select
        ActiveSheet.Protect Password:="007"

    End If

End Sub

[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

=Sheet1!E2

&

=Sheet1F2

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

Is there any solution?

[davesexcel]

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

=Sheet1!E2

&

=Sheet1F2

& 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)

[ss_bb_24]

thanks dave!!!!!!