Copy active cell value to specific cell using click event

    Good day all.

    I am trying to put a value from a specific range of values to a target cell by simply clicking one of the specific range of cells, i.e.

    C2- Bill
    C3- Bob
    C4- Jim
    C5- Mike
    C6- Steve

    I want to Click one of these cells and have the value in that cell show up in Cell A1

    So clicking on C2 would add the value Bill to A1, then if i click C6 it would change the value in A1 to Steve etc.

    Re: How to put a value in a cell by clicking a specified range fo cells


    Hows it going zelle132007.

    This is my first post back after a long time away, but I would like to get back to answering questions and following up with these forums.

    Down to business.

    Rule #2
    "Always use a relevant keyword thread title that titles what you are trying to do! NEVER use thread titles for what you THINK you want. You are posting BECAUSE you don't know."

    What are you trying to do here?

    I understand that you want to place certain values in certain cells quickly. Sounds like this might be a job schedual or time table of some sort, but if your asking the question it also may seem that your dealing with many, many fields to fill in.

    Can you give us some more information about the project?

    My first thoughts are these:
    1.) If excel did have the functionality to do what you are saying, wouldn't this translate into alot of clicking for you?

    2.) Would selecting the value you want, and the simple letting your hands do some work (AKA Crtl+C --> Ctrl+P) after clicking the cell be too much work?

    Cheers,
    Hat

    Re: How to put a value in a cell by clicking a specified range fo cells


    This will do what you want if you're happy to double-click the cell:-

    Code
    Option Explicit
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
  If Not Intersect(Target, Range("C2:C6")) Is Nothing Then
    Range("A1") = Target.Value
  End If
 
End Sub


    It needs to go in the module for the sheet: hit Alt-F11, double-click the sheet name in the VBA project and paste the code in.

    Any good?

    Re: How to put a value in a cell by clicking a specified range fo cells


    Code works perfectly to fit the description of his Request. Still unsure as to what use it will have. O.o


    Cheers,
    Hat

    Re: How to put a value in a cell by clicking a specified range fo cells


    Well that's going on the facebook right now. Brilliant! lol


    Cheers,
    Hat

    Re: Copy active cell value to specific cell using click event


    Here is a needful mod to Ruddles code. In order to prevent going into Edit Mode on the double-click you must cancel the double-click.


    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
  If Not Intersect(Target, Range("C2:C6")) Is Nothing Then
    Cancel = True
    Range("A1") = Target.Value
  End If
 
End Sub

    Re: Copy active cell value to specific cell using click event


    Code
    Cancel = True


    You have no idea how much time I've spent trying to achieve that! Now please excuse me whilst I go and edit a dozen or so worksheets...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login