View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Alan Alan is offline
external usenet poster
 
Posts: 138
Default Copying all whilst Range Forbidden active

On Jan 26, 11:44*am, donh wrote:
Hi,

Excel 2003, little knowledge. *I'm using VBA below to protect my
worksheet as I have found that using the standard Protect Cells stops
me from using a scroll bar on the page. *I have now gone a little
further and wanted to Copy All and Paste Values to remove all formulas
so the page can be archived and outside refernce tables be changed
without affecting past information. *Of course I've now hit the
problem that I cant Copy All because I have the Range Forbidden
Active! *anu suggestions?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rgForbidden As Range

Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"),
Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"),
Range("K10:K56"), _
Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"),
Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"),
Range("B66:R70"), Range("B74:R78"), _
Range("B82:R86"), Range("B90:R94"), Range("B98:R102"),
Range("B106:R110"), Range("T10:U56"))

If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub
Range("A1").Select

End Sub

Many thanks

Don


Can you not copy/paste before you set the forbidden range?

A.