How do I get a cursor to move back to where data was being input. and delay number of times it runs

[ghrek]

Hi

I have the following macro that runs everytime I put data in but it runs everytime I put data in and want it to only run like every 10 seconds and go back to where I put the last data in if that can be done.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Cells.Select
    With Selection.Font
        .Name = "Daytona"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "Daytona"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Selection.Font.Bold = False
    Selection.Font.Bold = True
    Selection.Font.Bold = False
    ActiveWorkbook.Save
End Sub

Can that be done?

 

[6StringJazzer]

it runs everytime I put data in and want it to only run like every 10 seconds

This code responds to a Change event, so that is why it runs every time you put data in.

You can set up a timer that will run a macro every 10 seconds. Is that what you mean?

Your code selects the entire sheet and makes format changes to all cells. You should just make changes to Cells instead of selecting them first. Then the cursor stays where it was and there is no need to "go back to where [you] put the last data."

 

[ghrek]

Yeah that would be it. How do I do that as can only write macros using the excel help

 

[6StringJazzer]

Your code is also redundant.

Put this code into a standard module (like Module1) and run the first sub to start the process.

Option Explicit

Dim RunWhen As Double

' Start the sequence
Public Sub StartTimer()
  
   SetNextTimer
   
End Sub

' This will be called repeatedly to set a periodic timer
Public Sub SetNextTimer()

   ' Convert seconds to fraction of a day
   RunWhen = Now + TimeSerial(0, 0, 10) ' timer goes off 10 seconds from now
   Application.OnTime _
      EarliestTime:=RunWhen, _
      Procedure:="FormatActiveSheet", _
      Schedule:=True

End Sub

' Cancel the last timer that was set
Public Sub StopTimer()

    On Error Resume Next
   
    Application.OnTime _
      EarliestTime:=RunWhen, _
      Procedure:="FormatActiveSheet", _
      Schedule:=False

End Sub

Private Sub FormatActiveSheet()
   
    With ActiveSheet.Cells.Font
          
        .Name = "Daytona"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
   
        .Bold = False
   
    End With
   
    ThisWorkbook.Save

End Sub

 

[6StringJazzer]

Note that I copied the functionality of your code, so it saves the workbook every 10 seconds.

I gave you the code to do what you are asking but honestly I can't see why you would want to reformat the sheet and save the file every 10 seconds.

 

[ghrek]

Many thanks

 

[6StringJazzer]

Also, this will happen every 10 seconds until you either call StopTimer or exit Excel.