Recorded Macro Combined with Established VBA code

dholmes

New Member
Joined
Oct 30, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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:

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:

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,803
If you are not opposed to a design change ....

What about placing the first group of questions on Sheet "First Questions Group" ... the second group of questions on Sheet "Second Questions Group", etc. etc.

The workbook could have an ENTRANCE SHEET that appears when the workbook is opened. No other sheets will be shown at this time.
You can use the ENTRANCE SHEET for test instructions, etc. The student will press a START button on that sheet ... the ENTRANCE SHEET is no longer visible
and cannot be activated again. The "First Questions Group" sheet is displayed and no others are visible. This sheet is on a timer. If the student completes
the questions before the timer finishes, they can click a button to proceed to "Second Questions Group" sheet. Or if the timer ends before they finish all
the questions on the "First Questions Group" a warning message is displayed advising them their time is up and the test automatically moves to the next
test sheet.

This process continues, sheet by sheet, until the test is finished.

All of the sheet are hidden from view as they are completed and the student cannot gain access to them as they are password protected for your use.

????
 

Watch MrExcel Video

Forum statistics

Threads
1,119,170
Messages
5,576,518
Members
412,730
Latest member
Thundereagle
Top