insert rows--->protected sheet

I have a protected sheet in Excel 2007, and I would like to write a macro 
that allows a user to insert a new row(s) that also copies the formulas (but 
not the text/data) from the previous row.

When I searched for a possible solution, I found the following macro #2, 
which works perfectly, if the sheet is unprotected. I use the following code 
(macro #1) in another macro to unprotect a sheet temporarily to run a macro, 
then re-protect it.

I select a row on the spreadsheet and run the macro 
InsertRowsAndFillFormulas_caller(). I get a "Compile Error: Invalid or 
Unqualified reference".

Any thoughts how to use Macro #2 and embedding Macro #1 so the sheet is 
temporarily unprotected, code to insert row/copy formulas is completed, then 
sheet is protected again?



-----------------------------------------------------------
MACRO #1---Unprotect Sheet/Re-protect Sheet
-----------------------------------------------------------

    Dim strSheetName As String
    strSheetName = ActiveSheet.Name
 
    Const MyPassword As String = "kendallpassword"
    
    Dim ChangeProtection As Boolean

    With Sheets(strSheetName)
        If .ProtectContents = True Then
            .Unprotect (MyPassword)
            ChangeProtection = True
        End If

        If ChangeProtection = True Then .Protect (MyPassword)




-----------------------------------------------------------
MACRO #2---INSERT ROW/COPY FORMULA
-----------------------------------------------------------

Option Explicit

Sub InsertRowsAndFillFormulas_caller()
  '-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
  
    Call InsertRowsAndFillFormulas
    
End Sub

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' Documented:  http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows --   1997/09/24 Mark Hill <markhill@charm.net.noSpam>
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
   
   
    Const MyPassword As String = "kendall415"
    Dim ChangeProtection As Boolean

    If .ProtectContents = True Then
            .Unprotect (MyPassword)
            ChangeProtection = True
    End If
    
   Dim x As Long
   ActiveCell.EntireRow.Select  'So you do not have to preselect entire row
   If vRows = 0 Then
    vRows = Application.InputBox(Prompt:= _
     "How many rows do you want to add?", Title:="Add Rows", _
     Default:=1, Type:=1) 'Default for 1 row, type 1 is number
   End If
   If vRows = False Then Exit Sub
   'if you just want to add cells and not entire rows
   'then delete ".EntireRow" in the following line

   'rev. 20001-01-17 Gary L. Brown, programming, Grouped sheets
   Dim sht As Worksheet, shts() As String, i As Long
   ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
       Windows(1).SelectedSheets.Count)
   i = 0
   For Each sht In _
       Application.ActiveWorkbook.Windows(1).SelectedSheets
    Sheets(sht.Name).Select
    i = i + 1
    shts(i) = sht.Name
    
    x = Sheets(sht.Name).UsedRange.Rows.Count '<a 
href="lastcell.htm#resetall">lastcell fixup</a>
   
    Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
     Resize(rowsize:=vRows).Insert Shift:=xlDown

    Selection.AutoFill Selection.Resize( _
     rowsize:=vRows + 1), xlFillDefault
   
    On Error Resume Next    'to handle no constants in range -- John McKee 
2000/02/01
    ' to remove the non-formulas -- 1998/03/11 Bill Manville
    Selection.Offset(1).Resize(vRows).EntireRow. _
     SpecialCells(xlConstants).ClearContents
   Next sht
   Worksheets(shts).Select

End Sub

Sub InsertBlankRows()
'-- Ken Wright, <a 
href="http://google.com/groups?threadm=%2381wPIlXDHA.2464@TK2MSFTNGP09.phx.gblN">2003-08-09</a>
Application.ScreenUpdating = False
Dim NumRows As Long
Dim R As Long
Dim Rng As Range
Dim lastrw As Long
NumRows = InputBox("How many Rows")
lastrw = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(1, "A"), Cells(lastrw, "A"))
   For R = Rng.Rows.Count To 1 Step -1
       Rng.Rows(R + 1).Resize(NumRows).EntireRow.Insert
   Next R
