MS Excel 2003 suddently only copies values and not formatting

pg111
pg111 used Ask the Experts™
on
Hi everybody,

Suddenly my Excel 2003 only copies values of selected cells and not their formatting. Moreover Insert cut/copied cells option is not any longer available in the shortcut menu after a cutting/copying operation.

I'm wondering what might have caused such behavior. I do not recall having changed my Excel configuration in whatsoever manner recently.

Can someone put me on the right track to solve this annoying issue?

Many thanks in advance.

Cheers.

PG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
Does this happen from one workbook to another?

If it does, you might just have two workbooks opened in two different instances of excel, in which case the clipboard is no longer the excel clipboard, but the windows clipboard which drops formatting (and formulas, etc).

To check that, try entering a formula that references the other workbook. If you can't, you have two separate instances of excel opened. Close one workbook, then go Ctrl+O to reopen it from the remaining excel window.

If that doesn't fix it, let us know,

Thomas

Author

Commented:
Hi Thomas,

Thanks a lot for your prompt response.
I just double checked from Windows Task Manager that only one Excel instance is open.

However I feel you're correct: the issue has certainly to do with Clipboard.

As a symptom, no blinking border around the copied cells.

Is it a clue for you?

Many thanks in advance.
Top Expert 2008

Commented:
Do you have a worksheet_change macro active? If you right-click the tab, and click view code, do you have anything there?

Any way you can upload the file (after removing any confidential information, if applicable)?

Thomas
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Author

Commented:
Sure, Thomas.

Yes, I also suspected my worksheet_change codes to be the culprit, but what is strange is that I've been using them for a while, and the issue occurred only yesterday.

May I send you the file by private message?
Top Expert 2008

Commented:
Not ideal, can you just post the code maybe?

Author

Commented:
OK. I will do.

Thanks.

Do you mean that an export of VBA procedures will be sufficient or do you need the whole workbook?
Top Expert 2008

Commented:
just cut and paste the worksheet module content in the comment window

Author

Commented:
Here are all the procedures of the worksheet module. I'm not sure that all of them are relevant.



Private Sub Worksheet_Activate()

'On Error Resume Next

With Application
    .CommandBars("PW").Visible = True
End With

Feuil2.AddItemToContextMenu_MontantFacturé

End Sub

Private Sub Worksheet_Deactivate()

'On Error Resume Next

With Application
    .CommandBars("PW").Visible = False
End With

Feuil2.AddItemToContextMenu_Suppr_MontantFacturé

End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

ValiderSansChanger

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

On Error Resume Next

Dim Cbc As CommandBarButton
Application.CommandBars.FindControl(, , "GénérerNomFichierZipPW").Delete

Dim custmenuItem1 As CommandBarButton
Dim custmenuItem2 As CommandBarButton

With Application.CommandBars("cell")

'L'on teste que l'on se trouve bien dans la bonne colonne
actcell = ActiveCell.Address
col = ActiveCell.Column

If col < 13 Or col > 14 Then End

    Set custmenuItem1 = .Controls.Add(before:=1)
    With custmenuItem1
        .Caption = "Générer nom fichier &zip PW"
        .OnAction = "!Feuil24.CreateZipPW"
        .Tag = "GénérerNomFichierZipPW"
    End With
    
    CommandBars("cell").Controls(2).BeginGroup = True
    
End With

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

col = ActiveCell.Column

If col = 19 Then Exit Sub 'La proc ne s'applique pas si l'on est dans la colonne S ("Montant facturable").

RecopierpgWC
SmartMove

End Sub

Sub ValiderSansChanger()
'Cette procédure permet d'effectuer la proc SmartMove sur la cellule dans la colonne H même si la valeur de cette cellule n'a pas été modifiée
'(cas où l'on accepte le décompte IBM) ou sur la cellule de la date de livraison (cas de projets NF).
'Elle est appelée par l'événement double-clic sur une cellule de la feuille

col = ActiveCell.Column

If col = 8 Then
    Selection.Cells(1, 3).Select
ElseIf col = 20 Then
    Selection.Cells(1, -14).Select
End If

End Sub

Sub RecopierpgWC()

On Error Resume Next

col = ActiveCell.Column

Application.EnableEvents = False

