Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Restrict use of macro (Excel 2002)

    We have a simple macro to insert a new row.

    Sub InsertRow()
    '
    ' InsertRow Macro
    ' Macro recorded 01/08/2004 by Mike Schindhelm
    '
    ' Keyboard Shortcut: Ctrl+i
    '
    UnProtect
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Protect
    End Sub

    We would like to restrict the use of the macro to only certain worksheets. We currently have a warning to users to only insert rows on detail worksheets, but we would like to prevent the accidental use of the macro on the summary worksheets.

    We are not experts (more like novices) in the writing of macros, so any help is greatly appreciated.

    TOA

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restrict use of macro (Excel 2002)

    How can the detail and summary sheets be identified? Is there something in the sheet name that makes it a summary or detail sheet?
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Restrict use of macro (Excel 2002)

    The detail worksheets names have numbers but the summary worksheets don't have any digits in their names.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Restrict use of macro (Excel 2002)

    Try this version:

    Sub InsertRow()
    Dim i As Integer
    Dim strName As String
    strName = ActiveSheet.Name
    For i = 1 To Len(strName)
    If IsNumeric(Mid(strName, i, 1)) Then
    Beep
    Exit Sub
    End If
    Next i

    ActiveSheet.Unprotect
    ActiveCell.Rows("1:1").EntireRow.Copy
    Selection.Insert Shift:=xlDown
    ActiveSheet.Protect
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Restrict use of macro (Excel 2002)

    Thanks, Hans, but it doesn't seem to work in our workbook.

    The worksheet names are "San 1271" or "Water 1273" or Storm 1272" etc. The summary worksheets are named "Bid Tab" or "Bid Schedule" or "PRINTING." My guess is that the space in the worksheet name is why the macro doesn't work for me.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Restrict use of macro (Excel 2002)

    That shouldn't matter. Does this work better?

    Sub InsertRow()
    Dim i As Integer
    Dim strName As String
    strName = ActiveSheet.Name
    For i = 1 To Len(strName)
    If IsNumeric(Mid(strName, i, 1)) Then
    Beep
    Exit Sub
    End If
    Next i

    ActiveSheet.Unprotect
    ActiveCell.EntireRow.Copy
    ActiveCell.EntireRow.Insert Shift:=xlDown
    ActiveSheet.Protect
    End Sub

  7. #7
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Restrict use of macro (Excel 2002)

    Sorry, Hans, I still don't get anything to happen on the worksheet with numbers in the name. The macro does insert a row for pages without numbers in the worksheet name. This is just the opposite of what we want to happen.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Restrict use of macro (Excel 2002)

    Please accept my apologies, that's my mistake, I had reversed the conditions. I hope that this works better:

    Sub InsertRow()
    Dim i As Integer
    Dim blnOK As Boolean
    Dim strName As String
    strName = ActiveSheet.Name
    For i = 1 To Len(strName)
    If IsNumeric(Mid(strName, i, 1)) Then
    blnOK = True
    Exit For
    End If
    Next i

    If blnOK = False Then
    Beep
    Exit Sub
    End If

    ActiveSheet.Unprotect
    ActiveCell.EntireRow.Copy
    ActiveCell.EntireRow.Insert Shift:=xlDown
    ActiveSheet.Protect
    End Sub

  9. #9
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Restrict use of macro (Excel 2002)

    Thanks, Hans.

    The macro works great!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •