Can you store clipboard text and pick it up by copy it again?

I have a catch 22 - impossible actions here:

A/ I want to have the sheet protected so users can't ruin all cells in the
sheet
B/ I have to Unprotect the sheet in the sub event Worksheet_Activate to be
able to do some format macros
C/ I want users to allow to copy text from one sheet and paste in this one.
D/ The clipboard wipes out by the Unprotect command
E/ The user have nothing to paste in...

Is there a solution?

/Regards

---------------------------------------------------------------
------------------------- Earl Kiosterud wrote: ---------------

Imbecill,

CutCopyMode gets turned off my many things when the clipboard contains an
Excel object.  To see this, try doing a manual copy, then select a cell,
enter something, and press Enter.  Clipboard cleared.  When the clipboard
contains other stuff, like text or a graphic, this doesn't happen

You can step through your code with F8, Alt-Tabbing back to Excel to see
where the marching ants around the copied area go away.  That's where it
happened.  I think you'll have to change your code around and go copy the
stuff just before you want to paste it.  The usual construct to do it all at
once is:

Range(MyRange).Copy Destination:=Range("E5")

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net

---------------------------------------------------------------
------------------------- Imbecill wrote: ----------------------

Hello,

My problem is that in my code I unprotect the sheet and furter down the sub,
I try to pastespecial. However, the clipboard go empty when
ActiveSheet.Unprotect come...

I can't "paste first" or in an other way change order, because the
pastespecial is generated by a  Application.OnKey "^v", "FormulaPaste"
and the Sheet.Unprotect is inside an event witin another macro that starts
when you activate the sheet.

Any solution? Ive searched everywere ...




0
5/18/2004 7:23:41 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
327 Views

Similar Articles

[PageSpeed] 5

Maybe a macro that does the work for them:

Option Explicit
Sub testme01()

    Dim rngF As Range
    Dim rngT As Range
    Dim wks As Worksheet
    Dim wasProtected As Boolean

    Set wks = Worksheets("sheet1")
    
    Set rngF = Nothing
    Set rngT = Nothing    
    On Error Resume Next
    Set rngF = Application.InputBox(prompt:="Copy from where", _
                   Default:=Selection.Address(0, 0), Type:=8)
    If rngF Is Nothing Then
        Exit Sub
    End If
    Set rngT = Application.InputBox(prompt:="Where to paste", _
                     Type:=8).Cells(1, 1)
    If rngT Is Nothing Then
        Exit Sub
    End If
    On Error GoTo 0
    
    On Error GoTo errHandler:
    If rngT.Parent.Name = wks.Name Then
        If wks.ProtectContents Then
            wks.Unprotect Password:="hi"
            wasProtected = True
        End If
        rngF.Copy _
            Destination:=rngT
        Application.Goto reference:=rngT
    End If
            
errHandler:
    If wasProtected Then
        wks.Protect Password:="hi"
    End If
End Sub


Imbecill wrote:
> 
> I have a catch 22 - impossible actions here:
> 
> A/ I want to have the sheet protected so users can't ruin all cells in the
> sheet
> B/ I have to Unprotect the sheet in the sub event Worksheet_Activate to be
> able to do some format macros
> C/ I want users to allow to copy text from one sheet and paste in this one.
> D/ The clipboard wipes out by the Unprotect command
> E/ The user have nothing to paste in...
> 
> Is there a solution?
> 
> /Regards
> 
> ---------------------------------------------------------------
> ------------------------- Earl Kiosterud wrote: ---------------
> 
> Imbecill,
> 
> CutCopyMode gets turned off my many things when the clipboard contains an
> Excel object.  To see this, try doing a manual copy, then select a cell,
> enter something, and press Enter.  Clipboard cleared.  When the clipboard
> contains other stuff, like text or a graphic, this doesn't happen
> 
> You can step through your code with F8, Alt-Tabbing back to Excel to see
> where the marching ants around the copied area go away.  That's where it
> happened.  I think you'll have to change your code around and go copy the
> stuff just before you want to paste it.  The usual construct to do it all at
> once is:
> 
> Range(MyRange).Copy Destination:=Range("E5")
> 
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> 
> ---------------------------------------------------------------
> ------------------------- Imbecill wrote: ----------------------
> 
> Hello,
> 
> My problem is that in my code I unprotect the sheet and furter down the sub,
> I try to pastespecial. However, the clipboard go empty when
> ActiveSheet.Unprotect come...
> 
> I can't "paste first" or in an other way change order, because the
> pastespecial is generated by a  Application.OnKey "^v", "FormulaPaste"
> and the Sheet.Unprotect is inside an event witin another macro that starts
> when you activate the sheet.
> 
> Any solution? Ive searched everywere ...

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/19/2004 12:56:45 AM
Reply:

