Cell locking

[wayneg]

I have a table that users fill in. i want to force them the fill out the date in column B on the next available empty row first. all the other cells in that row are to be read only until that cell has the date in. is this possible ?

[S O]

Not actually locking the cells, but I think this does what you require, put this in the worksheet module of the sheet you want it to work on.

Please Login or Register  to view this content.

[wayneg]

ty, will look into this now

[wayneg]

This works, but how do i modify it to use the first available row and not a random row. would i need to add Cells(Rows.Count, "B").End(xlUp) in it some how?

[mg58]

To find the last used row, I would use
Dim LastRow as Long
Dim sw1 as WorkSheet

Set ws1 = Sheets(mysheet) 'where mysheet is the sheetname or number of the sheet you are using
LastRow = ws1.UsedRange.Rows.Count ' LastRow will contain the value of the last used row number

ws1.Range("B" & LastRow).Select
MsgBox "Enter data in cell B " & LastRow


Insert this code, amended to suit your need into your macro.

[wayneg]

i tried this approach now MG58, yes it locks the row and doesnt allow the entry. but the msgbox pops up with the row i am on, and not the next available row... ie if row 98 is the last row with the data in, and i try entering data on row 120, the msgbox pops up saying "To begin, enter the date in cell B120"

I think it has to do with the used range part so i will look into changing that now

[mg58]

Oops, sorry. Should have been either:
ws1.Range("B" & LastRow + 1).Select
MsgBox "Enter data in cell B " & LastRow + 1

or
LastRow = ws1.UsedRange.Rows.Count + 1

This will give you the next free row

[Arkadi]

Please remember to use code tags when posting code mg58?

[wayneg]

This doesnt seem to work how i want it to, sorry......
What i need is for it only allow input in the first available cell in column b. once there is a date in that cell, the row becomes active, and only that row.

this information above is nearly what i need, but i keep getting msgbox saying to put date in that cell

[wayneg]

I have added a stop function to stop the msgbox now, but it still doesnt work properly for the row its on... ie if there are 10 rows of data, and i chose row 20. the msgbox says i need to add the date in b20 and not the first free cell in column b...

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim LastRow As Long
Dim sw1 As Worksheet

Set ws1 = Sheets("Data") 'where mysheet is the sheetname or number of the sheet you are using
LastRow = ws1.UsedRange.Rows.Count ' LastRow will contain the value of the last used row number

If ws1.Range("B" & LastRow) <> "" Then End

ws1.Range("B" & LastRow).Select
MsgBox "Enter date in cell B " & LastRow




End Sub  

[Arkadi]

Your explanation was that column B must have a date before the row can be used... are you saying it also needs to be the first blank row in column B? so if you have 10 rows of data, ONLY row 11 can be used next?

Also, a worksheet_change is an event... that code belongs NOT in a module, but in VBA editor double-click the sheet that is affected and put the code. Then you don't need to dim the sheet, because it is implied to be THAT sheet.

[Arkadi]

Try this code? Please paste it by going to VBA Editor and double-clicking the "Data" sheet on the list on left hand side. This is not code that gets put in a normal module.

Please Login or Register  to view this content.

[S O]

This works, but how do i modify it to use the first available row and not a random row. would i need to add Cells(Rows.Count, "B").End(xlUp) in it some how?

Sorry, didn't realise you had come back on this.

If I follow your logic I think this is what you're after:

Please Login or Register  to view this content.

[wayneg]

Ty all. S O, yours worked the best... i think...lol

[S O]

Happy to help