1

I'm trying to run a macro that runs the following formula when a cell is clicked and populates it with the rounded figure:

= Round((A1 + "0:02") * 96, 0) / 96

This takes the time from A1 and rounds it to the nearest 15 mins.

The range of cells that can be clicked for this are D5:X46.

Ƭᴇcʜιᴇ007
  • 111,883
  • 19
  • 201
  • 268
Michael
  • 11
  • 3
  • 1
    Possible duplicate of [Excel 2010 VBA - Trigger macro with ONE click on specific cell](http://superuser.com/questions/663580/excel-2010-vba-trigger-macro-with-one-click-on-specific-cell) – Ƭᴇcʜιᴇ007 May 13 '16 at 13:59
  • Welcome to Super User! What exactly is your question? You've told us what you're trying to do, but you haven't described a problem. Please [edit](http://superuser.com/posts/1076512/edit) your question to indicate exactly what you need help with. – Excellll May 16 '16 at 16:41

1 Answers1

1

One way to achieve this is with a Worksheet_SelectionChange() Sub. Place this code into the sheet module that you wish to have this functionality in.

Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("D5:X64")) Is Nothing Then Exit Sub
    Target.Formula = "= Round(($A$1 + " & Chr(34) & "0:02" & Chr(34) & ") * 96, 0) / 96"
End Sub

The IF statement checks that the click has been on the desired range. You can click on multiple cells and they will all populate

https://msdn.microsoft.com/en-us/library/office/ff194470.aspx

CallumDA
  • 1,045
  • 8
  • 18