locking drop down menus after a certain date and time

scockster

New Member
Joined
Feb 15, 2019
Messages
17
I'm running an NFL knockout pool with 750+ people and have a sheet prepared where each player has a drop down menu to choose a team every week to win and it is already validated to where they can only choose a team one time all year. I need to lock certain cells after a certain time and date because of different game times so they cant go back and change their pick after the game starts. PLEASE HELP!!!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi there, do you have a list of dates for which each drop down should be locked after?

Also, and maybe this question won't help too much but I'm interested, how do you receive the selections anyway? Is it all in a sharepoint or something along those lines?
 
Upvote 0
I have a list of dates and times that I would like to lock these cells. It would be the day and time the game starts that I would like to lock each cell so the player cannot go back and change their pick. Any ideas???
 
Upvote 0
Hi there, do you have a list of dates for which each drop down should be locked after?

Also, and maybe this question won't help too much but I'm interested, how do you receive the selections anyway? Is it all in a sharepoint or something along those lines?


Hey Kenny, I wanted to get back to you with little more info. So each week there will usually be 5 different times where I would need cells locked. 8:00pm on Thursday, 3 different times on Sunday (1:00pm, 4:15pm, and 8:30pm), and 8:30pm on Monday. Once they lock, they stay locked. I think I finally found a solution for locking which I will paste below but I still don't know how I am going to share this workbook. I need to give 500 people access to this workbook so they can go into it and make their own selection and it save on the fly. Of course I will have restrictions on each person on what they can see and edit. Google sheets, One drive, a personal website..??? I'm really not sure which of those vehicles would accommodate me the most. Maybe you can give me some insight on that. Here is the code for scheduling a macro to run and lock cells a particular date and time. The Macro that locks the cells is called "test". I recorded it.

THIS GOES IN THE "This Workbook" OBJECT
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Private Sub Workbook_Open()

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Application.ontime DateValue("2/24/2019") + TimeValue("10:56:00"), "test"

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sheets("HUMANTEAMS").Protect Password:="123456", UserInterFaceOnly:=True
End Sub[/FONT]

THIS GOES IN THE MODULE SECTION (which is the actual Macro)

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub test()
'
' test Macro
'[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'
Range("C5:E5").Select
Selection.Locked = True
Selection.FormulaHidden = False
End Sub
[/FONT]

When protecting the worksheet I have the top two selections checked
þ select locked cells
þ select unlocked cells.

This works even if the worksheet is closed or open. I'm assuming all I have to is add the other times and make it occur on a weekly basis. Thanks for your interest.
 
Upvote 0

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top