PLEASE HELP--- Double Click Macro Troubleshooting

[Wildcats23]

Hello, I'm trying to figure out a Macro so that if I click (For Example) where it says "Double Click" it will copy the Student # in "A4"(in Red) to "C9" AND copy the Date in "C1" (in Green) to "C8". What I would like this to be able to do is, if i double click anywhere in "C2:E6" it will populate the bottom cells with the Date and student number. THANK YOU!!!

A	B	C	D	E
Student #	Name	Jan	Feb	March
12	Jack
21	Jill
32	Sarah	Double Click
42	Chris
51	Jen
	Date	Jan
	Student #	32

<tbody>
</tbody>

 

[mrshl9898]

Like this?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


Dim monthname As String
Dim studentno As Long


studentno = Cells(Selection.Row, 1)
monthname = Cells(1, Selection.Column)

Range("C9").Value = studentno
Range("C8").Value = monthname


End Sub

 

[My Aswer Is This]

You said:

populate the bottom cells



And what happens if you double click on two cells in the same column how would we enter two names in row 8 and 9
​

 

[My Aswer Is This]

Try this:
My script will do what you asked when you said this

What I would like this to be able to do is, if i double click anywhere in "C2:E6" it will populate the bottom cells with the Date and student number. THANK YOU!!!​

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C2:E6")) Is Nothing Then
'Modified  12/6/2018  9:45:41 PM  EST
Cancel = True
Dim c As Long
Dim r As Long
r = Target.Row
c = Target.Column
Cells(8, c).Value = Cells(1, c).Value
Cells(9, c).Value = Cells(r, 1).Value
End If
End Sub

 

[My Aswer Is This]

If you plan to choose more then one student in one month you will need to send second student number to Rows 10 and 11 and so on.

So you will need this code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C2:E6")) Is Nothing Then
'Modified  12/6/2018  10:48:41 PM  EST
Cancel = True
Dim Lastrow As Long
Dim c As Long
Dim r As Long
r = Target.Row
c = Target.Column
Lastrow = Cells(Rows.Count, c).End(xlUp).Row + 1
If Lastrow < 8 Then Lastrow = 8
Cells(Lastrow, c).Value = Cells(1, c).Value
Cells(Lastrow + 1, c).Value = Cells(r, 1).Value
End If
End Sub

 

[Wildcats23]

Thank you for your reply! The macro works great the only stipulation ( I apologize I didn't make this clear) is that I'm trying to figure out a way so if i click anywhere in the range it will populate only C8 & C9. Currently it populates the date and student # in the corresponding column below.

 

[My Aswer Is This]

Your original post said doubleclick

But now you said:

figure out a way so if i click anywhere

So is it click or double click
​

 

[My Aswer Is This]

So assuming you mean Double Click
Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C2:E6")) Is Nothing Then
'Modified  12/7/2018  1:35:15 PM  EST
Cancel = True
Dim c As Long
Dim r As Long
r = Target.Row
c = Target.Column
Cells(8, "C").Value = Cells(1, c).Value
Cells(9, "C").Value = Cells(r, 1).Value
End If
End Sub

 

[Wildcats23]

Yes I mean't doubleclick sorry, that worked perfectly thank you so much!!!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Yes I mean't doubleclick sorry, that worked perfectly thank you so much!!!