Microsoft Office Forums - View Single Post - [Solved] Carbon Footprint Calculator problem

excelledsoftware · 06-02-2015, 05:30 PM

Ok This attempts to set up the range of where the cursor can return a value and where it should be ignored. The line says Set Preview is where you change the preview cell in between the quotes.

You need to insert this code into the worksheet module. To do so open your workbook, press ALT + F11, on the left side of the new screen you will see VBAProject followed by your workbook name. Double click the sheet below that has the data and then paste the following code in.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Preview As Range, BlankCol As Long, BlankRow As Long
  Dim StartRow As Long, StartCol As Long
  
  Set Preview = Range("M1") 'Set the reference you want
  
  'Set other references
  StartRow = 2
  StartCol = 1
  'Auto set the boundaries
  BlankCol = Cells(StartRow, StartCol + 1).End(xlToRight).Column + 1
  BlankRow = Cells(StartRow, StartCol + 1).End(xlDown).Row + 1
  
  'Ensure you are in the right area
  If Target.Column < BlankCol And Target.Column > StartCol Then
    If Target.Row < BlankRow And Target.Row > StartRow Then
      Preview = Target.Value
    End If
  End If
 
End Sub

Let me know if you have any questions.

Thanks