1

I am trying to capture a click event in a cell by editing the VBA code in one of my xlsm worksheets. For a sanity check, I am merely trying to turn whatever cell is clicked red. Here is my VBA code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'prevent Select event triggering again when we extend the selection below
    Application.EnableEvents = False
    Target.Interior.Color = vbRed
    Application.EnableEvents = True
End Sub

After saving the code, and going back to my worksheet I click on a cell but it doesn't work. I did get this to work in the past at one point, and I'm not sure what I did differently now, but I can't debug the issue.

I'm not sure if this matters, but when I go into the VisualBasic editor, there are multiple projects on the side. I'm only editing the code for a worksheet for the file I'm using, but I can't seem to remove the other project.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
TJB
  • 3,706
  • 9
  • 51
  • 102
  • Please define *"doesn't work"*. Also, reboot your PC. (since there are projects you can't identify, I get the feeling Excel's been running for a lonng time.) – ashleedawg Jun 10 '18 at 20:14
  • When I click on a cell, I expect that cell to turn red, however nothing happens. I even tried to add a debug spot in the code, and when I click on a cell it seems the sub routine isn't even ever triggered. – TJB Jun 10 '18 at 20:16
  • Where is the code located? Right click the worksheet tab (at the bottom of the screen) and click "View Code". Is *that* where it's located? – ashleedawg Jun 10 '18 at 20:18
  • 1
    Restarted my pc, and it worked ! Thanks ashleedawg, I'm a Python developer, and I believe this it the one and only time I will ever have to work with Visual Basic in Excel so I was starting to lose my mind hah. – TJB Jun 10 '18 at 20:26
  • 1
    Also yes, I have been getting the the VBA project by clicking 'View Code' on the worksheet tab. – TJB Jun 10 '18 at 20:27
  • In my opinion, people reboot far less often than they should. I figure it's best to reboot every 4 hours that Windows has been running, and I notice a difference in performance. – ashleedawg Jun 10 '18 at 20:31

1 Answers1

1

The code looks fine (assuming you want to change make every cell you touch turn red), so it must be located in the wrong place.

  1. Reboot your machine. (humour me)

  2. Open Excel and create a new workbook.

  3. Right click the worksheet's tab (at the bottom of the screen) and click View Code.

  4. Paste your code into the worksheet's module that opens.

  5. Click the worksheet.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105