Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    New Lounger
    Join Date
    Mar 2006
    Location
    vancouver, Washington, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro HELP Please (2000)

    I'm trying to create a MACRO where I want to:
    1) Unprotect the worksheet.
    2) Insert a row.
    3) Copy a value from another cell (let's say from D3) into a cell in that new row in column D.
    4) Unprotect certain cells in the new row.
    5) Protect the worksheet.
    6) Stop.

    My problem is step 3 and 4.

    It seems that the MACRO only works for the row where I created the MACRO.

    Later, when I try to run the macro from another position (a different row).... steps 1,2 and 5 work, but not 3&4.

    Help please.

    Thanks,
    Steve

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

    Re: Macro HELP Please (2000)

    Welcome to Woody's Lounge!

    Could you post the code you now have, or at least the part that is relevant to the problem?

  3. #3
    New Lounger
    Join Date
    Mar 2006
    Location
    vancouver, Washington, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro HELP Please (2000)

    Here is my script.....

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

    Re: Macro HELP Please (2000)

    Could you please copy and paste the code into a reply? I'm too lazy to type it in myself from your screenshot.

  5. #5
    New Lounger
    Join Date
    Mar 2006
    Location
    vancouver, Washington, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro HELP Please (2000)

    here is the actual syntax:
    Sub InsertRow()
    '
    ' InsertRow Macro
    ' Macro recorded 3/22/2006 by pratt
    '
    ' Keyboard Shortcut: Ctrl+i
    '
    ActiveSheet.Unprotect
    Selection.EntireRow.Insert
    Range("D28").Select
    ActiveCell.FormulaR1C1 = "=R[-25]C"
    Range("A28").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("B28").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("F28").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("H28").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("K28").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub

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

    Re: Macro HELP Please (2000)

    Try this version. It stores the selected row in a variable, and uses that in the rest of the code:

    Sub InsertRow()
    '
    ' InsertRow Macro
    ' Macro recorded 3/22/2006 by pratt
    '
    ' Keyboard Shortcut: Ctrl+i
    '
    Dim lngRow As Long
    lngRow = Selection.Row
    ActiveSheet.Unprotect
    Rows(lngRow).Insert
    Range("D" & lngRow).FormulaR1C1 = "=R<!t>[-25]<!/t>C"
    Range("A" & lngRow).Locked = False
    Range("B" & lngRow).Locked = False
    Range("F" & lngRow).Locked = False
    Range("H" & lngRow).Locked = False
    Range("K" & lngRow).Locked = False
    ActiveSheet.Protect DrawingObjects:=True
    End Sub

  7. #7
    New Lounger
    Join Date
    Mar 2006
    Location
    vancouver, Washington, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro HELP Please (2000)

    Hans,

    If I use the pulldowns....Tools > Macro > Macros > Run.. it works. For some reason, my shortcut (Cntrl +i) just beeps.

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

    Re: Macro HELP Please (2000)

    Select Tools | Macro | Macros...
    Select InsertRow.
    Click Options... to check whether Ctrl+i is still assigned to the macro.

  9. #9
    New Lounger
    Join Date
    Mar 2006
    Location
    vancouver, Washington, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro HELP Please (2000)

    Hans,

    I went back into the spreadsheet and using the pulldowns, selected Tools > Macros > Macro > Options and noticed that the keyboard shortcut was blank... even though it WAS in the script. I entered the key "i" and now the short cut works fine. Pretty weird, since it was already in the script.

    I reviewed the script after doing this and it looks exactly the same.

    Anyway........ MANY thanks for your help. Don't go away......... I'm sure I'll be back! <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

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

    Re: Macro HELP Please (2000)

    The shortcut was probably removed when you copied and pasted the code, so you had to set it again.

  11. #11
    New Lounger
    Join Date
    Mar 2006
    Location
    vancouver, Washington, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro HELP Please (2000)

    Hans,
    It works great now. Thanks again my man............ <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  12. #12
    New Lounger
    Join Date
    Mar 2006
    Location
    vancouver, Washington, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro HELP Please (2000)

    Hans......

    Well, I told you I'd be back........ HA!

    Seems I've hacked a bit too much in one of my macros.

    I saved my file before going to lunch and now I can't open it. It opens........ I mean I can see my data...... my the "hour glass" never goes away and the title bar at the top of the spreadsheet says "Not Responding".

    Any clues?

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

    Re: Macro HELP Please (2000)

    I hope your workbook hasn't become corrupt. Can you still close the workbook? If not, you'll have to use the Windows task manager to do so.
    Then try to open the workbook with the Shift key held down. Does that help?
    If not, see Jan Karel Pieterse's troubleshooting guide Corrupt Files.

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

    Re: Macro HELP Please (2000)

    It sounds like an event macro may be looping. Open Excel without the file in question. Select Open from the file menu and then navigate to the file. Hold down the Shift key and Open the file. That should keep any macros from running.
    Legare Coleman

  15. #15
    New Lounger
    Join Date
    Mar 2006
    Location
    vancouver, Washington, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro HELP Please (2000)

    Hans,

    I see you live in the Netherlands.... so assumed you went home for the day. I made a backup last night before I went home... so I just started over. It's basically back to where I was before I hacked the macro... so I'm cool. I'll check out the link that you sent though...... sounds interesting. Thanks again.

Page 1 of 2 12 LastLast

Posting Permissions

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