Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Clue...
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 -- tahi Student, working on a projec ----------------------------------------------------------------------- tahir's Profile: http://www.excelforum.com/member.php...nfo&userid=605 View this thread: http://www.excelforum.com/showthread.php?threadid=52481 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Clue...
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" tahir wrote: 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 -- tahir Student, working on a project ------------------------------------------------------------------------ tahir's Profile: http://www.excelforum.com/member.php...fo&userid=6053 View this thread: http://www.excelforum.com/showthread...hreadid=524816 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Clue...
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! "tahir" wrote: 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 -- tahir Student, working on a project ------------------------------------------------------------------------ tahir's Profile: http://www.excelforum.com/member.php...fo&userid=6053 View this thread: http://www.excelforum.com/showthread...hreadid=524816 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Clue...
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 "tahir" wrote in message ... 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 -- tahir Student, working on a project ------------------------------------------------------------------------ tahir's Profile: http://www.excelforum.com/member.php...fo&userid=6053 View this thread: http://www.excelforum.com/showthread...hreadid=524816 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I have no clue about how to use vba | Excel Discussion (Misc queries) | |||
SUMPRODUCT clue needed | Excel Discussion (Misc queries) | |||
Not a clue of which function to use! | Excel Discussion (Misc queries) | |||
I have not got a clue | Excel Discussion (Misc queries) | |||
any clue? | Excel Programming |