Private Sub Worksheet_SelectionChange

[notty dread]

Hi there, I am having a problem with the Paste' function in excel being disabled - i think this is down to the Private Sub Worksheet_SelectionChange(ByVal Target As Range) (as this seems to be the probelm from my web searches), however I'm not competant enoough in VB to know what in the sub is cuasing the problem.

Is there a work around for this problem?

Cheers,



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Me.Range("E1").Value = "Viewer On" Then
Application.DisplayFormulaBar = False
Select Case ActiveCell.Column
Case "6"
Application.Run "'Usability Issues 2008 09.xls'!ShowWatcher"
Case "7"
Application.Run "'Usability Issues 2008 09.xls'!ShowWatcher"
Case "10"
Application.Run "'Usability Issues 2008 09.xls'!ShowWatcher"
Case Else
Exit Sub
End Select
Else
Application.DisplayFormulaBar = True
Exit Sub
End If

End Sub

 

[jproffer]

I don't think that's your problem, in and of itself anyhow.

Post the "'Usability Issues 2008 09.xls'!ShowWatcher" code. There may be a problem there.

 

[RoryA]

Your event code clears the clipboard. Try this version:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim objData As Object, lngCopyMode As Long
   lngCopyMode = Application.CutCopyMode
   If lngCopyMode = xlCopy Or lngCopyMode = xlCut Then
      Set objData = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
      With objData
         .getfromclipboard
      End With
   End If
   If Me.Range("E1").Value = "Viewer On" Then
      Application.DisplayFormulaBar = False
      Select Case ActiveCell.Column
         Case 6, 7, 10
            Application.Run "'Usability Issues 2008 09.xls'!ShowWatcher"
         Case Else
      End Select
   Else
      Application.DisplayFormulaBar = True
   End If
   If lngCopyMode = xlCopy Or lngCopyMode = xlCut Then
      With objData
         .putinclipboard
      End With
   End If
 
End Sub

 

[notty dread]

jproffer: I thought this but after a couple of days scouring the net I know it is with the Selection Change sub, but thanks anyway.

rorya: that worked a treat! thanks so much. i apologise that my coding isn't great, i'm more of an intuitive programmer (self taught with little time to study - i know the structures and methods etc) and do very basic programming in a few languages (javascript my preference), unfortunatley this means i'm not great with the finer points of all the languages and I am only now really getting in to VB. many, many thanks again!

http://www.thenaturalmystic.com

 

[RoryA]

No need to apologise at all - I've seen, and written, far worse in my time!