0

I need to get the name of the worksheet in which the user is currently editing cells. Activesheet.Name normally works, but if the user changes sheets while editing a cell I get problems.

Assume that the code below is running for 'Sheet1':

Private Sub Worksheet_Change(ByVal Target As Range) debug.print = ActiveSheet.Name End Sub

It triggers whenever a cell in that sheet changes. However, it is also possible that the user on 'Sheet1' starts to edit a cell and then (while still editing) clicks on 'Sheet2', thereby activating it. The code above will then print 'Sheet2'. Strictly speaking this may be correct since 'Sheet2' was activated. However, I still need to know what sheet the user was changing a cell in - and that was 'Sheet1', not 'Sheet2'! Anyone know how to do this? cheers, Mikael

Mikael Svalgaard
  • 1
  • Just curious. Why you need to know if a user *tried* to edit something? Anyways, the event `Workbook_SheetChange` wil trigger only if somethins *was edited* and return the sheet name of the worksheet edited https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.sheetchange – Foxfire And Burns And Burns Mar 10 '23 at 13:00
  • It's not just 'tried' - the cell contents actually *do* change if you edit something, don't press enter but instead click on another sheet. – Mikael Svalgaard Mar 10 '23 at 13:57
  • Then the event I posted is what you want. It triggers everytime a cell's content is edited :) but it triggers after editing has been confirmed (pressing enter or clicking on another sheet or whatever) – Foxfire And Burns And Burns Mar 10 '23 at 14:03
  • Perhaps check the Worksheet.Name off the Target passed to the Change event as that range is the range that was changed whereas ActiveSheet is the currently active sheet. – AndrewS Mar 10 '23 at 14:19
  • Thanks AndrewS - this works. Don't know how to mark this as the solution though? – Mikael Svalgaard Mar 12 '23 at 21:57

0 Answers0