No Clue...

[tahir]

I have been hitting my head with the wall since i m new to VBA, i wis
to block the whole sheet for data entry except the very first row fo
data entry. as the user complete the first row for data entry, He/Sh
will press a button to paste the record to another sheet and afte
pasting the row will be locked but the next row will be available fo
entry. for example if i am entering data on a range A1 to AA1 then th
remaining rows must be locked. and as soon as i finish entry till AA
and press macro button then this particular row should be pasted t
another sheet lets say sheet3. and as soon as the record is pasted, th
row A1:AA1 must be locked and the next row A2:AA2 must be available fo
entry and rest of the rows must be locked. and the cycle will continu
uptil lets say for 200 rows.

Regards,

Darn

 

[Ziado]

Just some brief hints:
I have a program that does a similar thing so I am using what I have,
hopefully it will be of help


' lock whole worksheet, for example
Range("A1:AJ" & row_results + 1).Locked = True

' then unlock the cells you want to be editable, example
ws.Range("A" & merge_cntr + 3 & ":AA" & merge_cntr + 3).Locked = False

' then you need to protect the worksheet (you can add a password to
unprotect ex for administrator)
ws.Protect Password:="pass"

 

[Guest]

Create a button with the following attached:

Sub PasteAndProtect()
Worksheets("Sheet1").Unprotect
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 27)).Locked = True
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 27)).Copy
Worksheets("Sheet3").Activate
Cells(1, 1).CurrentRegion.Select
Selection.Offset(Selection.Rows.Count, 0).Resize(1, 1).Select
ActiveSheet.Paste
Worksheets("Sheet1").Activate
Cells(ActiveCell.Row + 1, 1).Select
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 27)).Locked = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Before using the button, select A1:AA1 and unlock the range (Format, Cells,
Protection) and then protect the sheet (Tools, Protection, Protect Sheet).
You should then be ready to start typing in row 1. Click on the button to
continue to row 2 and so on.

Good luck!

 

[EvolBob]

You could just use XL's built-in User form from the Data menu.

The user can just enter data and this is automatically entered to the first blank row as long as you have header labels in Row 1, XL will know where to add the data - including updating formulae if they already exist.
And all you have to do is make sure this is the activesheet.
This code below could be added to the sheet code - right click sheet tab that has your data, and pick view code....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[A1].Select
ActiveSheet.ShowDataForm
End Sub

... should prevent the user attempting to enter directly onto the sheet.

Add this to the ThisWorkbook code module to activate the Data form when the sheet is selected - I used sheet2 for the database.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name <> "Sheet2" Then End
Sh.ShowDataForm
End Sub


You can do it your way Tahir, but it takes a lot more code and has a lot more traps to guard against.
And would take too long here to explain in detail - I can send you an example workbook if you like?


Regards
Robert McCurdy


I have been hitting my head with the wall since i m new to VBA, i wish
to block the whole sheet for data entry except the very first row for
data entry. as the user complete the first row for data entry, He/She
will press a button to paste the record to another sheet and after
pasting the row will be locked but the next row will be available for
entry. for example if i am entering data on a range A1 to AA1 then the
remaining rows must be locked. and as soon as i finish entry till AA1
and press macro button then this particular row should be pasted to
another sheet lets say sheet3. and as soon as the record is pasted, the
row A1:AA1 must be locked and the next row A2:AA2 must be available for
entry and rest of the rows must be locked. and the cycle will continue
uptil lets say for 200 rows.

Regards,

Darno