Macro problem..

[APN]

I am using the following macro...

ActiveSheet.Unprotect Password:="1234"
'Sub InsertRowIF>3()
If Selection.Rows.Count > 1 Then Exit Sub
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="1234"
If ActiveCell.Row < 4 Then Exit Sub
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="1234"
ActiveSheet.Unprotect Password:="1234"
ActiveCell.EntireRow.Insert
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="1234"

End Sub

The problem is I have a rows A & B blank C with TOTALS .D..E..F £.....

A..Blank..B..Blank..C..TOTALS..D..amount $..E..amount $..F..amount $..

I would like to know how to include in the above macro.....to end sub if the row with the above info on it is selected for a row insert....i.e. I do not want a row inserted on selection of row with TOTALS on it as it affects the formulas..

Any help much appreciated....

[HalfAce]

Hi,
I'm not sure I get the picture on your layout, but if I'm guessing right (that the value TOTALS is found only in column C) then you should be able to get away with this.

Sub InsertRowIFGreaterThan3()
Dim x As Long
x = ActiveCell.Row
If Selection.Rows.Count > 1 Or x < 4 Or Cells(x, 3).Value = "TOTALS" Then
Exit Sub
End If
ActiveSheet.Unprotect Password:="1234"
ActiveCell.EntireRow.Insert
ActiveSheet.Protect Password:="1234"
End Sub

(If I'm wrong, post back. We can get this figured out if I understand a little more.)
Dan

[APN]

Dan...

I have 10 worksheets 6 of them outlined as below.....
..........A..........B..............C.......................D.........E.......F...........G
Row1.........................Company Name...........................................
Row2..Date....Invoice....Details.................Total€....Tax€...Nett€..Remarks.
Row3............................Materials......................................................
Rows 4 to 11 Material Details

Rows12.........................ESB............................................................
Rows13 to 19..ESB Details etc..


Row20...........................Refuse........................................................
Rows21 to 27..Refuse Details..

Row28...........................TOTALS................€...........€..........€.............

The Totals from Materials transfers to sheet 8
The Totals from EBB transfers to sheet 9
The Totals from Refuse transfers to sheet 10
I could receive anything from 10 to 400 invoices,I am trying to keep the sheets as short as possible, so I am trying to write a macro to insert a row only between rows 4 to 11,13 to 19 and 21 to 27 as the formulas used to transfer the amounts to sheets 8,9 & 10 will not add the amount if a row is inserted on rows.1,2,3,12,20 or 28
Therefore I want to prevent a row being inserted if selected, on Rows 1,2,3,12,20 & 28.on the 6 sheets only...
The sheets have to be protected...
Is ther a way to prevent other users from reading or altering the macro..

APN..Jim.

[HalfAce]

Hi Jim,
Let's see if I'm keeping up here.
To get this to only work on the sheets or your choice, just paste this code in a standard module, assign it to a button and copy & paste the button to all the sheets you choose.
If I'm understanding what you want to do, this code should work. (or at least get you closer to it...)

Sub InsertRowsIf()
Dim x As Long
x = ActiveCell.Row

If Selection.Rows.Count > 1 Then Exit Sub
'(This next line will select the next row down if Col. C
'contains "TOTALS". Just delete this if you don't need it or
'change the 1 to -1 if you want to go to the next row up instead.)
If Cells(x, 3) = "TOTALS" Then Cells(x, 3).Offset(1, -2).Select

If x > 3 And x < 12 Or x > 12 And x < 20 Or x > 20 And x < 28 Then
  ActiveSheet.Unprotect Password:="1234"
   ActiveCell.EntireRow.Insert
    ActiveSheet.Protect Password:="1234"
End If
   
End Sub

Now, to protect it so nobody else can see or change your code, go into the VB Editor (Press Alt+F11) and right click on the VBAProject (Your Workbook Name) > VBA Project Properties > Protection tab. Check the "Lock project for viewing box", enter your password twice and hit OK. Press Alt+Q and If I remember correctly, you need to save & close the workbook and then reopen it. Now you will need to use your password to view (and therefore change) any code in the workbook.
This help?
Dan

[APN]

Dan..
Many Thanks for your reply and help...
I should be able to adjust to what I want...

APN..Jim.

[HalfAce]

Most welcome.
Good luck. (And as Yogi says...Keep EXCELling!)
Dan