Application.ScreenUpdating = True

    If ChangeProtection = True Then .Protect (MyPassword)
    
    
End Sub

Sub InsertBlankRowBeforeLast()
   Cells(Rows.Count, "A").End(xlUp).EntireRow.Insert
End Sub

Sub Guarantee2RowsAfterA_values()
    Dim Rng As Range, i As Long
    Set Rng = Intersect(Columns("A:A"), ActiveSheet.UsedRange)
    For i = Rng.Cells.Count - 1 To 1 Step -1
      If Trim(Rng(i).Value) <> "" Then
        If Trim(Rng(i + 1)) <> "" Then
         Rng.Item(i).Offset(1, 0).Resize(2).EntireRow.Insert
        ElseIf Trim(Rng(i + 2)) <> "" Then
         Rng.Item(i).Offset(1, 0).EntireRow.Insert
        End If
      End If
   Next i
End Sub

Sub Guarantee3RowsAfterA_values()
    Dim Rng As Range, i As Long
    Set Rng = Intersect(Columns("A:A"), ActiveSheet.UsedRange)
    For i = Rng.Cells.Count - 1 To 1 Step -1
      If Trim(Rng(i).Value) <> "" Then
        If Trim(Rng(i + 1)) <> "" Then
         Rng.Item(i).Offset(1, 0).Resize(3).EntireRow.Insert
        ElseIf Trim(Rng(i + 2)) <> "" Then
         Rng.Item(i).Offset(1, 0).Resize(2).EntireRow.Insert
        ElseIf Trim(Rng(i + 3)) <> "" Then
         Rng.Item(i).Offset(1, 0).EntireRow.Insert
        End If
      End If
   Next i
End Sub

Public Sub Insert_Rows_betwn_existing()
    'guarantee at least xx blank rows between rows in selection
    'Sean Bartleet, excel.programming,  2005-10-20,  mod. D.McR
' - http://google.com/groups?threadm=%23MKtkBY1FHA.2540%40TK2MSFTNGP09.phx.gbl
    Dim R As Long
    Dim n As Long
    Dim Rng As Range
    Dim myCell As Range
    Dim NumRows As Long, J As Long, inserts As Long
    If Selection.Rows.Count > 0 Then    'corrected since posting
    On Error Resume Next
        Set Rng = Intersect(Selection, ActiveSheet.UsedRange)
        If Rng.Rows.Count = 0 Then
           MsgBox "selection outside of used range"
           Exit Sub
        End If
        NumRows = Application.InputBox("Enter number of rows to insert " _
         & "between each row in the selection", _
         "Input number of guaranteed blank rows", 1, , , , , 1)
        If NumRows = 0 Then
           MsgBox "Cancelled by your command"
           Exit Sub
        End If
        On Error GoTo EndMacro
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual

        n = 0
        For R = Rng.Rows.Count To 1 Step -1
          If Rng.Cells(R, 1) <> "" Then
           For J = 1 To NumRows
             If Rng.Cells(R + J, 1) <> "" Then
               Rng.Rows(R + 1).Resize(NumRows + 1 - J).EntireRow.Insert
               n = n + 1
               inserts = inserts + NumRows + 1 - J
             End If
           Next J
          End If
        Next R
        MsgBox (n & " insertion points for" & NumRows & _
            " blank rows required between populate rows, " _
            & inserts & " blank rows actually inserted" _
            & "within preselected range")
       Rng.Select   '-- show scope based on original range
    Else
        MsgBox ("Must select one or more rows for range " _
          & "before executing command")

    End If
    '-- the following sometimes works but apparently not here
    J = ActiveSheet.UsedRange.Rows.Count 'see J-Walkenbach tip 73
