Hello,
I apologize if I am posting this to the wrong thread area... I am a newbie to this forum. I also do not completely understand how VBA is designed to work. Anyway, I am trying to create a timer button in Excel 2016 that simultaneously unlocks cells for input by other users and also starts a countdown timer. I thought I had it figured out; but am still getting a compile error. As stated previously, I am a non-coder (having copied the countdown timer code from other users of this forum. The code associated with unlocking cells, I copied into the VBA window once I recorded the appropriate macro within Excel. Both sets of code are posted below:
Please help me solve this issue. I am a teacher trying to use Excel to create a testing platform that students can access via access to a network drive, sent via e-mail attachment, or posted to a learning management system (like Canvas). I want to have the test timed with a set time limit. I don't want the students to be able to answer any questions until the timer has started. Whether that timer is controlled by a button or by a cell value entered in another cell matters not to me (as long as it is logical, simple and happens in the course of filling in the appropriate blanks). To be clear, from this forum; I am mostly concerned with the code for a timer and simultaneous unlocking of certain cells within the document. I thought you would appreciate some background on my problem for perspective.
Thank you so much for looking into this for me!
I apologize if I am posting this to the wrong thread area... I am a newbie to this forum. I also do not completely understand how VBA is designed to work. Anyway, I am trying to create a timer button in Excel 2016 that simultaneously unlocks cells for input by other users and also starts a countdown timer. I thought I had it figured out; but am still getting a compile error. As stated previously, I am a non-coder (having copied the countdown timer code from other users of this forum. The code associated with unlocking cells, I copied into the VBA window once I recorded the appropriate macro within Excel. Both sets of code are posted below:
VBA Code:
Sub RunAllmacros()
Procedure1
Procedure2
End Sub
Sub Procedure1()
Public interval As Date
ThisWorkbook.Worksheets("Sheet1").Unprotect ("test")
Sub timer()
' Check if the timer is finished and exit the macro if timer is finished
interval = Now + TimeValue("00:00:01")
' Remove 1 second from the timer
If Range("j13").Value = 0 Then Exit Sub
' Set when the macro when the macro should run again - should be the same time value
' as the previous line.
Range("j13") = Range("j13") - TimeValue("00:00:01")
' Make this macro run again in 1 second
Application.OnTime interval, "timer"
End Sub
Sub stop_timer()
' Stop the timer macro from running
Application.OnTime EarliestTime:=interval, Procedure:="timer", Schedule:=False
End Sub
Sub reset_timer()
' Default time for the timer
Range("j13").Value = "00:15:00"
End Sub
ThisWorkbook.Worksheets("Sheet1").Protect ("test")
End Sub
Sub Procedure2()
Sub UnlockAnsCells()
'
' UnlockAnsCells Macro
' This will unlock Answer Cells so that students can complete the test.
'
' Keyboard Shortcut: Ctrl+Shift+U
'
Range("J19,J24,J29").Select
Range("J29").Activate
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 28
Range("J19,J24,J29,J34,J39,J44,J49,J54").Select
Range("J54").Activate
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 52
Range("J19,J24,J29,J34,J39,J44,J49,J54,J65,J70").Select
Range("J70").Activate
Selection.Locked = False
Selection.FormulaHidden = False
End Sub
End Sub
Please help me solve this issue. I am a teacher trying to use Excel to create a testing platform that students can access via access to a network drive, sent via e-mail attachment, or posted to a learning management system (like Canvas). I want to have the test timed with a set time limit. I don't want the students to be able to answer any questions until the timer has started. Whether that timer is controlled by a button or by a cell value entered in another cell matters not to me (as long as it is logical, simple and happens in the course of filling in the appropriate blanks). To be clear, from this forum; I am mostly concerned with the code for a timer and simultaneous unlocking of certain cells within the document. I thought you would appreciate some background on my problem for perspective.
Thank you so much for looking into this for me!
Last edited by a moderator: