• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trying to Create a Simple Sign Up Sheet

  • Thread starter Dimitrios Paterakis
  • Start date
D

Dimitrios Paterakis

Guest
Hello All,

I am trying to create a simple sign up sheet using an exsisting spreadsheet. As it is used now, one person is a point of contact that issues 5 minute blocks of "work" by entering a technician name. I have a macro that automatically stamps the date and time. I would like to create a way for each user to get their own blocks using a User Form. The form would populate the next available block and display the block to the user requesting it. I've been trying to do this myself for some time but to no avail. It's definitley beyond my ability at this point but it seems like it would be fairly simple for an advanced user. Any help would be greatly appreciated.

This is the User Form I would like to use. User enters name, clicks Get Next Block button, and the next unassigned block is then assigned and displayed in the block window.
User Form.jpg

This is the current spreadsheet currently being used without the UserForm

CurrentSpreadSheet.jpg

Here's the code that enters the date and time automatically. It works just fine, I'm including it just in case.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 2 Then
  Application.EnableEvents = False
  Cells(Target.Row, 3).Value = Time
  Cells(Target.Row, 4).Value = Date
  Application.EnableEvents = True 
  End If
End Sub
 
Welcome to the forum!

Tried to build this from scratch, but I think it matches what you described.
 

Attachments

  • Example Form.xlsm
    23.6 KB · Views: 10
Works perfectly! Thank you so much for your time Luke.

Any suggestions on the best way to have the user access this? I would prefer they not see/manipulate the spreadsheet once they get their assignment. Some info that may help; all the user are NOT in the same location. Some are in the office and some remote in. All ARE on the same intranet though. The spreadsheet is on a shared drive that they all have access to.

Would it be possible to access form that pops up via an intranet webpage where the use just types in "blocks" and is taken to that pop up? In that scenario, would it then be possible to eliminate the need for the user input and just use the user name that they log into the system with?

Again, Thank you so much for taking the time to help out a total stranger, it is very much appreciated Luke.

Dimitrios
 
I personally don't have much experience with SharePoint or other similar software, so not sure how to set that up. We could make the workbook/worksheet hidden though. See the latest version...I setup the form to launch when user opens workbook. Workbook itself is hidden. When user is done and clicks 'exit', the workbook saves and closes.
 

Attachments

  • Example Form.xlsm
    24.3 KB · Views: 9
I think this should work quite nicely Luke. You are indeed worthy of your ninja status. Thanks so much for your help. I will test this for a couple of days and report back.

Thanks again
 
One more request, sorry Luke. Is it possible to save the worksheet as the date (e.g. 11032014.xls) automatically once the last time block for the day is assigned (23:55) and have a new day/file started when the next block is requested?

Thanks again Luke,
 
I should note, if you want to edit the code, when the form appears, hit Ctrl+Alt+Break (located on Pause key). This will halt the code, and let you edit it, if desired.
 
Hmmm... I tested it at home and when i clicked the button to get the next block after 2355, it gave me 0000, which is good, but it didn't save that days info and create a new file under the new date. In other words, I want the spreadsheet named 11042014 to save under that file name with all the logged info and a new document, 11052014 to be created.
 
Hmm. Clairfication:
IF today is Nov 4, and the file is currently on the Nov 4, what happens after last slot is filled? Should file deny any new entries, or should it create a new file for tomorrow?
 
It should create a new file without altering the old file, creating a record that can be referenced, if necessary, in the future. I apologize for not being clear earlier.
 
Ok, that helps. I added some more code to what happens when you click the button. If checks if it's reached the end. If it has, takes current file's name (which is a date), adds 1 to it, and creates new file. Attached file has all slots already filled, for testing purposes.
 

Attachments

  • 11042014.xlsm
    30.3 KB · Views: 8
Perfect! Can't thank you enough Luke! I will try this in the next couple days with multiple live users and see how it goes. I will let you know.

Again, thank you so much for all your help. I really appreciate it.
 
Ok Luke... Update. It does work, but the problem I am continually running into is that the end users aren't closing the file once they get their blocks. Would it be possible to automatically save and close the file and still display the block time to the end user?

Thanks again Luke
 
I suppose you could make it so that you click the button on form, form closes, msg box appears with your time, and then the whole file saves & closes. Is that what you want?
 
Hello again Luke,

I have been using the form for a little while now, and for the most part it has been working great. The users like it as well because it is much more efficient than our old process. The issue I am having is occasionally the file is corrupted and/or deleted all together. I suspect because 2 users are attempting to access it at the same time. In order to avoid this I was wondering if creating a second worksheet that would act as a sort of "front end" or a "wrapper" would be possible. The user would open the 'front-end' worksheet, which would display the options to get 1 or 2 blocks. Once clicked, the 'back-end' spreadsheet would open, process the request, save, and close, leaving the block time displayed using the 'front-end' worksheet. This would minimize the amount of time each user is in the worksheet and allow my to "hide" the worksheet as well.

I have tried doing this myself, using a word doc and another worksheet as my 'front-end' to no avail. If you could help me accomplish this I would be most appreciative.

I have modified the previous version you sent me so I'll include the version I am currently using.

Thanks Luke
 

Attachments

  • 12092014.xlsm
    36.8 KB · Views: 0
Hi Dimitrios,

What you ask can be done...but I wouldn't recommend it. Having all the data within one workbook is much easier/simpler to code than having multiple workbooks. If you add a 2nd book, then we have to write code that can open/access the other workbook, and add in an error check in the rare case that the 2nd book is already open.

With the current setup, if one user is already in the file, then anyone else trying to access the file should be getting a "file already open, go to read-only" type message. Is that not happening?
 
It is but unfortately they are still managing to either corrupt or delete the file.
 
My fear then would be that they could ultimately do the same thing to the back-end type file we make.

Reminds me of a quote:
We can never make something idiot proof...the universe always makes a better idiot.

:p
 
So true,

I'll just go back to the tried and true paper and pen method.

Thanks for all you help Luke! It is appreciated.
 
Back
Top