Results 1 to 8 of 8

Thread: How to populate a cell[s] by clicking on another cell

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to populate a cell[s] by clicking on another cell

    Hi all,

    1st post here. I want to cells to populate with "Y" when someone clicks on another cell in a worksheet. Is this possible within excel? If so, could someone assist.

    Example, someone clicks on cell A1 then cells A2, A3, A4, A5 etc. are populated with Y.

    Thanks.

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,590
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    9 Thread(s)

    Default Re: How to populate a cell[s] by clicking on another cell

    Quote Originally Posted by liemaj View Post
    Hi all,

    1st post here. I want to cells to populate with "Y" when someone clicks on another cell in a worksheet. Is this possible within excel? If so, could someone assist.

    Example, someone clicks on cell A1 then cells A2, A3, A4, A5 etc. are populated with Y.

    Thanks.
    Try this sheet event code.

    To install sheet code:
    1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
    2. Copy the code below from your browser window and paste it into the white space in the VBE window.
    3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
    4. Make sure you have enabled macros whenever you open the file or the code will not run.
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Range("A2:A5").Value = "Y"
    End If
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: How to populate a cell[s] by clicking on another cell

    Quote Originally Posted by JoeMo View Post
    Try this sheet event code.

    To install sheet code:
    1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
    2. Copy the code below from your browser window and paste it into the white space in the VBE window.
    3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
    4. Make sure you have enabled macros whenever you open the file or the code will not run.
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Range("A2:A5").Value = "Y"
    End If
    End Sub
    This works, thank you very much!

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,590
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    9 Thread(s)

    Default Re: How to populate a cell[s] by clicking on another cell

    Quote Originally Posted by liemaj View Post
    This works, thank you very much!
    You are welcome - thanks for the reply.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to populate a cell[s] by clicking on another cell

    Quote Originally Posted by JoeMo View Post
    You are welcome - thanks for the reply.
    Hello! Me again, so i've been playing around with your code. I was hoping I could easily figure out how to do this, but it's proving harder than I envisaged.

    So I basically want to be able to replicate the code above for multiple columns in a worksheet i.e. if I click A1, populate A2, A3, A4 etc with Y, if I click B1 populate B2, B3, B4 etc.

    Apologies if this is basic, but i am very, very new to VBE.

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: How to populate a cell[s] by clicking on another cell

    The following code will update rows 2-5 in any column with "Y" whenever a single column in row 1 is selected:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Rows("1:1")) Is Nothing Then
        Range(Cells(2, Target.Column), Cells(5, Target.Column)).Value = "Y"
    End If
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to populate a cell[s] by clicking on another cell

    Quote Originally Posted by Joe4 View Post
    The following code will update rows 2-5 in any column with "Y" whenever a single column in row 1 is selected:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Rows("1:1")) Is Nothing Then
        Range(Cells(2, Target.Column), Cells(5, Target.Column)).Value = "Y"
    End If
    End Sub
    Yup, this works! Thanks for the quick response.

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: How to populate a cell[s] by clicking on another cell

    You are welcome.
    Glad us "Joes" were able to help!
    Last edited by Joe4; Oct 23rd, 2019 at 04:22 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •