[RESOLVED] Excel Conundrum

[Kubull]

Hi All,

I'm trying to get this code to work, I'm stuck on the first hurdle, I'm trying to get the below to lock each cell after a user enters a value, but I can't get it to work.

The only ranges a user would need to fill in once are:

C8 - F12
C23 - F27
C38 - F42
C53 - F57

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
ActiveSheet.Unprotect Password:="justme"
N = Target.Row
If Excel.Range("B" & N).Value <> "" Then
Excel.Range("B" & N).Locked = True
End If
End If
enditall:
ActiveSheet.Protect Password:="justme"
Application.EnableEvents = True
End Sub

[Siddharth Rout]

You lost me...

Forget the code at the moment... Tell me what do you exactly want to do?

[Kubull]

haha!! Sorry!

Okay, Well i have an Excel sheet where I enter my daily working hours, IE: 07:30 - 12:30 - 13:30 - 18:30

Management in their infinite wisdom think this is a great idea and want everyone to do it, but are worried the peoples will use this to fiddle the numbers (somehow) so I tried to get a code that locks each cell after editing, I tried to upload the excel sheet to attach to the post but, due to format and size it can't be attached, it would explain things alot easier, sorry.

[Siddharth Rout]

that locks each cell after editing

What if the user has made a mistake in typing. How would the user amend it?

[Kubull]

I know, daft an't it! I think they would then appoint an administrator (not liable to bribes, counts me out!) that would be able to amend the typos/mistakes and reprotect.

[Siddharth Rout]

You still want to go ahead with it?

If yes, then my next question... If the first user enters and locks the sheet then how will the 2nd user enter data?

[Kubull]

Yeah, I think it could be a good idea, and could save alot of paper,

Techincally, the sheet consists of 4 weeks worth of daily times, and is only meant to be filled in by one user each day, the above code was good because it wouldn't lock the cell unless a value has been entered.

Ideally the sheet would be a template for which users would open and then save for themselves each month.

[Siddharth Rout]

Ok let me upload a sample for you... Could you tell me which version of excel are you using?

[Kubull]

Ah your awesome! many thanks it's 2003 SP3

[Kubull]

Would you need a copy of the Sheet, I'll try and get a chopped down version, so you can see what I'm on about..

Please see the attached... I've had to change the file extention to .doc from .xls so i could post it.

[Siddharth Rout]

Ok I was busy creating a separate sample for you but it's good that you gave a sample of your own for me to work on...

So now what you want (correct if I am wrong) is

The moment user fills up Cell C8 and D8 then those cells should be locked. Similarly when the user fills up C9 and D9 then those cells should get locked... Am I right in my understanding?

[Kubull]

Yeah, although its more the individual cells rather then the pairs.

[Siddharth Rout]

Something like this? I have just worked for the range C8 to F12

The moment you enter any value in the above range, it will get locked...

Before you paste this code ensure that
1) The locked property of C8 to F12 is False.
2) The sheet is protected using the password "justme"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Long, c As Long

    '~~> Required as it will give error if it encounters a protected cell    
    On Error Resume Next
    r = Target.Previous.Row
    c = Target.Column
    On Error GoTo 0
    
    Select Case c
    Case 3, 4, 5, 6 '<~~ Column C to F
        Select Case r
        Case 8, 9, 10, 12 '<~~ Row 8 to 12
            If Len(Trim(Cells(r, c))) = 0 Then Exit Sub
            ActiveSheet.Unprotect Password:="justme"

            Cells(r, c).Locked = True
            
            ActiveSheet.Protect Password:="justme"
        End Select
    End Select
End Sub

Edit:
I am leaving for the day (I am in the office and don't have internet at home for the time being). Will check on this thread tom

[Kubull]

Thanks again, I'll test it when I get home, but from the looks of it, it looks good!

[Kubull]

Thanks again Coolsid! the Code works really well, I'm just putting the final touches to the template before submitting to the 'heavy's'. I'm having slight issue with a userform I've created for it, I'm used to Word and userforms "Selection.Typetext...blah!) but it's different in Excel, I'm trying to get the below to work, but it's not liking it. any help?

Private Sub cmdOkay_Click()
Dim ws As Worksheet
Set ws = Worksheets("FlexiHours")

ws.Cells(B3, 2).Value = Me.txtname.Value
ws.Cells(E3, 5).Value = Me.txtdepartment.Value
ws.Cells(J3, 10).Value = Me.txtbalance.Value
ws.Cells(B8, 2).Value = Me.txtdate.Value

End Sub

[Siddharth Rout]

There are two ways you can reference a cell for example if you want to refer to cell B10 then you can do it like this

Range("B10")

or

Cells(10,2) '<~~ 10 is the row number and 2 is the column number

[Kubull]

Thanks again, "Range("") worked in the end! your a star, if any royalities come out of this (Government department... doubt it!) 50-50? lol more like 70 - 30! thanks again!