EndMacro:

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Sub InsertRow_A_Chg()
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Dim irow As Long, vcurrent As String, i As Long
 '// find last used cell in Column A
  irow = Cells(Rows.Count, "A").End(xlUp).Row
 '// get value of  that cell in Column A (column 1)
  vcurrent = Cells(irow, 1).Text
 '// rows are inserted by looping from bottom
  For i = irow To 2 Step -1
     If Cells(i, 1).Text = "" Then
       vcurrent = Cells(i - 1, 1)
     ElseIf Cells(i, 1).Text <> vcurrent Then
        vcurrent = Cells(i, 1).Text
        Rows(i + 1).Insert
     End If
  Next i
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub
-----------------------------------------------------------


THANKS!!!!!!!!!
0
Utf
3/19/2010 12:45:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1330 Views

Similar Articles

[PageSpeed] 0

I figured it out. I left out part of the un-protect code. 

"Art" wrote:

> I have a protected sheet in Excel 2007, and I would like to write a macro 
> that allows a user to insert a new row(s) that also copies the formulas (but 
> not the text/data) from the previous row.
> 
> When I searched for a possible solution, I found the following macro #2, 
> which works perfectly, if the sheet is unprotected. I use the following code 
> (macro #1) in another macro to unprotect a sheet temporarily to run a macro, 
> then re-protect it.
> 
> I select a row on the spreadsheet and run the macro 
> InsertRowsAndFillFormulas_caller(). I get a "Compile Error: Invalid or 
> Unqualified reference".
> 
> Any thoughts how to use Macro #2 and embedding Macro #1 so the sheet is 
> temporarily unprotected, code to insert row/copy formulas is completed, then 
> sheet is protected again?
> 
> 
> 
> -----------------------------------------------------------
> MACRO #1---Unprotect Sheet/Re-protect Sheet
> -----------------------------------------------------------
> 
>     Dim strSheetName As String
>     strSheetName = ActiveSheet.Name
>  
>     Const MyPassword As String = "kendallpassword"
>     
>     Dim ChangeProtection As Boolean
> 
>     With Sheets(strSheetName)
>         If .ProtectContents = True Then
>             .Unprotect (MyPassword)
>             ChangeProtection = True
>         End If
> 
>         If ChangeProtection = True Then .Protect (MyPassword)
> 
> 
> 
> 
> -----------------------------------------------------------
> MACRO #2---INSERT ROW/COPY FORMULA
> -----------------------------------------------------------
> 
> Option Explicit
> 
> Sub InsertRowsAndFillFormulas_caller()
>   '-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
>   
>     Call InsertRowsAndFillFormulas
>     
> End Sub
> 
> Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
> ' Documented:  http://www.mvps.org/dmcritchie/excel/insrtrow.htm
> ' Re: Insert Rows --   1997/09/24 Mark Hill <markhill@charm.net.noSpam>
> ' row selection based on active cell -- rev. 2000-09-02 David McRitchie
>    
>    
>     Const MyPassword As String = "kendall415"
>     Dim ChangeProtection As Boolean
> 
>     If .ProtectContents = True Then
>             .Unprotect (MyPassword)
>             ChangeProtection = True
>     End If
>     
>    Dim x As Long
>    ActiveCell.EntireRow.Select  'So you do not have to preselect entire row
>    If vRows = 0 Then
>     vRows = Application.InputBox(Prompt:= _
>      "How many rows do you want to add?", Title:="Add Rows", _
>      Default:=1, Type:=1) 'Default for 1 row, type 1 is number
>    End If
>    If vRows = False Then Exit Sub
>    'if you just want to add cells and not entire rows
>    'then delete ".EntireRow" in the following line
> 
>    'rev. 20001-01-17 Gary L. Brown, programming, Grouped sheets
>    Dim sht As Worksheet, shts() As String, i As Long
>    ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
>        Windows(1).SelectedSheets.Count)
>    i = 0
>    For Each sht In _
>        Application.ActiveWorkbook.Windows(1).SelectedSheets
>     Sheets(sht.Name).Select
>     i = i + 1
>     shts(i) = sht.Name
>     
>     x = Sheets(sht.Name).UsedRange.Rows.Count '<a 
> href="lastcell.htm#resetall">lastcell fixup</a>
>    
>     Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
>      Resize(rowsize:=vRows).Insert Shift:=xlDown
> 
>     Selection.AutoFill Selection.Resize( _
>      rowsize:=vRows + 1), xlFillDefault
>    
>     On Error Resume Next    'to handle no constants in range -- John McKee 
> 2000/02/01
>     ' to remove the non-formulas -- 1998/03/11 Bill Manville
>     Selection.Offset(1).Resize(vRows).EntireRow. _
>      SpecialCells(xlConstants).ClearContents
>    Next sht
>    Worksheets(shts).Select
> 
> End Sub
> 
> Sub InsertBlankRows()
> '-- Ken Wright, <a 
> href="http://google.com/groups?threadm=%2381wPIlXDHA.2464@TK2MSFTNGP09.phx.gblN">2003-08-09</a>
> Application.ScreenUpdating = False
> Dim NumRows As Long
> Dim R As Long
> Dim Rng As Range
> Dim lastrw As Long
> NumRows = InputBox("How many Rows")
> lastrw = Cells(Rows.Count, "A").End(xlUp).Row
> Set Rng = Range(Cells(1, "A"), Cells(lastrw, "A"))
>    For R = Rng.Rows.Count To 1 Step -1
>        Rng.Rows(R + 1).Resize(NumRows).EntireRow.Insert
>    Next R
> Application.ScreenUpdating = True
> 
>     If ChangeProtection = True Then .Protect (MyPassword)
>     
>     
> End Sub
> 
> Sub InsertBlankRowBeforeLast()
>    Cells(Rows.Count, "A").End(xlUp).EntireRow.Insert
> End Sub
> 
> Sub Guarantee2RowsAfterA_values()
>     Dim Rng As Range, i As Long
>     Set Rng = Intersect(Columns("A:A"), ActiveSheet.UsedRange)
>     For i = Rng.Cells.Count - 1 To 1 Step -1
>       If Trim(Rng(i).Value) <> "" Then
>         If Trim(Rng(i + 1)) <> "" Then
>          Rng.Item(i).Offset(1, 0).Resize(2).EntireRow.Insert
>         ElseIf Trim(Rng(i + 2)) <> "" Then
>          Rng.Item(i).Offset(1, 0).EntireRow.Insert
>         End If
>       End If
>    Next i
> End Sub
> 
> Sub Guarantee3RowsAfterA_values()
>     Dim Rng As Range, i As Long
>     Set Rng = Intersect(Columns("A:A"), ActiveSheet.UsedRange)
>     For i = Rng.Cells.Count - 1 To 1 Step -1
>       If Trim(Rng(i).Value) <> "" Then
>         If Trim(Rng(i + 1)) <> "" Then
>          Rng.Item(i).Offset(1, 0).Resize(3).EntireRow.Insert
>         ElseIf Trim(Rng(i + 2)) <> "" Then
>          Rng.Item(i).Offset(1, 0).Resize(2).EntireRow.Insert
>         ElseIf Trim(Rng(i + 3)) <> "" Then
>          Rng.Item(i).Offset(1, 0).EntireRow.Insert
>         End If
>       End If
>    Next i
> End Sub
> 
> Public Sub Insert_Rows_betwn_existing()
>     'guarantee at least xx blank rows between rows in selection
>     'Sean Bartleet, excel.programming,  2005-10-20,  mod. D.McR
> ' - http://google.com/groups?threadm=%23MKtkBY1FHA.2540%40TK2MSFTNGP09.phx.gbl
>     Dim R As Long
>     Dim n As Long
>     Dim Rng As Range
>     Dim myCell As Range
>     Dim NumRows As Long, J As Long, inserts As Long
>     If Selection.Rows.Count > 0 Then    'corrected since posting
>     On Error Resume Next
>         Set Rng = Intersect(Selection, ActiveSheet.UsedRange)
>         If Rng.Rows.Count = 0 Then
>            MsgBox "selection outside of used range"
>            Exit Sub
>         End If
>         NumRows = Application.InputBox("Enter number of rows to insert " _
>          & "between each row in the selection", _
>          "Input number of guaranteed blank rows", 1, , , , , 1)
>         If NumRows = 0 Then
>            MsgBox "Cancelled by your command"
>            Exit Sub
>         End If
>         On Error GoTo EndMacro
>         Application.ScreenUpdating = False
>         Application.Calculation = xlCalculationManual
> 
>         n = 0
>         For R = Rng.Rows.Count To 1 Step -1
>           If Rng.Cells(R, 1) <> "" Then
>            For J = 1 To NumRows
>              If Rng.Cells(R + J, 1) <> "" Then
>                Rng.Rows(R + 1).Resize(NumRows + 1 - J).EntireRow.Insert
>                n = n + 1
>                inserts = inserts + NumRows + 1 - J
>              End If
>            Next J
>           End If
>         Next R
>         MsgBox (n & " insertion points for" & NumRows & _
>             " blank rows required between populate rows, " _
>             & inserts & " blank rows actually inserted" _
>             & "within preselected range")
>        Rng.Select   '-- show scope based on original range
>     Else
>         MsgBox ("Must select one or more rows for range " _
>           & "before executing command")
> 
>     End If
>     '-- the following sometimes works but apparently not here
>     J = ActiveSheet.UsedRange.Rows.Count 'see J-Walkenbach tip 73
> EndMacro:
> 
>     Application.ScreenUpdating = True
>     Application.Calculation = xlCalculationAutomatic
> End Sub
> 
> Sub InsertRow_A_Chg()
>   Application.ScreenUpdating = False
>   Application.Calculation = xlCalculationManual
>   Dim irow As Long, vcurrent As String, i As Long
>  '// find last used cell in Column A
>   irow = Cells(Rows.Count, "A").End(xlUp).Row
>  '// get value of  that cell in Column A (column 1)
>   vcurrent = Cells(irow, 1).Text
>  '// rows are inserted by looping from bottom
>   For i = irow To 2 Step -1
>      If Cells(i, 1).Text = "" Then
>        vcurrent = Cells(i - 1, 1)
>      ElseIf Cells(i, 1).Text <> vcurrent Then
>         vcurrent = Cells(i, 1).Text
>         Rows(i + 1).Insert
>      End If
>   Next i
>   Application.Calculation = xlCalculationAutomatic
>   Application.ScreenUpdating = True
> End Sub
> -----------------------------------------------------------
> 
> 
> THANKS!!!!!!!!!
0
Utf
3/19/2010 2:25:02 AM
Reply:

