Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have the following piece of code attached to my Excel worksheet:

    ActiveSheet.Shapes("Object 407").Select
    Selection.Copy
    Range("BP106").Select
    ActiveSheet.Paste
    The code is activated by making a choice in a drop down in the worksheet. The worksheet is password protected but the cell BP106 is not locked.

    When I make a selection from the drop down, the shape is not being pasted into BP106. If I go to a new worksheet I can do a paste command and the shape is pasted.

    If I put a breakpoint in the code, and manually step through the code, the shape is pasted into PB106 with no problem.

    I can't figure out why it works when I manually step through the code but not when the code runs automatically.

    Any helpful thoughts?

    Thanks!

    PS: I also posted this question on June 28 on another site with no responses, so thought I would try here.

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Not sure why, but it works OK if the sheet is NOT protected.
    Try Turning Off Protection just before the Paste
    Then Back on after

    conPWD is a variable containing the Password.
    For safety you could make it a Constant and store it in another Module

    Const conPWD="your password"


    Code:
    ActiveSheet.Shapes("Object 407").Select 
    Selection.Copy
    Range("BP106").Select
    Activesheet.Unprotect conPWD
    ActiveSheet.Paste 
    Activesheet.Protect conPWD
    When I used this technique it worked fine on a password protected sheet.
    Andrew

  3. #3
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Adding the unprotect/protect didn't help on my spreadsheet.

    What I did find though --
    The shape I'm copying is at the very bottom of the worksheet
    When the paste isn't showing up in cell BP106, I found that the paste is being done at the bottom of the worksheet in the same location it is copying the shape from.
    So the selection of the cell BP106 isn't working, unless I step through the code manually.

    I still have not figured out why, but at least I now know it is the selection of the cell that is not working.

    Anyone have any thoughts on what I am missing here?

    Thanks,
    Patty

  4. #4
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I do have a split screen spreadsheet. The bottom half has input fields that feed into the top half, and the screen is split so the users can see both sections of the worksheet at the same time.

    I put a breakpoint at the very last line of code (which moves the cursor to another cell input cell in the worksheet, after the shape has been pasted), and notice that if I scroll the bottom half of the split screen to show PB106 the cell is selected but the shape is not pasted into it. The shape is pasted at the location of the original shape. BP106 in the top half of the split screen does not show selected at this same point.

  5. #5
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Never mind the split screen confusion. I took out the split screen with the same result. It pastes the shape in cell BP106 if I go through the code manually. If I don't set a breakpoint and let it run, the shape is pasted at the original location instead of in BP106.

    I tried putting in msgboxes before and after the select and paste commands, to see if slowing it down made any difference. It didn't. It still didn't paste into BP106.

  6. #6
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I found a way to make it work!

    I moved my code around to look like the following:
    Worksheets("front page").Range("BP106").Select 'selects the range to be copied into before copying the shape
    ActiveSheet.Shapes("Object 407").Copy 'copy the shape without selecting it
    ActiveSheet.Paste 'then the shape pastes into BP106 whether running automatically or manually stepping through this code

    Hope I haven't wasted anyone's time. Persistence has paid off.

    Thanks to all who have looked at and contemplated this problem for me.

Posting Permissions

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