Help on Double Click Event - Copy to Another Sheet

[USFengBULLS]

Hello all,

To begin here is the DropBox link to the exact work book I am talking about here
https://www.dropbox.com/sh/dunqarfm89unhbu/AABMkvpgW6HYXBrOry2qpXPTa?dl=0

I have a question Regarding the Worksheet double click event. If you go to Sheet "Finish List" Notice there are three items on that list in Column A,B,C. I want to write a code in the Worksheet Before double click event that if the user double clicks on a cell in that Description Column only (Column B) It will take that Description and copy it to sheet "NEW FORM-RESET" and put it in the Next blank row from E23:E27, It will take the corresponding MR CODE (Column A) and AC CODE (Column C) and place it in it's respective column B23:B27 (MR CODE) and D23:D27 (AC CODE).
Notice I only have 5 rows that this form will populate at a time i.e. A23:E27. Can there also be an error (or Message Box) that restricts the user to only be able to add 5 items at a time? Basically that when all 5 rows have been filled, it will not allow the user to fill up anymore. Any help would be greatly appreciated. I so close to finishing this little application. Thanks!

 

[DanteAmor]

Add the following code to the events on your sheet

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Target.Row < 11 Then Exit Sub
        If Target.Value = "" Then Exit Sub
        '
        Set h = Sheets("NEW FORM-RESET")
        i = 23
        Do While h.Cells(i, "E").Value <> ""
            i = i + 1
            If i > 27 Then
                MsgBox "No more data is allowed"
                Exit Sub
            End If
        Loop
        h.Cells(i, "E").Value = Target.Value
        h.Cells(i, "B").Value = Cells(Target.Row, "A").Value
        h.Cells(i, "D").Value = Cells(Target.Row, "C").Value
        MsgBox "Data copy"
    End If
End Sub

 

[USFengBULLS]

Dante, again, Thank you for your help with this. Great Learning experience I needed with worksheet events.
After studying your code, it's not as hard as I first made it out to be.

 

[DanteAmor]

Dante, again, Thank you for your help with this. Great Learning experience I needed with worksheet events.
After studying your code, it's not as hard as I first made it out to be.

It's because my code is very soft.

I'm glad to help you

 

[USFengBULLS]

@DanteAmor
I have this macro now making a copy of the first three cells in a row based off the revised status change and copying them down to the next blank row in same worksheet.
Dim erow As Double
erow = Sheets("DRAWING SCHEDULE").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
Range(Cells(tRow, 1), Cells(tRow, 3)).Copy Sheets("DRAWING SCHDULE").Cells(erow, 1)

This is working fine but I also need it to rename the contents in cell C Column with REV 1 at the end and increment it each time there is a duplicate in the same column by 1.
For instance if in Column C I originally have LEVEL 1/ AREA A/ RM 124 and the user selects Revise, It needs to copy down to the next blank cell which it is and then be renamed to this LEVEL 1/ AREA A/ RM 124 REV 1 then if that one get a status of revise then same thing, gets copied down and now renamed to LEVEL 1/ AREA A/ RM 124 REV 2.
Would youo be able tot help me with the renaming part in this code please? Thanks

 

[DanteAmor]

I think you should create a new thread

 

[USFengBULLS]

Sorry I did already earlier and no one is responding. Plus you are familiar with this and what i've been working on so I thought I'd ask you directly. Sorry for that.