Similar Artilces:

How can I know which SP has been put on the exchange2K
Where can I see this msg? Thanks Open exchange manager, organization, right pane. "Geoffrey" <yile1978@hotmail.com> wrote in message news:uvLLBUYwFHA.3312@TK2MSFTNGP09.phx.gbl... > Where can I see this msg? > Thanks > check www.petri.co.il or check for the answer on the 10th(9th from ur posting)thread on this site,that is named"finding on version of sp installed" ...

Public information store
Exchange 2K-Under the Properties for a new public folder I have created, the Directory Rights tab is grayed out. Any input would be appreciated -- Joe M On Tue, 12 Jul 2005 12:01:03 -0700, "Joe M" <JoeM@discussions.microsoft.com> wrote: >Exchange 2K-Under the Properties for a new public folder I have created, the >Directory Rights tab is grayed out. > >Any input would be appreciated Its grayed out until you mail-enable it. ...

Save formatted text from RichEdit control to rtf-file
Hi , How can I save the text from Rich edit control (2.0) to *.rtf , *.txt , *.doc I tried to get the buffer and putting the buffer to file, then saving the file but the text in the file is something different. Please let me know what to do? Here is the Code I ma using: mFile.Seek( 0, CFile::begin ); CString cBuffer2; int iTotalTextLength = m_oChatMessageControl.GetWindowTextLength(); HWND focusWnd = ::GetFocus(); m_oChatMessageControl.HideSelection(TRUE, TRUE); m_oChatMessageControl.SetSel(iTotalTextLength, iTotalTextLength); cBuffer2 = m_oChatMessageControl.GetSelText(); LPTSTR...

How can I print just the flip side of a booklet?
How can I print just the flip side of a booklet? I printed the first side just fine and then my computer went funny and I had to shut down and start back up again. I have 25 copies of the first side that need to be printed on the back side. Pub 2000 user here: There may be a better way but I think you will have to print a page at a time i.e., File, Print Page 2 of 2; then 3 of 3 and so on. -- Don Vancouver, USA "pqc18" <pqc18@discussions.microsoft.com> wrote in message news:92653E8D-9772-4AE0-A9C3-514B363E72B2@microsoft.com... > How can I print just the flip side ...

outlook can't receive exe files
A guy here at work can't get exe files through his outlook. Is there a check to uncheck somewhere to allow it to do this? He can receive normal attachments. ...

Create static text from cell reference
Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

Copy constructor
Dear people, I have a class C1 which contains a member variable of class C2. C2 didn't have a copy constructor up to now but due to a change needed (and got) one, and an assignment operator as well. Now when compiling, I get en error C2558: "class C1: No copy constructor available or copy constructor has been declared 'explicit'". It is correct that C1 doesn't have a copy constructor but I thought that the default copy constructor just calls the assignment operators of the members. Since C2 has an explicitly programmed assignment operator - where is the mi...