If col = 6 Then
    If ActiveCell.Offset(0, 2).Value = 0 And ActiveCell.Offset(0, 4) = 0 And ActiveCell.Offset(0, -6) <> "" Then
        ActiveCell.Offset(0, 2).Value = ActiveCell.Value
        ActiveCell.Offset(0, 4).Value = ActiveCell.Value
    End If
End If

If col = 8 Then
    If ActiveCell.Offset(0, -7) <> "" Then ActiveCell.Offset(0, 2).Value = ActiveCell.Value
End If

Application.EnableEvents = True

End Sub

Sub CreateZipPW()
'
' Cette macro permet de générer à partir des infos de date, de projet et d'urgence _
  un titre pour le fichier zip de livraison des PW

Dim MesDonnées As New DataObject

Dim w As String
Dim f As String
Dim col As Variant
Dim d As Variant
Dim ma As String
Dim p As String
Dim u As String
Dim aa As Variant
Dim mm As Variant
Dim jj As Variant
Dim tz As String
Dim répPWàzipper As String
Dim titrePWzip As String
Dim actcell As String
Dim ractcell As String
Dim sr As String


'L'on teste que l'on se trouve bien dans le bon classeur
w = ActiveWorkbook.Name

If w <> "Activité pg pro.xls" Then
    m = MsgBox("Vous ne vous trouvez pas dans le bon classeur." & Chr(13) & "Activez le bon classeur (Activité pg pro.xls)" & Chr(13) & "et relancez la macro.", vbOKOnly, "Création d'un fichier zip")
    End
End If

'L'on teste que l'on se trouve bien dans la bonne feuille
f = ActiveSheet.Name

If InStr(f, "Activité Trad. PWD") = 0 Then
    m = MsgBox("Vous ne vous trouvez pas dans une feuille de gestion de projet de traduction." & Chr(13) & "Passez à l'une de ces feuilles et relancez la macro.", vbOKOnly, "Création d'un fichier zip")
    End
End If

Application.ScreenUpdating = False

'L'on teste que l'on se trouve bien dans la bonne colonne
actcell = ActiveCell.Address
col = ActiveCell.Column

If col < 13 Or col > 14 Then
    m = MsgBox("Vous ne vous trouvez pas dans la bonne colonne." & Chr(13) & "Passez à la colonne M ou à la colonne N et relancez la macro.", vbOKOnly, "Création d'un fichier zip")
    End
End If

ColCount = Selection.Columns.Count
If ColCount <> 1 Then
    m = MsgBox("La sélection contient plusieurs colonnes." & Chr(13) & "Toutes les cellules sélectionnées doivent se trouver dans une seule colonne." & Chr(13) & "Passez à la colonne M ou à la colonne N et relancez la macro.", vbOKOnly, "Création d'un fichier zip")
    End
End If

'RowCount = Selection.Rows.Count
'If RowCount <> 1 Then
'    m = MsgBox("La sélection contient plusieurs lignes." & Chr(13) & "Ne sélectionnez qu'une seule cellule et relancez la macro.", vbOKOnly, "Création d'un fichier zip")
'    End
'End If
'RowCount = Selection.Rows.Count
            
'Un seul job est sélectionné

If col = 13 Then 'Si l'on est dans la colonne Sent for rev
    J = ActiveCell.Offset(0, -12).Value 'L'on récupère la date des jobs
    
    ma = ActiveCell.Offset(0, -11).Value 'L'on récupère les éventuelles infos AM/PM (cas des jobs du vendredi)
    p = ActiveCell.Offset(0, -10).Value 'L'on récupère la nature des jobs (HW/ISV/SW)
    u = ActiveCell.Offset(0, -9).Value 'L'on récupère l'urgence des jobs (F/NF/W)

    sr = ""
ElseIf col = 14 Then 'Si l'on est dans la colonne Proofread by Lnt/pg
    J = ActiveCell.Offset(0, -13).Value 'L'on récupère la date des jobs
    
    ma = ActiveCell.Offset(0, -12).Value 'L'on récupère les éventuelles infos AM/PM (cas des jobs du vendredi)
    p = ActiveCell.Offset(0, -11).Value 'L'on récupère la nature des jobs (HW/ISV/SW)
    u = ActiveCell.Offset(0, -10).Value 'L'on récupère l'urgence des jobs (F/NF/W)

    sr = ActiveCell.Value 'L'on récupère le statut éventuel de la relecture (R/A)
End If

