Sharing part of workbook

[tbones]

Hello,
I run a football prediction league on facebook. Basically I put onto a facebook post around 10 matches per week. Each player copies the matches and adds their scores to them. What I have been doing then is once the matches have been played I manually check them against the results and then add the totals onto a spreadsheet.
I am doing slightly different for the year as I have created a work book with the master table which is populated by 38 spreadsheets which represents the week number of the game form Gameweek 1 to 38.
Each of the spreadsheets has a master table with the fixtures on it. Once I populate the fixtures it then populates the same info for each player within each worksheet so in the master table on the spreadsheet it would read A versus B, c versus d, e versus f and so on. This then populates each fixture list for upto 25 players. At the moment once I have all the predictions in I then manually input these to the players fixture list on their part of the table. Once done and the matches have been played I then input the actual results into the master table which is then linked to everyones predictions. If they predict the correct score then they win a point. The total points for each player then is populated onto a different worksheet .

So the question I am asking for this is that for each gameweek spreadsheet there are 25 different tables 1 for each player. Is there a way of linking their individual table on the spreadsheet to them so that they can populate it them selves without being able to populate the other players in the game.

I may not have put this right... In an ideal world there would be a website that each player could put their predictions into and then this is compiled automatically to a spreadsheet that would then populate once the results have come in... a bit like fantasy football web pages,

Any help would be amazing

 

[Monty Norman]

Hello @tbones
Why not name each worksheet with the name of the player who will fill it?

For example if James Bond is a player that sends you results, name the worksheet "James Bond"?

Note you up to 32 characters for each worksheet name?

Let me know if that works for you.

Monty

 

[tbones]

Hello @tbones
Why not name each worksheet with the name of the player who will fill it?

For example if James Bond is a player that sends you results, name the worksheet "James Bond"?

Note you up to 32 characters for each worksheet name?

Let me know if that works for you.

Monty

Thank you for your reply. I had thought of that to be fair although I would need to reprogram and re VBA code the work book. I did see something about sharing a range within the worksheet so I thought maybe it could be done like that. I have managed to do that part but I am not sure how to protect the rest of the worksheet . So where I have selected the range it will allow me to edit within the range but it will also allow me to edit the rest of the workbook. What would be good is to be able to protect the unranged area within the workbook for each player.

 

[Candyman8019]

Check out this post. It may be overkill for what you're looking for, but see what you think.

Lock Down Spreadsheet to Specific Users

Hey Team, Curious questions here. My company has just finished developing an Excel Spreadsheet full of VBA code and functions that we wanted to protect. Is there a way that we can lock down our spreadsheet from an access perspective. Like you have to have some credentials to be able to sign...

www.mrexcel.com

Let me know if the links aren't active anymore and I can paste a new link when I get back to my other computer.

 

[tbones]

Check out this post. It may be overkill for what you're looking for, but see what you think.

Lock Down Spreadsheet to Specific Users

Hey Team, Curious questions here. My company has just finished developing an Excel Spreadsheet full of VBA code and functions that we wanted to protect. Is there a way that we can lock down our spreadsheet from an access perspective. Like you have to have some credentials to be able to sign...

www.mrexcel.com

Let me know if the links aren't active anymore and I can paste a new link when I get back to my other computer.

Thank you I can see that. So my quandary is that I have a worksheet with 25 different tables . What I want to do is to give each person a different table that they can edit but can only read the other tables and not edit.
I started to use Allow Edit Ranges and I could successfully set each table as a range with a different password for each table( range to edit) however when it came to go to that part of the table I could edit it without a password which is not what i wanted to do. How can I get each of the ranges to work so that if the 4th person wants to edit their table with their own set password they can but not able to edit anyone else's table.

 

[Candyman8019]

Okay, how about something like this. Create a small user form for the user to enter their password. Do a worksheet_change event that will validate that password for each of the affected ranges. If the password doesn't match the range, the application will "undo" what the user just changed. You could also add a message box indicating to the user that they don't have permissions to change that field.

Private Sub worksheet_change(ByVal target As Range)
Dim uPassword As String
If Not Intersect(target, Range("A1:D5")) Is Nothing Then
    If uPassword <> "{kevin's password}" Then
        Application.Undo
    End If
End If

If Not Intersect(target, Range("E1:H5")) Is Nothing Then
    If uPassword <> "{john's password}" Then
        Application.Undo
    End If
End If
'repeat for each user / range.
End Sub

 

[Candyman8019]

Quick edit. I forgot a couple of critical statements. Here's the correct code.

Private Sub worksheet_change(ByVal target As Range)
Dim uPassword As String

uPassword = "box"
If Not Intersect(target, Range("A1:D5")) Is Nothing Then
    If uPassword <> "box" Then
    Application.EnableEvents = False
        Application.Undo
    Application.EnableEvents = True
    End If
End If

If Not Intersect(target, Range("E1:H5")) Is Nothing Then
    If uPassword <> "{john's password}" Then
    Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
End If
'repeat for each user / range.
End Sub

 

[Candyman8019]

If you prefer not to add a user form, you could do something like the following. Have a password field at the top of the sheet where the user will enter their password when they open the file:

separatetables.xlsm
	A	B
1	Password:
Sheet1

then use this code to manage permissions. There is also a "masterpass" for your password to allow editing of any cells.

Private Sub worksheet_change(ByVal target As Range)

If Not Intersect(target, Range("B1")) Is Nothing Then    
    'store current password far off in the distance and color it white so it doesn't attract attention.
    Range("JJ1").Font.Color = white
    Range("JJ1").Value = target.Value
    'clear password field for next guy
    Application.EnableEvents = False
    Range("B1").Value = ""
    Application.EnableEvents = True

End If

'if master pass then allow all changes
If Range("JJ1").Value = "masterpass" Then Exit Sub


If Not Intersect(target, Range("A2:D5")) Is Nothing Then
    If Range("JJ1").Value <> "{bobs password}" Then
    Application.EnableEvents = False
        Application.Undo
    Application.EnableEvents = True
    End If
End If

If Not Intersect(target, Range("E2:H5")) Is Nothing Then
    If Range("JJ1").Value <> "{john's password}" Then
    Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
End If
'repeat for each user / range.
End Sub

 

[Candyman8019]

and you'll also want to do a workbook_open event to clear the password when the workbook is opened...something like:

private sub worksheet_open()
Range("JJ1").Value = ""
end sub

 

[tbones]

hank you for your reply. Theres a lot to take in here. I will indeed look at that for sure.
I think I have another way of doing it . I think if I link paste from a webform then that will work. I have tried it and seems ok.
I suppose the problem I have now is how to have a time limit on when they can edit. So say i want the player to not be able to edit after say 20th August at 3pm i need to understand how I do that in different ranges of their own workbook that I send them for them to fill in.