|re: In each row from 21 down to 78, I have combined the cells of Column M & N|
Just FYI...when cells are merged, VBA (and Excel) typically sees the merged range as a single cell. In other words, with M21:N21 merged, the value is actually stored in M21 and N21 is empty. Therefore, the code only needs to check M21:M78 for a "Yes".
The following code seems to accomplish what you've asked for.
As written, it is very basic. I have not assigned a Type to the InputBoxes, which is something you might want to consider. By using the Type argument, you can ensure that a user only enters Text in the proj_Desc InputBox, a Date in the strt_Date InputBox, etc. Since I don't know the format of your inputs, I didn't include a Type.
You may also want to include code to ensure that things like the Dates are valid. In other words, you may want to ensure that a user can't enter a date from the Jurassic time period as a Start Date. ;-) There are a number of things you might want to add to make the code more user-proof, but I hope that this will get you started.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim proj_Desc, proj_Val, strt_Date, nxt_Rw
'Determine if change was made to a singe cell in
'the desired range and if the choice was Yes
If Target.Cells.Count = 1 And _
Not Intersect(Target, Range("M21:M78")) Is Nothing And _
Target = "Yes" Then
'Get input from user, Exit if cancelled
proj_Desc = Application.InputBox("Enter Project Description", "Project Description")
If proj_Desc = False Then Exit Sub
proj_Val = Application.InputBox("Enter Project Value", "Project Value")
If proj_Val = False Then Exit Sub
strt_Date = Application.InputBox("Enter Start Date", "Start Date")
If strt_Date = False Then Exit Sub
'Determine next Available Row in Sheet2
nxt_Rw = Sheets(2).Range("J" & Rows.Count).End(xlUp).Row + 1
'Place input in Columns J:L
Sheets(2).Range("J" & nxt_Rw) = proj_Desc
Sheets(2).Range("K" & nxt_Rw) = proj_Val
Sheets(2).Range("L" & nxt_Rw) = strt_Date
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.