If J = "" Then
    m = MsgBox("La date du job est vide. Choisissez une autre ligne et relancez la macro.", vbOKOnly, "Création d'un fichier zip")
    End
End If

aa = Year(J)
mm = Month(J)
If mm < 10 Then mm = "0" & mm
jj = Day(J)
If jj < 10 Then jj = "0" & jj

d = aa & mm & jj

If sr <> "" Then
    If sr = "R" Then sr = "_relu"
    If sr = "A" Then sr = "_autorelu"
End If
    
If ma <> "" Then
    tz = d & "_" & ma & "_" & p & "_" & u & "_pg" & sr
Else
    tz = d & "_" & p & "_" & u & "_pg" & sr
End If

titrePWzip = CStr(tz) & ".zip"

MesDonnées.SetText titrePWzip
MesDonnées.PutInClipboard

m = MsgBox("Le titre " & titrePWzip & " de l'archive zip a été copié vers le Presse-papiers.", vbOKOnly, "Génération de titre zip")

RetVal = Shell("c:\windows\explorer.exe /root, D:\Traductions\9.Fichiers_IBM-TM_a_envoyer\!PWD", vbMaximizedFocus)

End Sub

Sub SaisieEnMaj()

col = ActiveCell.Column
s = ActiveCell.Value

If col = 2 Or col = 4 Then
    t = UCase(s)
    ActiveCell.Value = t
End If

End Sub

Sub FiltrerFlashWeb()
'Cette macro permet de n'afficher que les jobs PW Flash ou Web

'L'on teste que l'on se trouve bien dans le bon classeur
w = ActiveWorkbook.Name

If w <> "Activité pg pro.xls" Then
    m = MsgBox("Vous ne vous trouvez pas dans le bon classeur." & Chr(13) & "Activez le bon classeur (Activité pg pro.xls)" & Chr(13) & "et relancez la macro.", vbOKOnly, "Filtrage jobs Flash/Web")
    End
End If

'L'on teste que l'on se trouve bien dans la bonne feuille
f = ActiveSheet.Name

If InStr(f, "Activité Trad. PWD") = 0 Then
    m = MsgBox("Vous ne vous trouvez pas dans une feuille de gestion de projet de traduction." & Chr(13) & "Passez à l'une de ces feuilles et relancez la macro.", vbOKOnly, "Filtrage jobs Flash/Web")
    End
End If

Selection.AutoFilter Field:=4, Criteria1:="=Flash", Operator:=xlOr, Criteria2:="=Web"

End Sub

Sub FormatPWPM()
'Cette macro permet d'appliquer le format "dddd d mmmm yyyy ""12:00" pour les PW du vendredi après-midi

Selection.NumberFormat = "dddd d mmmm yyyy ""12:00"""

End Sub

Sub OuvrirStatsPWD()
'Cette macro permet d'ouvrir le fichier des statistiques PWD

Workbooks.Open ("D:\pg1\Compta\Travaux effectués\PWD10-12 Stats.xls")

End Sub

Sub AffPWNonLivrés()
'Cette macro permet de n'afficher que les fichiers PW n'yant pas encore été livrés à Alpha CRC (col. 15 vide et col. 21 non vide)

'L'on teste que l'on se trouve bien dans le bon classeur
w = ActiveWorkbook.Name

If w <> "Activité pg pro.xls" Then
    m = MsgBox("Vous ne vous trouvez pas dans le bon classeur." & Chr(13) & "Activez le bon classeur (Activité pg pro.xls)" & Chr(13) & "et relancez la macro.", vbOKOnly, "Actualisation de la liste des fichiers traduits")
    End
End If

'L'on teste que l'on se trouve bien dans la bonne feuille
f = ActiveSheet.Name

If InStr(f, "Activité Trad. PWD") = 0 Then
    m = MsgBox("Vous ne vous trouvez pas dans la feuille de gestion des projets PW." & Chr(13) & "Passez à cette feuille et relancez la macro.", vbOKOnly, "Actualisation de la liste des fichiers traduits")
    End
End If

'L'on déprotège la feuille
ActiveSheet.Unprotect

'L'on active l'affichage
Application.ScreenUpdating = True

'L'on commence par filtrer la colonne pg (col. 21) pour exclure de la sélection les éventuels fichiers non traités par pg (pour cause de vacances, par exemple)
Selection.AutoFilter Field:=21, Criteria1:="=x"

