Copying all whilst Range Forbidden active

[donh]

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("A256"), 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

 

[Alan]

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("A256"), 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.

 

[donh]

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

A.- Hide quoted text -

- Show quoted text -

Hi Alan,

The way I've done it is the Range Forbidden is always active and lives
on the worksheet it applies to not in a module and with my limited
knowledge do not know how to turn it off and on if required

Don

 

[AB]

Do you want to copy the values to some other location? Or do you want
to just remove the formulas and replace those with actual values
(returned by those formulas)?
If so, you don't need to copy at all. You can have a loop that:
- loops through every cell in the range of your interest
(forbidenRange)
- replaces the cell's .value with .value (i.e. it just puts its
visible value back into the cell as a value [as oppose to formula])

that's it.

 

[Alan]

Hi Alan,

The way I've done it is the Range Forbidden is always active and lives
on the worksheet it applies to not in a module and with my limited
knowledge do not know how to turn it off and on if required

Don- Hide quoted text -

- Show quoted text -

AB is right. You may not be able to select the cells but there is
nothing to stop you using code to change cell contects without
selectiing them.

Alternatively, if that is too complicated and you are looking to make
a one off change, the code you have shown us is a worksheet event.
Comment this code out to stop it running, make your copy/paste changes
manually and then reacticate the code. OR you could bypass the event
code by using a password set elsewhere:

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

If Range("A1") = "MyPassword" Then Exit Sub

Set rgForbidden = Union(Range("A256"), 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

Remove your password and the code comes back into play.

A

 

[donh]

AB is right. You may not be able to select the cells but there is
nothing to stop you using code to change cell contects without
selectiing them.

Alternatively, if that is too complicated and you are looking to make
a one off change, the code you have shown us is a worksheet event.
Comment this code out to stop it running, make your copy/paste changes
manually and then reacticate the code. OR you could bypass the event
code by using a password set elsewhere:

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

If Range("A1") = "MyPassword" Then Exit Sub

Set rgForbidden = Union(Range("A256"), 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

Remove your password and the code comes back into play.

A- Hide quoted text -

- Show quoted text -

Thank you both for replying. The workbook will not be under my
control so I need to keep things up front rather than expecting the
user to dive into the VBA so like the password idea for unlocking. At
the same time this would be a one-time overwrite of formulas to values
so would be happy to incorporate a loop, just don't know how.

Thanks

Don

 

[Alan]

Thank you both for replying.  The workbook will not be under my
control so I need to keep things up front rather than expecting the
user to dive into the VBA so like the password idea for unlocking.  At
the same time this would be a one-time overwrite of formulas to values
so would be happy to incorporate a loop, just don't know how.

Thanks

Don- Hide quoted text -

- Show quoted text -

Don

So far we have called it a loop because we have assumed that you may
only want to get rid of the formulae in specific cells. If you want to
clear the whole sheet it is simpler than a loop. Add the following
macro.

Sub RemoveFormulae()

Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("A1").Select

End Sub

This does, using code, what I think you were suggesting you were
wanting to do manually ... but it does change the whole sheet. You may
still like to use the password idea so that you can check that it has
done what you wanted it to do after execution.

A.

 

[donh]

Don

So far we have called it a loop because we have assumed that you may
only want to get rid of the formulae in specific cells. If you want to
clear the whole sheet it is simpler than a loop. Add the following
macro.

Sub RemoveFormulae()

    Cells.Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Range("A1").Select

End Sub

This does, using code, what I think you were suggesting you were
wanting to do manually ... but it does change the whole sheet. You may
still like to use the password idea so that you can check that it has
done what you wanted it to do after execution.

A.- Hide quoted text -

- Show quoted text -

Alan,

Thank you that does exactly want I wanted.

Many thanks

Don

 

[Alan]

Alan,

Thank you that does exactly want I wanted.

Many thanks

Don- Hide quoted text -

- Show quoted text -

Glad to have been of help.

Just for completeness, if you had wanted to "loop" through individual
cells you could have used ...

Sub RemoveFormulaeFromCells()

Dim rgForbidden As Range
Dim Cell As Range

Set rgForbidden = Union(Range("A256"), 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"))

Application.ScreenUpdating = False

For Each Cell In rgForbidden
Cell.Copy
Cell.PasteSpecial Paste:=xlPasteValues
Next
Application.CutCopyMode = False
Range("A1").Select

Application.ScreenUpdating = True
End Sub

.... This only changes the formulae in your named ranges but due to the
number of iterations it does take longer to run so I wouldn't advise
using it.

A.