Similar Artilces:

Autofit Row Height #3
Hello, I have a column on sheet 1 that is set to wrap text so that the row height increases and decreases as more text is entered into the cell. This works fine. Users enter a number on sheet two where there is a lookup function that returns the appropriate text from sheet 1. The problem is that when the text is returned by the function, the row height does not adjust to fully display all of the text in the cell. Is there a way of automating this? Any help would be appreciated. -- Thanks, MarkN ...

Deleting Non-Duplicate Rows
Have done tihs is the past, but can't remember how: Have a sheet with 9500+ rows. Column C contains a storage bin number. Want to delete all rows that DO NOT have a duplicate (trying to resolve items that have a duplicate bin number.) Have sorted the sheet on Column C. THX. . . -- BillW ------------------------------------------------------------------------ BillW's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27516 View this thread: http://www.excelforum.com/showthread.php?threadid=470299 You could use a helper column of formulas: =countif(c:c,c1)...

Sheet to close
Why won't this work? oldjayo Sub test() Dim wsShtToSave As Worksheet Set wsShtToSave = ActiveSheet If wsShtToSave= "Master" Then ActiveWorkbook.Save ActiveWorkbook.Close End If End Sub Activesheet.name= try this simpler version Sub CloseifMaster() If ActiveSheet.Name = "Master" Then With ActiveWorkbook 'one dot only .Save .Close End With End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "oldjay" <oldjay@discussions.microsoft.com> wrote in message news:...

