RE: 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.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="calldennis", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _



"EagleOne@xxxxxxxxxxxxxxxxxxxxxxxxx" wrote:


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, _
End If

End Sub


Relevant Pages

  • Re: userform exception error 4 textboxes
    ... So the textboxes pointing to the formula cells get updated ... textbox or does one need to create a class module ... Private Sub TextBox1_AfterUpdate ... I also do similar for buttons and checkboxes because I hate using controls on a worksheet when there's a better, more efficient way to handle things without the extra overhead the controls involve. ...
  • Re: Is it possible to make pasting not allowed?
    ... The menu controls, respectively ... Private Sub Workbook_BeforeClose ... Select the cells that you want thm to be able to change them, ... Protect the sheet. ...
  • Re: connecting formulas
    ... Private Sub Worksheet_Change ... 'If not changing A1 or B1, or if you change a group of cells ... 'Formula that controls B1 ... "Chris" wrote: ...
  • Re: Can I blanket update all controls inside a frame?
    ... The problem I had was that when the worksheet updates, not all these controls update to reflect the changed value. ... This ensures all controls always show their linked cells value in real time when click the button, ... Private Sub CommandButton1_Click ...
  • Re: input date from calendar in textbox
    ... Private Sub Calendar1_Click ... ' Transfer date selected on calendar to the insertion ... >inserted within the grey field, keeping the grey field and bookmark. ...