How Can I Control When Messages are Downloaded from Server
I have the box "Check for messages every ___ minutes" unchecked (tools/options/general). Yet seemingly randomly, messages get downloaded a couple of times a day without my clicking "Sync." This is problematic due to the way I share the computer. Is there another setting I need to keep this spontaneous downloading from occurring? "Gripper" <Gripper@discussions.microsoft.com> wrote in message news:F22A9D3A-AFB5-4C85-B94B-79B109E4829E@microsoft.com... > I have the box "Check for messages every ___ minutes" unchecked > (too...

copy does not work in word 2003
I can't seem to copy from word 2003. I select text but it won't take it. I can copy from an e-mail and paste into word, but word doesn't seem to want to copy, either from one word document to another or into my e-mail. It's been working fine for almost four years, it just acted up. Any suggestions? The usual reason is some third party add-in that is messing with the Office clipboard. If you start Word in Safe Mode, can you copy/paste normally then? From Start | Run (Winkey+R), type in winword /a and press Enter. Word will start in Safe Mode. Open two doc...

"x" button enables deletion.... can we put a focus trigger there?
Hi, on three forms, I put focus triggers on the "delete" button because I need to validate some additional data before allowing the user to delete. However, if the user clicks on the corner "x" button to close the form and gets the dialogue box saying they have changes, do they want to "Save", "Delete" or "Cancel", they can delete the record anyways. Is there any way to put a focus trigger on the button of the dialogue box, or on the "x" button? Thanks in advance. I assume you are working with Dexterity. To work with an add...

How can I sort duplicate text data in excel?
I have a large list of noames that I need to make sure that none of them are duplicated. Is there a way to have excel check it quisker than me reading every name until I find a duplicate? After selecting your data go to filter Advanced filter and check "Unique records only" You can even copy it to another area all uniques entries if you want to ... "TinaScheu" <TinaScheu@discussions.microsoft.com> wrote in message news:0399D580-7E69-4DF0-A969-E7FC5F777C70@microsoft.com... >I have a large list of noames that I need to make sure that none of them >are >...

Catch all outgoing email on Exchange and send copy to specified account
Hi all. I have been asked to find out if there is anyway to configure our mail server to catch all outgoing email from certain email accounts on our Exchange 2003 Enterprise server and forward a copy of this email to a specified account, also on the Exchange server. If anyone could point me in the right direction I'd be grateful. On 25 Oct 2006 04:10:54 -0700, googlegroups@tlbsolutions.com wrote: >Hi all. > >I have been asked to find out if there is anyway to configure our mail >server to catch all outgoing email from certain email accounts on our >Exchange 2003 Enterpr...

manufacturing scrap not calculating on pick list and serial #'s no
I have entered my percentage of scrap on a MFG BOM and is seems to be calculating properly in the setup. When I release the components and add them to a pick list the scrap is not calculating. I also choose serial numbers and they do not carry thorugh and print on the pick list. Is there some setting that I have missed? -- Paula ...

Information Store Corrupted
Guys,, I think I'm in very deep trouble. I have two Exchange 5.5, one for outgoing mail and one for incoming, the incoming mail sever is a W2K and is the Domain Controller. My DNS server is a separated dedicated server. I had a big power failure last thursday night. The three server sudently shut off and restart again after the AC came back. On friday morning I realized we didn't have email service, I checked the outgoing server and it was OK, but on the incoming server the Information Store Service was not starting, I went to the web and found this article about how to reco...

Text Very Small When Replying
I'm able to change the text size when I'm replying to an email but how does one set the default size? No matter what I ser the font size to when replying it always reverts back to the smallest size when I do another reply. Look, I'm 54! I can't read that small text! James Are you using stationery? Also, Alt + T, Options, Compose,....... -- Ron Sommer MS MVP-Mail "JamesJ" <jjy@darwin_roadrunne0r.com> wrote in message news:OGt2E1OhKHA.1460@TK2MSFTNGP06.phx.gbl... > I'm able to change the text size when I'm replying to an emai...

Extract text from field
If you have a filed that contatins the following data:LastName, FirstNameWhere LastName has varing lengths. Can you run an update query to obtain just the LastName part of the field? If so, what would be the command?Many, many thanks in advance. On Tue, 6 Mar 2007 14:09:45 -0500, "Mary M" <none@no_email.com> wrote:>If you have a filed that contatins the following data:>>LastName, FirstName>>Where LastName has varing lengths. Can you run an update query to obtain >just the LastName part of the field? If so, what would be the command?>>Many, many thanks in...

Can't upgrade companies to SP4
R [Microsoft][ODBC SQL Server Driver][SQL Server]Insert Error: Column name or number of supplied values does not match table definition.I had a base installation of GP 9.0, no patches. Decided to download SP4 and install. Install of the MSI went fine and upgrading the master databases worked, but going company by company, I get the following error: The following SQL statement produced an error: CREATE Procedure SVC_POP_Make_PO( @PromisedDate datetime, @OrderedBy char(15), @BatchNum char(15), @ItemNumber char(30), @VendorItemNumber char(30), @LocationCode char(10), @Uo...

Can't receive EMail #5
Keep getting this pop up along with 'server did not connect within 60 seconfs, do I want to wait A time-out occurred while communicating with the server. Account: 'Richard', Server: 'pop.east.cox.net', Protocol: POP3, Port: 110, Secure(SSL): No, Error Number: 0x800CCC19 Please reply using lmmurray@cox.net Thanks! Hi RASZ, please read this information from Microsoft http://support.microsoft.com/default.aspx?scid=kb;EN-US;813518 -- Ich habe nichts gemacht, gestern gings noch! Bitte in den Newsgroup antworten damit jeder etwas davon hat. Bravestar@Datenschutzminis...

Can you create custom activities? MSCRM 3.0
Hi, Is there a way to create a new custom activity instead of customising an existing one? I have created a custom entity called 'Chat' utilising an IFRAME. All works well but this entity really should be an activity considering it's properties. In fact I've just been introduced to MS-CRM 3.0 and don't really understand what the difference is between an entity and activity. Would anyone shed the light for me? BTW, I think 3.0 looks great. Gotta admit it's improved. Cheers. Ty In my experience, you cannot create custom activities. In fact, I have been dire...

Excel 2002 converts 'S' to 0 when pasting from Clipboard
I came across the following problem: I copied some tabular data from IBM Personal Communications into the clipboard (yes, I am still a user of good old 3270 applications). Then I pasted the data into Microsoft Excel 2002 and all cells containing a 'S' became a '0' (number zero). Next I did some tests and found out that every single uppercase 'S' that is transferred to Excel using copy/paste is translated to '0'. This would not happen with other letters or with words containing an 'S'. Using 'Paste special' I can choose to insert my Clipboard a...

Office 2004 issue with documents NOT BEING ABLE TO OPEN APPLICATION, but application can open documents.
BACKGROUND: Just migrated all my files and apps from a G4-400 to a new Mac Mini 1.87ghz Intel running pre-installed OSX10.4.10. Used CD to install 'normal' version of Office 2004 Mac on the new Mac Mini. In fact its the same disk that I used originally to install Office on the G4-400. PROBLEM: Neither old .xls and .doc documents (made on old Mac, nor new .xls and ..doc documents (made on new Mac)will not open their respective applications, when clicked upon. ADDITIONAL INFO: However, when I use either of the the application's "Open" feature, theres is no pr...

copying formulas in vba
Hey guys. I was wondering if someone could help me. I am writing a vba script that takes in data, analyzes it, and then copies the results to a new file. I am having a problem with two things. 1) I am using a template for the new file so there are a lot of formulas (sums and std) already defined and ready to use. However, there are some instances where there is a random amount of additional data I have to put in. So, I have to apply the same formulas to this new data. How do I copy formulas from one cell to another (allowing for a change in row) in vba? Lets say cell(1,4) has the form...

Can I do this query in one step?
Suppose I have a talble like this, each record has a unique ID. All"A"s or "B", "C"s should have only x or y or z property attached. Iwant to find A, B or C which has more than one properties.1 A x ...2 A x ...3 A x ...4 A y ...5 B x6 B y7 C z....resutls would look like:A xA y....I think I can do this in two steps or with a subquery. Then I thoughtof self-join but didn't figure out how to use it in this case.Thanks a lot! SELECT DISTINCT and do not include the record ID perhaps? Or is the record ID vital?"muster" <muster@gmail.com> wrote in me...

How many pages can a booklet have?
Hi how many pages can a booklet have? Is there a limit to the pages in a publisher booklet. Each page has three photos. There is no limit, your RAM may decide for you. What version Publisher? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Verystumped" <Verystumped@discussions.microsoft.com> wrote in message news:8F2778CF-7B14-4BDD-93C5-EF9B39E2C1A7@microsoft.com... > Hi how many pages can a booklet have? Is there a limit to the pages in a > publisher booklet. Each page has three photos. ...

Adding a certain text label in a excel chart
I am plotting in regularly basis a certain set of data in excel. Based on some data analysis this set of data has to be fitted to these equations: y = 1/x^a (1) and /or y = b/x^c (2) from data analysis, constants a, b and c are found and are placed lets say in cells A1, B1, C1. On my graph, I am putting then two small text labels where the real equation is displayed: smth. like: y = 1/ x^3.45 and / or y = 0.256 / x^3.12 The whole process is similar with excel curve fitting, when the “show equation on chart” is checked. Thank you in advance My question is: Can ...