Password protect
How do I password protect a shared excel book so that people have read only access but so thta I can have write access? This should work. 1. In your shared workbook, hit F12 to bring up the 'Save As' dialog box. 2. Click Tools>General Options 3. Enter a password to modify the file. 4. Don't share the password. HTH, JP On Jan 8, 11:51=A0am, GeoSte <Geo...@discussions.microsoft.com> wrote: > How do I password protect a shared excel book so that people have read onl= y > access but so thta I can have write access? ...

Compare and Highlight Rows
I have an excel file with two worksheets. is there a way to programatically compare the two worksheets to find matches and highlight those matches on each worksheet. They both have the same columns. I want to compare the InspectionID column. The Inspection ID column may have duplicates in both of the worksheets. I have never done anything in excel above the beginner level. However, I am a very skilled VBA programmer (programmed in ACCESS for over 10 years). Thank you You may find it easier to use Conditional formatting instead: For instance, with Sheet1 column A selected, and cel...

How to make Unique coloumn in Excel sheet ?
I want to make one coloumn in excel sheet as unique, so no entry is repeated in that particular column. See http://www.contextures.com/xlDataVal07.html on this. HTH. Best wishes Harald "V JHANJI" <V JHANJI@discussions.microsoft.com> skrev i melding news:5D9465E8-EE69-4D59-8BD7-B0115D09145C@microsoft.com... > I want to make one coloumn in excel sheet as unique, so no entry is repeated > in that particular column. Hi check out http://www.cpearson.com/excel/NoDupEntry.htm for detailed instructions on how to do this. Cheers JulieD "V JHANJI" <V JHANJI...

