- Joined
- May 6, 2011
- Messages
- 13
- Reaction score
- 0
Hi, I have a piece of code that is written in vba-module, I have timer event that I wish to execute the code, it should copy over data from cell A1 in the Data Entry sheet (sheet9) to Sheet1. It works for another workbook I have created with just 2 sheets (data entry and user sheet), but does not work for this workbook with multiple sheets (9 to be exact), it will not run the timer event it seems. Can someone review this code and suggest anything I might be doing wrong and supply me with code that will work please?
Cheers - the code I have written is below;
Public Sub Timer()
If Sheet9.Range("A1").Value <> "" Then
AcceptInput
Sheet9.Range("A1").Value = ""
End If
Application.OnTime Now + TimeValue("00:00:01"), "Timer"
End Sub
Private Sub AcceptInput()
Application.ScreenUpdating = False
Sheet1.Activate
If Not _
((ActiveCell.Column = 3 And ActiveCell.Row > 10 And ActiveCell.Row < 21) Or _
(ActiveCell.Column = 3 And ActiveCell.Row > 28 And ActiveCell.Row < 39)) Then
MsgBox "You cannot enter values into cell " & ActiveCell.Address, vbCritical
Exit Sub
End If
Sheet1.Unprotect "qc"
ActiveCell.Locked = False
ActiveCell.Value = Sheet9.Range("A1").Value
ActiveCell.Locked = True
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Row = 21 Then
Sheet1.Range("C29").Activate
End If
Sheet1.Protect Password:="qc", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub
Cheers - the code I have written is below;
Public Sub Timer()
If Sheet9.Range("A1").Value <> "" Then
AcceptInput
Sheet9.Range("A1").Value = ""
End If
Application.OnTime Now + TimeValue("00:00:01"), "Timer"
End Sub
Private Sub AcceptInput()
Application.ScreenUpdating = False
Sheet1.Activate
If Not _
((ActiveCell.Column = 3 And ActiveCell.Row > 10 And ActiveCell.Row < 21) Or _
(ActiveCell.Column = 3 And ActiveCell.Row > 28 And ActiveCell.Row < 39)) Then
MsgBox "You cannot enter values into cell " & ActiveCell.Address, vbCritical
Exit Sub
End If
Sheet1.Unprotect "qc"
ActiveCell.Locked = False
ActiveCell.Value = Sheet9.Range("A1").Value
ActiveCell.Locked = True
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Row = 21 Then
Sheet1.Range("C29").Activate
End If
Sheet1.Protect Password:="qc", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub