Jump to content

Colar só valores


Recommended Posts

Bom dia

Tenho uma planilha de Excel com montes de formatações condicionais.

O que se passa é que os meus colegas por vezes copiam dados de umas celulas para outras, e fazem o "colar" normal em vez do "colar especial">valores.

Isto leva à duplicação das formatações condicionais, e passado algum tempo a planilha fica muito pesada.

Existe alguma maneira de, ao fazermos "colar", por defeito seja só de valores?

Desde já grato pela vossa atenção e qualquer ajuda

Link to comment
Share on other sites

Olá!!!

Penso que seja isto que pretende:

Criar Module1

Option Explicit


Sub desactivarpaste()

    EnableControl 21, True   ' cut

    EnableControl 19, True   ' copy

    EnableControl 22, False   ' paste

    EnableControl 755, True  ' pastespecial

    Application.OnKey "^c", "Dummy"

    Application.OnKey "^v", "Dummy"

    Application.OnKey "+{DEL}", "Dummy"

    Application.OnKey "+{INSERT}", "Dummy"

    Application.CellDragAndDrop = False

    Application.OnDoubleClick = "Dummy"

    CommandBars("ToolBar List").Enabled = True


End Sub



Sub activarpaste()

    EnableControl 21, True   ' cut

    EnableControl 19, True   ' copy

    EnableControl 22, True   ' paste

    EnableControl 755, True  ' pastespecial

    Application.OnKey "^c"

    Application.OnKey "^v"

    Application.OnKey "+{DEL}"

    Application.OnKey "+{INSERT}"

    Application.CellDragAndDrop = True

    Application.OnDoubleClick = ""

    CommandBars("ToolBar List").Enabled = True

End Sub



Sub EnableControl(Id As Integer, Enabled As Boolean)

Dim CB As CommandBar

Dim C As CommandBarControl

  

On Error Resume Next

For Each CB In Application.CommandBars

    Set C = CB.FindControl(Id:=Id, recursive:=True)

    If Not C Is Nothing Then C.Enabled = Enabled

Next



End Sub



Sub Dummy()

   

    MsgBox "Terá que copiar em modo colar especial"

End Sub

No Vba na folha que pretende bloquear ao colar normal:

Private Sub Worksheet_Activate()
Call desactivarpaste

End Sub

Cumprimentos,

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.