inserting
How do you insert more than one row at a time? Hi just select some rows before inserting rows -- Regards Frank Kabel Frankfurt, Germany "aSussertown" <DROPsussertown@adelphia.net> schrieb im Newsbeitrag news:roednbCg9PBJpA_cRVn-tg@adelphia.com... > How do you insert more than one row at a time? > > Select as many rows as you wish to insert by clicking on the row number and dragging down. It doesn't matter if the rows have data or not. XL will count the selection and insert that number of rows, starting at the row number you first clicked on. -- HTH, RD =...

Protection Problem #3
I have a 3 sheet excel file with protection on all 3 sheets (let's call them A, B and C). These 3 pages have formulas that link to each other. Sheet A has a few checkboxes that link to sheet C. The problem is that when I try to check any of the check boxes on sheet A an error box pops up that says "The cell or chart you are trying to change is protected and therefore read only". Is there a way to protect a sheet while being able to check a box off that is linked to another sheet? What protection options would allow me to do this? Thanks in advance. Dave -- Dave123 ----------...

Detailed Time Sheet (overtime, comp time, vacation used)
I need to track in time, out time, in time, out time, overtime, vacation totals (received/used), comp received, comp used, and so. I would recommend to write all the headers on row 1, and start entering the data on row 2. Any column that is a calculation, enter a formula (for example, if column E is the difference of column D and C, enter "=D2-C2") and format the cells as you want the data to be presented (date/time, number, etc.) Hope this helps, Miguel. "Robert D. Sandersfeld" wrote: > I need to track in time, out time, in time, out time, overtime, vacation ...

create price list from excel sheet
I have been using Excel 2003 to manage a list of approximately 200 items. This is a very simple list comprising of four or five fields one of which is the price. I prefer to use Excel to manage this data because it then becomes very simple for me to use formulas in order to calculate prices after changes or additions to the items. I would like to produce a simple A5 flyer listing all the items together with their prices. What will be the best way to do this? I have explored the possibility of exporting the Excel sheet into something like Microsoft publisher but I am looking for a fa...

Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook?
Wow! I tried this out, and it seems to work beautifully. It's odd, though, that I haven't seen this technique mentioned in any of the several Excel references that I've looked at. Basically, I have a workbook with several worksheets...one worksheet contains a large list with all the records. I wanted to set up the other worksheets to have certain functions...I wanted them to use only certain columns from the main list, and to contain only certain records from the main list that met specified conditions. This is the best solution I have found so far - i.e., creating database q...

Password Protect a Workbook
I have a workbook that has 5 different worksheets. This workbook is saved everyday with a new date behind the name to reflect the current date data. I need to password protect the entire workbook so that it holds for each time it is saved. How do I do this? When I go to file->saveas- ..Tools-> and assign a password it works for that workbook, but when I resave the workbook with a new name, I have to reset it all. Can I automate this somehow or is there a different way to do what I am trying to do. I only want the password there so admin's can make changes. I want eve...

conditional formating: ifs and highlighting rows
my spreadsheet documents error incidents, with each row showing the date the incident was discovered (column A) and the date it was resolved (column I). it also calculates networkdays (column J) -- unless column I=0 -- and references an array of holiday dates on another sheet. i would like to create a conditional format that will identify rows with an incident, but no resolution date, then highlight the row and possibly even show "unresolved" in column J cell of that row. Thanks! Well, you have to decide which way you want to go with this. If you leave column J ...

How do I find duplicate rows in a list in Excel, and not delete it
I have a long list of data in Excel that is 3 columns wide. I need to find and save only the duplicate rows but don't want to delete them. Instead, I could delete the unique rows and keep the duplicate rows. In the customer assistance, I found out how to delete duplicate rows and save the unique rows, but this is exactly opposite of what I want to do. This is one option: =COUNTIF($A$4:$A$18,A4)>1 copy down, and use a Autofilter to find all True HTH Ola Sandstr�m Picture encl.: http://www.excelforum.com/attachment.php?attachmentid=3498&stc=1 +---------------------------...

How to type subscript and superscript in excel sheet
Hi Can anybody suggest me how to type the subscript and superscript in excel sheet?????????????????? Hi select the characters in the formula bar and goto 'Format - Cells' "Desh" wrote: > Hi > Can anybody suggest me how to type the subscript and superscript in excel > sheet?????????????????? > IF you do this a lot, John Walkenbach's free SuperSub add-in makes it much easier (it's included in his commercial PUP v5 add-in, as well): http://j-walk.com/ss/excel/files/supersub.htm In article <E060E171-0622-4DB1-8734-3B4727A3FF4E@microsoft.com&...

inserting object
i try to insert pdf file to my excel file in Insert>Object>Create from file>Browse the pdf file there an message appear when opening the inserted file-"Cannot start the source application for this object" What does it means? Do you have Adobe Reader install on your machine. If not that is wha the message is taking about. The insert object needs the applicatio that created or that can read the file you are attempting to insert. HT -- Case ----------------------------------------------------------------------- Casey's Profile: http://www.excelforum.com/member.ph...

insert a link/shortcut to a file on comuter in a contact window
I am trying to add a link or shortcut to a file on my computer in a contact window, in the lower window of the contact When I drag a file in that box it actualy creats a copy in a certain folder in Microsoft, but then its hard to find the file I want to Insert only a Link or shrtcut to the file. Can anyone have an idea? pl;ace your cursor in the white window of the Contact. Then Insert | File, single click the file you want and use the tiny drop down arrow, bottom right corner to select: Insert as Hyperlink. "Jacob" <info@kohn.ca> wrote in message news:O5g%23hYD2G...

Inserting word 'merge field' into Excel
I have data in Word merge fields that I need to put in an Excel table. Each Word file is exactly the same and there are a few hundred files. If I can't do this direct to a spreadsheet, can I do it through Access? ...

reset protection
anyway to reset a sheets protection settings to default? Thanks, Steve default meaning __________? -- Don Guillett SalesAid Software donaldb@281.com "Steven" <me@where.why> wrote in message news:vNxgc.1417$lN.1200@newsfe2-gui.server.ntli.net... > anyway to reset a sheets protection settings to default? > > Thanks, > > Steve > > back to a new XL sheet settings Steve "Don Guillett" <donaldb@281.com> wrote in message news:ujbvTBWJEHA.3040@TK2MSFTNGP09.phx.gbl... > default meaning __________? > > -- > Don Guillett > S...

Using SqlDataAdapter for Insert but not Update?
I'm having trouble getting my SqlDataAdapter to NOT overwrite existing records in my SQL table. I'd like it to perform the Insert on rows that don't exist, but not update the record if it already exists. I have it set to ContinueUpdateOnError because I don't want it to throw an exception if a row already exists, I just want to ignore it. I'm clearly missing something... On Dec 11, 4:46=A0pm, Stu <stumor...@gmail.com> wrote: > I'm having trouble getting my SqlDataAdapter to NOT overwrite existing > records in my SQL table. =A0I'd like it to pe...

Protected Mode not working??
Last week I was running IE7 and noticed that even though the "Enable Protected Mode" tick box under Tools > Internet Options > Security was properly checked the Status Bar at the bottom of the main screen said "Protected Mode: Off". So I installed IE8, but it still said the same. So I reset the security level to "Default" and re Set the tick box, clicked Apply, and restarted IE and the status bar still says the protected mode is off. What is going on here? What can I do about it. Ted Smith Vista SP1? You're logged in to the Adm...

password protect personal folders in Outlook xp
How to password protect personal folders in Outlook xp "Dolly" <anonymous@discussions.microsoft.com> wrote in message news:08b801c3b326$6312ee20$a501280a@phx.gbl... > How to password protect personal folders in Outlook xp "Dolly" <anonymous@discussions.microsoft.com> wrote in message news:08b801c3b326$6312ee20$a501280a@phx.gbl... > How to password protect personal folders in Outlook xp Right-Click on Outlook Today shortcut, choose Properties, go to Advanced and click on Change Password. Obviously the Old password will be blank! HTH >Right-Click...

Lock sheets automatically
Hi everyone, I have 26 fortnightly timesheets in a workbook for every staff and would like to lock each timesheet after some days.Can you please help me to fix my code as the follows: Select Case s_worksheet Case "TS1", "TS2", "TS3" If (DateDiff("d", B12, Date) > 6) Then s_worksheet.Unprotect "admin" s_worksheet.Range("C6:C12,D6:D12,F6:F12,G6:G10,H6:H10,I6:I10").Locked = True s_worksheet.Protect "admin" End If Case Else: End End Select Man...

Relative 'Sheet' Reference
I have a workbook, and multiple worksheets. I am referencing cells in prior sheets by "worksheet name and cell". Is there a way to make these 'references' relative, so they may be copied? thanks rob Unfortunately, there is no such thing as relative sheet referencing in Excel. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "mortini�" <rental@comcast.net> wrote in message news:kOmdnVoHw9qPNaXcRVn-rA@comcast.com... >I have a workbook, and multiple worksheets. I am referencing >cells in prior ...

Huge sheet for no reason
I have a worksheet with about 200 rows and 60 columns in one sheet and about half that in another sheet. The size of the file has become about 25MB!! It had some word emebeded object (logos) which I deleted and the size only went doen by 2 MB. If I try and export to a CSV file it takes a while, seems done but the CSV file cant be seen in the folder to which it was saved. What's going? Why is the file size so large and wht cant I save? Joel -- Sammeg Satellite PTY Ltd Po Box 43234 Industria 2042 South Africa www.samsat.co.za Tel +27114742666 Fax +27114742950 Open your spreadsh...