Application.DisplayFormulaBar Kills CutCopyMode

[Ed Adamthwaite]

Hi all,
I have a friend with workbook that he needs to distribute and doesn't want
to have the formula bar visible to users.
When using Application.DisplayFormulaBar = False/True when switching between
sheets or workbooks, the CutCopyMode is deactivated.

Is there any way preserve the CutCopymode while doing other Events within
the Excel app?

Thanks for any replies,
Ed.

 

[Dave Peterson]

Lots (most) macros will kill the clipboard. I don't think there's a good way to
save it.

But maybe you could kill your macro if there's something in it.

something like:

If Application.CutCopyMode <> False Then
Exit Sub
End If
Application.DisplayFormulaBar = False

 

[Jim Rech]

Further to Dave, I always try to delay the copy until all events that kill
copy mode are run.

Another approach is to copy directly to the clipboard, bypassing the Excel
copy mechanism. But since this does not copy formats you lose some utility.
Also it copies formulas literally, rather than relatively as Excel does.

Sub CopyData()
Dim MyData As DataObject
Dim StrBuf As String, SrcRg As Range
Dim CurrRow As Range, CurrCell As Range
'Build a long string of cell contents (formulas)
' Tabs separate columns
' Carriage returns separate rows
Set SrcRg = Selection
For Each CurrRow In SrcRg.Rows
For Each CurrCell In CurrRow.Cells
StrBuf = StrBuf & CurrCell.Formula & Chr(9)
Next
'Remove last Tab on row and add carriage return
StrBuf = Left(StrBuf, Len(StrBuf) - 1) & Chr(13)
Next
On Error Resume Next
Set MyData = New DataObject
MyData.SetText StrBuf
MyData.PutInClipboard
End Sub


--
Jim
| Hi all,
| I have a friend with workbook that he needs to distribute and doesn't want
| to have the formula bar visible to users.
| When using Application.DisplayFormulaBar = False/True when switching
between
| sheets or workbooks, the CutCopyMode is deactivated.
|
| Is there any way preserve the CutCopymode while doing other Events within
| the Excel app?
|
| Thanks for any replies,
| Ed.
|
|