Planilha que controla o tempo

Szoke

14-03-2014 22:55:33

Galera,

Sou novo aqui mas já acompanhei diversas discussões do Fórum que me ajudaram bastante.

Estou fazendo o seguinte uma planilha que controla o tempo em que estou trabalhando, e minha rotina é o seguinte, chegar ligar o Outlook trabalhar, e só fechar o Outlook na hora de ir embora. Essa parte funciona bem... e ela também esta funcionando muito bem sem dar problema, acontece que eu não consigo usar o Excel quando ela esta aberta rsrsrs na verdade se eu crio uma nova planilha ela funciona normalmente agora se eu abro uma planilha que ja existe ela trava e para de contar o tempo tem como resolver esse problema?

Segue o código dela abaixo

Código: Selecionar todos


Private Sub Workbook_Open()
Definir_Data_hora

TimerAtualizar
TimerDefinir2

End Sub


' Access the GetCursorPos function in user32.dll
      Declare PtrSafe Function GetCursorPos Lib "user32" _
      (lpPoint As POINTAPI) As Long
      ' Access the GetCursorPos function in user32.dll
      Declare PtrSafe Function SetCursorPos Lib "user32" _
      (ByVal x As Long, ByVal y As Long) As Long

      ' GetCursorPos requires a variable declared as a custom data type
      ' that will hold two integers, one for x value and one for y value
      Type POINTAPI
         X_Pos As Long
         Y_Pos As Long
      End Type

      ' Main routine to dimension variables, retrieve cursor position,
      ' and display coordinates
      Sub Get_Cursor_Pos()

      ' Dimension the variable that will hold the x and y cursor positions
      Dim Hold As POINTAPI

      ' Place the cursor positions in variable Hold
      GetCursorPos Hold
'ActiveWorkbook.Sheets("Marca").Select
      If Workbooks("Controle.xlsm").Sheets("Marca").Range("E2") = Hold.X_Pos Then
      
        If Workbooks("Controle.xlsm").Sheets("Marca").Range("F2") = Hold.Y_Pos Then
      
            If Workbooks("Controle.xlsm").Sheets("Marca").Range("G1").Value > Workbooks("Controle.xlsm").Sheets("Marca").Range("H1").Value Then
            
            Workbooks("Controle.xlsm").Sheets("Marca").Range("D1").FormulaR1C1 = "=now()- Today()"
            
            If Workbooks("Controle.xlsm").Sheets("Marca").Range("D1").Value > Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1").Value, 2).Value Then
            'Marca hora se cursor parado
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("H1"), 3).FormulaR1C1 = "=now()- Today()"
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("H1"), 3).Copy
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("H1"), 3).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
            
            Else
            
            'Se passar de meia noite ele marca nova linha pro novo dia
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("H1"), 3).FormulaR1C1 = "23:59:59"
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("H1"), 3).Copy
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("H1"), 3).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("F1"), 1).FormulaR1C1 = "=Today()"
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("F1"), 1).Copy
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("F1"), 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1"), 2).FormulaR1C1 = "=00:00:01"
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1"), 2).Copy
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1"), 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("H1"), 3).FormulaR1C1 = "=now()- Today()"
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("H1"), 3).Copy
            Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("H1"), 3).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
    
    End If
            
            
            
            End If
        
      
        End If
      
      End If
      

        Workbooks("Controle.xlsm").Sheets("Marca").Range("E2") = Hold.X_Pos
        Workbooks("Controle.xlsm").Sheets("Marca").Range("F2") = Hold.Y_Pos


      End Sub



Public Sub TimerDefinir()
Const sTempo As String = "00:10:00"
    Application.OnTime EarliestTime:=Now + TimeValue(sTempo), Procedure:="TimerAtualizar"
End Sub

Public Sub TimerAtualizar()
      

      ' Dimension the variable that will hold the x and y cursor positions
      Dim Hold As POINTAPI

      ' Place the cursor positions in variable Hold
      GetCursorPos Hold
       
    If Workbooks("Controle.xlsm").Sheets("Marca").Range("G1").Value = Workbooks("Controle.xlsm").Sheets("Marca").Range("H1").Value Then
    
    If Workbooks("Controle.xlsm").Sheets("Marca").Range("E2") <> Hold.X_Pos Or Workbooks("Controle.xlsm").Sheets("Marca").Range("F2") <> Hold.Y_Pos Then
   
    Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("F1"), 1).FormulaR1C1 = "=Today()"
    Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("F1"), 1).Copy
    Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("F1"), 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1"), 2).FormulaR1C1 = "=now()- Today()"
    Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1"), 2).Copy
    Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1"), 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
   Application.CutCopyMode = False
    End If
   
    End If
    
    Get_Cursor_Pos
    
    TimerDefinir
End Sub
Sub Definir_Data_hora()
'
' Definir_Data_hora Macro
'

'
'ActiveWorkbook.Sheets("Marca").Select
    Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("F1"), 1).FormulaR1C1 = "=Today()"
    Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("F1"), 1).Copy
    Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("F1"), 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
   Application.CutCopyMode = False
    Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1"), 2).FormulaR1C1 = "=now()- Today()"
    Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1"), 2).Copy
    Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1"), 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False
  
End Sub
Public Sub TimerDefinir2()
Const sTempo As String = "00:02:01"
    Application.OnTime EarliestTime:=Now + TimeValue(sTempo), Procedure:="TimerAtualizar2"
End Sub

Public Sub TimerAtualizar2()
Dim j As Integer
      
'Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1") - 1, 3).Select
Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1") - 1, 3).FormulaR1C1 = "=now()- Today()"
Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1") - 1, 3).Copy
Workbooks("Controle.xlsm").Sheets("Marca").Cells(Range("G1") - 1, 3).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

'ActiveWorkbook.Sheets("Marca").Select
j = Workbooks("Controle.xlsm").Sheets("Marca").Range("G1") - 1
Workbooks("Controle.xlsm").Sheets("Marca").Range(Cells(2, 1), Cells(j, 3)).Copy
'ActiveWorkbook.Sheets("Grava").Select
Workbooks("Controle.xlsm").Sheets("Grava").Cells(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
'ActiveWorkbook.Sheets("Marca").Select
    TimerDefinir2
'
ThisWorkbook.Save

End Sub


Agradeço a todos que tentarem entender a bagunça que eu fiz no código :D

Vlw Galera