1

I'm new with VB coding in Excel and would like to know how if I can click on a particular cell and have that cell loaded with the value of another cell with a single click. For example, if the value “SVE” is in cell AP1, how can I click on the blank cell B8 and have “SVE” loaded into B8 in a single click without typing the value “SVE” prior to clicking on the cell? Does it require a formula or does it have to be done with VBA code?

Tony Woods
  • 13
  • 3

1 Answers1

0

Populate Cell on Single Click

  • You need VBA to do this. Select Developer > Visual Basic to open the Visual Basic Editor. Open View > Project Explorer. Double-click on your project (VBAProject(YourWorkbookName.xlsm)) and double-click the worksheet (e.g. Sheet1(Data)) where you need the requirement. Into the newly opened sheet module window, copy/paste the following code:
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Range("B8"), Target) Is Nothing Then Exit Sub
    Target.Value = Range("AP1").Value
End Sub
  • It will actually only work when you select the cell i.e. the cell isn't selected and you click on it.
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Your solution worked perfectly. I modified my code to use a range instead of a single cell. Also changed A1 to A5. (See current code at below) That also worked perfectly. It does not allow me to overwrite an existing value on a cell that has already been populated. Is there a modification that can delete the existing value in one of the range cells and insert the new value if A5 is changed? Coding that would delete any existing value in a range cell before repopulating it would be ideal. If Intersect(Range("B8:R13"), Target) Is Nothing Then Exit Sub Target.Value = Range("A5").Value – Tony Woods Feb 27 '22 at 15:30
  • You best ask another question. Copy your code to it and explain, in more detail, what it's doing and what you want it to do. – VBasic2008 Feb 27 '22 at 20:06
  • Eighteen cells (B8 thru D13) are empty. I want to click on any one of these cells and have it filled with the value that is in cell A5. After filling any one of the 18 cells with the A5 value, I want to be able click on that cell later and overwrite that cell filling it with the A5 value again because A5 may have changed. – Tony Woods Feb 28 '22 at 04:49
  • Public Sub Worksheet_SelectionChange(ByVal Target As Range) If xfrActiveCell = "" Then xfrActiveCell = ActiveCell xfrLastCell = xfrActiveCell xfrActiveCell = ActiveCell Range("AQ1").Value = xfrLastCell Range("AQ2").Value = xfrActiveCell If xfrActiveCell = Range("AP1").Value Or xfrActiveCell = Range("AP2").Value Or xfrActiveCell = Range("AP3").Value Then gamewinner = xfrActiveCell End If Range("A5").Value = gamewinner If Intersect(Range("B8:D13"), Target) Is Nothing Then Exit Sub Target.Value = Range("a5").Value End Sub – Tony Woods Feb 28 '22 at 04:53
  • I copied the code from word, I don't know how to make it recognize line feeds so that it is in a more readable format – Tony Woods Feb 28 '22 at 05:02
  • I have solved my issue. Thanks – Tony Woods Mar 02 '22 at 03:27