Macro upon mouse click - copy data from that cell to populate a 4x15 form on same worksheet

[Louis_Jr]

I have a worksheet that has 3 columns of numbers, each column having 26 rows (H2:H26, L2:L26 and P2:P26) and on the same worksheet a matrix which is 4 x 15 (4 cells in adjoining columns and 15 rows down B3:E17).

I would like a macro that would upon clicking a button, or clicking or doubleclick the mouse in a certain area such as the 4x15 matrix area or H2:H26, L2:L26 or P2:P26 execute a macro.
The user after executing the macro as describe above would click on one on the cells in column H2:H26, L2:L26 or P2:P26. When the first cell in (H2:H26, L2:L26 or P2:P26) is clicked or doubleclicked it would copy the cell contents from that cell into cell B3, upon the next click or doubleclick it would copy the cell contents from the cell clicked in column H2:H26, L2:L26 or P2:P26 into cell C3, ditto for D3, ditto for E3.
The next click or doubleclick would copy the cell contents from that cell in column H2:H26, L2:L26 or P2:P26 down one row in the matrix to B4, next C4, next D4, then E4.
Next down one row, copy the cell contents of the cell clicked to B5, C5, D5, E5
Down one row until all 15 rows are filled (last cell filled would be E17)
The macro would end when clicked outside of column H2:H26, L2:L26 or P2:P26.

I have seen some private macros with this type of functionality and have tried to modify them without any luck.

Thanks for your help
Lou

 

[My Aswer Is This]

It would be best to tell us what your ultimate goal is.
See it appears as if your wanting to tell us how the script should work. Why not tell us your ultimate goal and let us tell the script what to do to achieve the ultimate goal. Your plan as shown here would be very difficult way to do things.

 

[MickG]

Give this a try:-
This is an Event Code and will run each time a cell in any one of your 3 ranges is clicked.
Install code a follows:-

Right click Sheet TAB, Select "View Code" from Drop down.
VB window appears, Paste code into VB Window.
Close VbWndow

Private [COLOR=navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, R1 [COLOR=navy]As[/COLOR] Range, R2 [COLOR=navy]As[/COLOR] Range, R3 [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range("B3:F17")
[COLOR=navy]Set[/COLOR] R1 = Range("H2:H26")
[COLOR=navy]Set[/COLOR] R2 = Range("L2:L26")
[COLOR=navy]Set[/COLOR] R3 = Range("P2:P26")

[COLOR=navy]If[/COLOR] Not Intersect(Target, Union(R1, R2, R3)) [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Dn.Value = "" [COLOR=navy]Then[/COLOR]
        Dn.Value = Target
        [COLOR=navy]Exit[/COLOR] For
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Louis_Jr]

Mick,
The macro works perfectly. One enhancement I would like to make is to protect the sheet. I realize if a user clicks outside the ranges (H2:H26, L2:L26 or P2:P26) that the data isn’t entered. However I have many formulas on the worksheet and I wouldn’t want a user to inadvertently change a formula.
Once again thanks for your fast response and excellent code, it truly is appreciated.
Best,
Lou

 

[Louis_Jr]

Mick,
I rushed the email asking about protecting the workbook. The areas that would not be protected would be the 3 columns (H2:H26, L2:L26 or P2:P26) and the 4x15 form (B3:E17).
Sorry for any inconvenience or confusion that the previous email may have caused you.
Thanks again,
Best,
Lou

 

[MickG]

Try this:-
Change sheet name and Password to suit!!

[COLOR="Navy"]Sub[/COLOR] MG25Aug05
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Sheet4")
     [COLOR="Navy"]Set[/COLOR] Rng = Union(.Range("B3:F17"), .Range("H2:H26"), .Range("L2:L26"), Range("P2:P26"))
     Rng.Locked = False
     .Protect Password:="mick", UserInterfaceOnly:=True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Louis_Jr]

Mick,
Once again a timely response that works beautifully. I can’t thank you enough for your dedication to this community and your thoughtfulness to others.
May the good deeds you do for others come back to you in a special way.
Best,
Lou

 

[Louis_Jr]

I agree that I should have included a needs assessment in the request.
There is a list of users that changes based on their availability. Weekly these users are scheduled for an activity.
I have list of users with each user having a discreet number assigned to them. The row of each user contains their discreet number, last name, first name, several other cells of data specific to the user and the dates they are out and in . A formula calculates which users are available that week based on these dates and notes in their row an "X" or the word "In". This list is sorted and only the available users are presented on a separate worksheet that also contains a 4x15 form/matrix.
The monitor lists in the form/matrix the number of the 4 users who are scheduled for a time slot. There are 15 time slots, hence the 4x15 matrix. Once the numbers are manually entered into the matrix, the matrix is copied to another worksheet that produces several reports.
The process I wanted automated was for the monitor, rather than typing in the number for each user to populate the matrix, clicking on their number which was listed next to the name of each available user.
The macro that Mick G. wrote worked beautifully and does exactly as I asked.

 

[MickG]

You're very welcome and thank you for your kind response
Regrds Mick