Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to format only the cells added in a new row insertion
2003/2007
OK, I must not be asking the correct/logical question. My w/s is protected; but the User can insert Rows. I would like to have Excel automatically remove the protection for the cells in the new row so that the cells (in all columns) can be populated with data. That said, I do not wish the User to ba able to change the data (except in certain columns which are not protected) in the cells that were their prior to the inserted row data. Truly, I am lost as to the best way to do this. Is there a way to do this? I have played with worksheet events but I am having a problem obtaining the original W/S row count and comparing that to the row count after the row insertion. My idea was: IF Sheets("Trial Balance Current").Rows.Count OrigRows Then "Reformat only the added cells via the row insertion" I am almost there but no home run yet. Any help greatly appreciated!! Eagleone The unsuccessful code that I have is: Function OrigRows() As Long OrigRows = Sheets("Trial Balance Current").UsedRange.Rows.Count End Function Private Sub Worksheet_Activate() ' How do I get OrigRows in the function above to the code next? End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Sheets("Trial Balance Current").Rows.Count OrigRows Then Dim myRange As Range ActiveSheet.Unprotect Password:="calldennis" Set myRange = Intersect(ActiveSheet.UsedRange, Selection) Selection.Locked = False Selection.FormulaHidden = False ActiveSheet.Protect Password:="calldennis", DrawingObjects:=False, Contents:=True, _ Scenarios:=False, AllowFormattingCells:=True, AllowFormattingRows:=True, _ AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to format only the cells added in a new row insertion
Hi birdy;
If I understood you well, you might try this one. I regularly face this "problem" with my projects. People must be able to insert new records (at the bottom of a database/table/list), but may not change the contents of that database. I solve that this way. The fully protected database starts at row 9 with the headings/fieldnames so that the first record is in row 10 (easy for counting etc). Now in row 6 I put the same fieldnames as on row 9 and row 7 becomes the "Input-record" New stuff is inserted into this row which is always unprotected. Then you can then do several things. I first check the contents of the input record and when that is correct, unprotect the database/worksheet, add the record at the bottom ot the database, format the database so that the new/last/added record looks like all the others, clean the input-record and protect the database/worksheet again. All this is triggered by a command-button. Might that be a solution ? -- Regards; Mark Rosenkrantz -- Spreadsheet Solutions Uithoorn Netherlands (Those who live some 18 feet below sea level) -- E: W: www.spreadsheetsolutions.nl -- wrote in message ... 2003/2007 OK, I must not be asking the correct/logical question. My w/s is protected; but the User can insert Rows. I would like to have Excel automatically remove the protection for the cells in the new row so that the cells (in all columns) can be populated with data. That said, I do not wish the User to ba able to change the data (except in certain columns which are not protected) in the cells that were their prior to the inserted row data. Truly, I am lost as to the best way to do this. Is there a way to do this? I have played with worksheet events but I am having a problem obtaining the original W/S row count and comparing that to the row count after the row insertion. My idea was: IF Sheets("Trial Balance Current").Rows.Count OrigRows Then "Reformat only the added cells via the row insertion" I am almost there but no home run yet. Any help greatly appreciated!! Eagleone The unsuccessful code that I have is: Function OrigRows() As Long OrigRows = Sheets("Trial Balance Current").UsedRange.Rows.Count End Function Private Sub Worksheet_Activate() ' How do I get OrigRows in the function above to the code next? End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Sheets("Trial Balance Current").Rows.Count OrigRows Then Dim myRange As Range ActiveSheet.Unprotect Password:="calldennis" Set myRange = Intersect(ActiveSheet.UsedRange, Selection) Selection.Locked = False Selection.FormulaHidden = False ActiveSheet.Protect Password:="calldennis", DrawingObjects:=False, Contents:=True, _ Scenarios:=False, AllowFormattingCells:=True, AllowFormattingRows:=True, _ AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to format only the cells added in a new row insertion
Interesting thoughts. THat said I need a worksheet_ChangeEvent to unprotect the added (which were
added via row insertion only) cells only. It might be interesting if I knew hor to query the ReDo function for Row inserts. I do just not know how to access those properties or attributes "Spreadsheet Solutions" wrote: Hi birdy; If I understood you well, you might try this one. I regularly face this "problem" with my projects. People must be able to insert new records (at the bottom of a database/table/list), but may not change the contents of that database. I solve that this way. The fully protected database starts at row 9 with the headings/fieldnames so that the first record is in row 10 (easy for counting etc). Now in row 6 I put the same fieldnames as on row 9 and row 7 becomes the "Input-record" New stuff is inserted into this row which is always unprotected. Then you can then do several things. I first check the contents of the input record and when that is correct, unprotect the database/worksheet, add the record at the bottom ot the database, format the database so that the new/last/added record looks like all the others, clean the input-record and protect the database/worksheet again. All this is triggered by a command-button. Might that be a solution ? -- Regards; Mark Rosenkrantz -- Spreadsheet Solutions Uithoorn Netherlands (Those who live some 18 feet below sea level) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to format only the cells added in a new row insertion
The used range should change each time a row is inserted/deleted so i don't
know how you could make your code work. I think that you would be better to create a command button to insert rows and remove allow insert rows in the protection. That way only by clicking the command button can a row be inserted. Not sure how comfortable you are with VBA and controls etc but I have used an Activex command button. In xl2007 these are the ones in the bottom section of the drop down under Controls Insert on the Developer ribbon. In earlier versions of xl they are in the controls toolbox toolbar (Not forms toolbar). You need to click the design button (like a blue set square, ruler and pencil) to make alterations to activex controls and you need to turn design off to use the controls. With the design button clicked, right click the control and select view code and insert the following code between the private sub and end sub that is created. The following command button code will unprotect the sheet, insert a row at the currently selected cell or cells, unlock the inserted row and then protect the sheet again. You might want to include a msgbox to confirm that the user wants to insert a row at the selected cell and it gives them an out if they click the button unintentionally. ActiveSheet.Unprotect Password:="calldennis" Selection.EntireRow.Insert Selection.EntireRow.Locked = False Selection.FormulaHidden = False ActiveSheet.Protect Password:="calldennis", _ DrawingObjects:=False, _ Contents:=True, _ Scenarios:=False, _ AllowFormattingCells:=True, _ AllowFormattingRows:=True, _ AllowSorting:=True, _ AllowFiltering:=True, _ AllowUsingPivotTables:=True -- Regards, OssieMac " wrote: 2003/2007 OK, I must not be asking the correct/logical question. My w/s is protected; but the User can insert Rows. I would like to have Excel automatically remove the protection for the cells in the new row so that the cells (in all columns) can be populated with data. That said, I do not wish the User to ba able to change the data (except in certain columns which are not protected) in the cells that were their prior to the inserted row data. Truly, I am lost as to the best way to do this. Is there a way to do this? I have played with worksheet events but I am having a problem obtaining the original W/S row count and comparing that to the row count after the row insertion. My idea was: IF Sheets("Trial Balance Current").Rows.Count OrigRows Then "Reformat only the added cells via the row insertion" I am almost there but no home run yet. Any help greatly appreciated!! Eagleone The unsuccessful code that I have is: Function OrigRows() As Long OrigRows = Sheets("Trial Balance Current").UsedRange.Rows.Count End Function Private Sub Worksheet_Activate() ' How do I get OrigRows in the function above to the code next? End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Sheets("Trial Balance Current").Rows.Count OrigRows Then Dim myRange As Range ActiveSheet.Unprotect Password:="calldennis" Set myRange = Intersect(ActiveSheet.UsedRange, Selection) Selection.Locked = False Selection.FormulaHidden = False ActiveSheet.Protect Password:="calldennis", DrawingObjects:=False, Contents:=True, _ Scenarios:=False, AllowFormattingCells:=True, AllowFormattingRows:=True, _ AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching for code for insertion of dynamic excel formulas | Excel Programming | |||
filename insertion in cells | Excel Programming | |||
Automatic Row insertion code | Excel Programming | |||
Copyi8ng format after row insertion | Excel Programming | |||
VBA Code- Row Insertion | Excel Programming |