'Puis l'on procède au filtrage des documents non encore livrés
Selection.AutoFilter Field:=15, Criteria1:="=" 'L'on choisit comme critère personnalisé (Supérieur à "0") pour la colonne 7

End Sub

Open in new window

Top Expert 2008

Commented:
So are you copying from one spot on that sheet to another spot on that sheet?

Do you use the right-click to pick the paste option?

Author

Commented:
Yes and yes.
Top Expert 2008

Commented:
use ctrl+v instead and tell me if you still have the same issue.

Author

Commented:
Yes. Same behavior.
Top Expert 2008

Commented:
Can you also post the code for the SmartMove procedure?

I'm off to bed now, but I'll pick up tomorrow morning if this hasn't been solved by another expert first.

Bon courage,

Thomas

Author

Commented:
Thanks for your nice help. Have a good rest. See you tomorrow anyway.

Here you are for SmartMove procedure:

Sub SmartMove()

f = ActiveSheet.Name
w1 = InStr(f, "Projet Trad")
w2 = InStr(f, "Activité Trad.")
col = ActiveCell.Column

On Error Resume Next

If f = "Activité Trad. PWD" Then

        If col = 7 And ActiveCell.Value = "x" Then
            Selection.Cells(1, 2).Select
        End If
        
        If col = 8 Then
            Selection.Cells(1, 3).Select
        End If
        
        If col = 10 And ActiveCell.Value = "0" Then
            Selection.Cells(1, 4).Select
        End If
        
        If col = 15 Then
            Selection.Cells(1, 2).Select
        End If
        
        If col = 16 Then
            Selection.Cells(1, -8).Select
        End If

ElseIf w1 > 0 Then

        actcell = ActiveCell.Address

        If actcell = "$B$1" Then
            Range("$G$1").Select
        End If

        If actcell = "$G$1" Then
            Range("$F$1").Select
        End If

        If actcell = "$F$1" Then
            Range("$A$4").Select
        End If

'        If actcell = "$A$4" Then
'            Range("$E$4").Formula = "x"
'        End If

ElseIf w2 > 0 Then
    
        If col = 4 And ActiveCell.Value = "Intel" Then
            Selection.Cells(1, 7).Select
        End If
        
        If col = 10 And ActiveCell.Value <> "" Then
            Selection.Cells(1, -4).Select
        End If
        
        If col = 7 And ActiveCell.Value <> "" Then
            Selection.Cells(1, 6).Select
        End If
               
        If col = 7 And ActiveCell.Value = "" Then
            If ActiveCell.Offset(0, 1).Value = "" Then
                Selection.Cells(1, 2).Select
            Else
                Selection.Cells(1, 3).Select
            End If
        End If
        
        If col = 8 And ActiveCell.Value <> "" Then
            Selection.Cells(1, 6).Select
        End If
        
        If col = 9 And ActiveCell.Value <> "" Then
            Selection.Cells(1, 5).Select
        End If
        
End If

End Sub

Open in new window

Most Valuable Expert 2011
Top Expert 2011

Commented:
Do you have Skype's Click to Call plug in installed?

Author

Commented:
Yes. And actually installing Skype is the latest memorable change to my PC. Do you mean that plug-in interferes with Clipboard operations?
Most Valuable Expert 2011
Top Expert 2011

Commented:
Yep. There is a fixed version available for download from Skype.

Author

Commented:
Do you think that plug-in might be the root cause of the issue?
Most Valuable Expert 2011
Top Expert 2011
Commented:
Definitely: http://support.microsoft.com/kb/2697462
:)
(it's been a bit of a hot topic in Excel forums recently)

Author

Commented:
Thank you so much! I just uninstalled it and Excel copy operations have got back to normal behavior! Allow me extra time just to be sure it works fine and I will be back here to choose your solution as helpful!
Top Expert 2008

Commented:
Thanks for your intervention Rory.

Thomas

Author

Commented:
Thomas, I appreciate your help very much. However Rory found the solution for me, so I will give him/her credits. Thanks to you two.
Top Expert 2008

Commented:
As it should be. I've learnt something myself.

Glad you got your answer.

Thomas
Most Valuable Expert 2011
Top Expert 2011

Commented:
>>"I will give him/her credits"

him. :) (except on weekends)

glad to be of assistance.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial