turninf off macros
 

Hi,

I have this code written which doesnt let the user to select the cell
(to prevent accidental deletion):

1st macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("b2:c2001")) Is Nothing Then Exit Sub
Me.Cells(Target.Row, "D").Select

End Sub


Now the problem is that i want a macro which will just copy the b and
columns and paste the values to the same columns, the macro is preet
simple, the problem is that because of the earlier macro, i canno
select the columns.....

2nd Macro:
Sub Macro()

Range("B1:C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
End Sub


is there a way where i can turn the fist macro OFF when the secon
macro runs???

thanks for any help!!

--
Message posted from http://www.ExcelForum.com

turninf off macros
 

No need to select the cells to change then, just use this
Range("B1:C2001") = Range("B1:C2001").Value

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

"paritoshmehta " wrote in
message ...
Hi,

I have this code written which doesnt let the user to select the cells
(to prevent accidental deletion):

1st macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("b2:c2001")) Is Nothing Then Exit Sub
Me.Cells(Target.Row, "D").Select

End Sub


Now the problem is that i want a macro which will just copy the b and c
columns and paste the values to the same columns, the macro is preety
simple, the problem is that because of the earlier macro, i cannot
select the columns.....

2nd Macro:
Sub Macro()

Range("B1:C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub


is there a way where i can turn the fist macro OFF when the second
macro runs???

thanks for any help!!!


---
Message posted from http://www.ExcelForum.com/

turninf off macros
 

one suggestion is to declare a public variable as a boolean then you can set it in the copying macro and test it in this one

In Module
Dim Public LetCopy as Boolea

In Copy macro
Private Sub MyCopyMacro(
LetCopy = Tru
Do your code here..
LetCopy= Fals
End Su

Private Sub Worksheet_SelectionChange(ByVal Target As Range
if LetCopy = True Then Exit Su
If Intersect(Target, Me.Range("b2:c2001")) Is Nothing Then Exit Su
if Target.Cells.Count 1 Then Exit Su
Me.Cells(Target.Row, "D").Selec

End Su

----- paritoshmehta wrote: ----

Hi

I have this code written which doesnt let the user to select the cell
(to prevent accidental deletion)

1st macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range
If Intersect(Target, Me.Range("b2:c2001")) Is Nothing Then Exit Su
Me.Cells(Target.Row, "D").Selec

End Su


Now the problem is that i want a macro which will just copy the b and
columns and paste the values to the same columns, the macro is preet
simple, the problem is that because of the earlier macro, i canno
select the columns.....

2nd Macro:
Sub Macro(

Range("B1:C1").Selec
Range(Selection, Selection.End(xlDown)).Selec
Selection.Cop
Range("B1").Selec
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:=
False, Transpose:=Fals
End Su


is there a way where i can turn the fist macro OFF when the secon
macro runs??

thanks for any help!!


--
Message posted from http://www.ExcelForum.com

turninf off macros
 

please help me