You can use a flag, public boolean variable which should be set to true by
default, the code in worksheets should run only if if the flag is true.
When the copy code is executed the flag has to be set to false which will
prevent the code from execution.
After the copy code is executed the flag can again be set to its default
value ie true.
something like this.
Public Flg as boolean
Flg=True
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Flg=True then
Application.EnableEvents = False
Columns(2).Interior.ColorIndex = 0
Cells(ActiveCell.Row, 2).Interior.Color = vbYellow
Application.EnableEvents = True
endif
End Sub
Sub Copy_April()
Flg=false
Sheets(1).Select
Range("A1:AG100").Select
Selection.Copy
sheets(13).select
Range("d10").Select
activecell.PasteSpecial :xl paste special values
flg=true
end sub
On Thu, Jun 23, 2011 at 2:12 PM, Rajesh K R <rajeshkainikk...@gmail.com>wrote:
> Hi Vasant
> Thank you very much, excellent work. I posted the query two times
> before this but I din't get a proper answer on that time.but this time
> u did it well. I have one more problem in that file. this file is used
> for marking attendance of employees, I have an another page for the
> settlement of salary,in that page I have to copy data from data entry
> sheets" April to March" but when I tried to copy the data the code I
> am given in each page preventing copy paste, the code is
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Application.EnableEvents = False
> Columns(2).Interior.ColorIndex = 0
> Cells(ActiveCell.Row, 2).Interior.Color = vbYellow
> Application.EnableEvents = True
> End Sub
> Sub Copy_April()
> Sheets(1).Select
> Range("A1:AG100").Select
> Selection.Copy
> sheets(13).select
> Range("d10").Select
> activecell.PasteSpecial :xl paste special values
> end sub
>
> Is there any code available to pause the above code for the time
> being,ie when the macro for copy paste works
>
> Regards & Thanks
>
> Rajesh Kainikkara
>
>
>
> On 6/22/11, Vasant <vasant...@gmail.com> wrote:
> > pls try this
> >
> > Sub locksheet()
> > Dim DataRng As Range, DataFilledRange As Range
> > Dim WkSht As Worksheet
> > Set DataRng = ThisWorkbook.Worksheets("April").Range("C3:F7")
> > Set WkSht = ThisWorkbook.Worksheets("April")
> > WkSht.Unprotect
> > With WkSht.Cells
> > .Locked = fase
> > .FormulaHidden = False
> > End With
> > For Each cls In DataRng
> > If cls.Value <> "" Then
> > If WorksheetFunction.CountA(WkSht.Range(Cells(3, cls.Column), Cells(3 +
> > DataRng.Rows.Count - 1, cls.Column))) <> DataRng.Rows.Count Then
> > If DataFilledRange Is Nothing Then
> > Set DataFilledRange = cls
> > Else
> > Set DataFilledRange = Application.Union(DataFilledRange, cls)
> > End If
> > End If
> > End If
> > Next cls
> > With DataFilledRange
> > .Locked = True
> > .FormulaHidden = True
> > End With
> > WkSht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
> > End Sub
> >
> > On Wed, Jun 22, 2011 at 7:28 PM, Rajesh K R
> > <rajeshkainikk...@gmail.com>wrote:
> >
> >> Hi Vasant
> >>
> >> Thanks for the code, its working well I need a condition in this the
> >> columns must not be locked in case all the data entry cells filled eg;
> >> A B C D E F G H I
> >> S N NAME 1 2 3 4 5 6 7
> >> 3 3 3 1 0 0 0
> 0
> >> 1 RAJESH X X X
> >> 2 SANU X X
> >> 3 ANIL X X
> >>
> >> Here in column C & D have full data so it have to be locked, but
> >> column"E" don't have full data so it should remain unlocked till it
> >> finished the data entry.Row 3 have counting formula & Cell "A3" have
> >> the max formula. u can compare them for
> >>
> >> Range("b2").Select
> >> ActiveCell.Offset(0, 1).Select
> >> Application.ScreenUpdating = False
> >> ActiveSheet.Unprotect Password:="rajesh"
> >> If ActiveCell.Text <> Range("a2").Text Then
> >> ActiveCell.Offset(0, 1).Select
> >> Else
> >> ActiveCell.EntireColumn.Locked = True
> >> ActiveSheet.Protect Password:="rajesh"
> >> End If
> >> Application.ScreenUpdating = True
> >>
> >> The code explain my idea about locking, but I don't to know how to
> >> make a loop .Pls consider this also & modify the code.
> >>
> >> Regards
> >> Rajesh Kainikkara
> >>
> >>
> >> On 6/22/11, Vasant <vasant...@gmail.com> wrote:
> >> > pls try this
> >> >
> >> > this will lock the populated cells in the range C3:AA5 in sheet
> 'april'
> >> >
> >> > Sub locksheet()
> >> > Dim DataRng As Range, DataFilledRange As Range
> >> > Dim WkSht As Worksheet
> >> > Set DataRng = ThisWorkbook.Worksheets("April").Range("C3:AA5")
> >> > Set WkSht = ThisWorkbook.Worksheets("April")
> >> > WkSht.Unprotect
> >> > For Each cls In DataRng
> >> > If cls.Value <> "" Then
> >> > If DataFilledRange Is Nothing Then
> >> > Set DataFilledRange = cls
> >> > Else
> >> > Set DataFilledRange = Application.Union(DataFilledRange, cls)
> >> > End If
> >> > End If
> >> > Next cls
> >> > With DataFilledRange
> >> > .Locked = True
> >> > .FormulaHidden = True
> >> > End With
> >> > WkSht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
> >> > End Sub
> >> >
> >> > On Wed, Jun 22, 2011 at 3:54 PM, Rajesh K R
> >> > <rajeshkainikk...@gmail.com>wrote:
> >> >
> >> >> Hi Experts
> >> >>
> >> >> I add a code in the work sheet for the identification of data
> >> >> selected, But the copy paste is not working in that sheet. How can I
> >> >> solve the issue,Pls check the code & tell me the change required.
> >> >>
> >> >> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >> >> Application.EnableEvents = False
> >> >> Columns(2).Interior.ColorIndex = 15
> >> >> Cells(ActiveCell.Row, 2).Interior.Color = vbYellow
> >> >> Application.EnableEvents = True
> >> >> End Sub
> >> >>
> >> >> Regards
> >> >> Rajesh Kainikkara
> >> >>
> >> >> --
> >> >>
> >> >>
> >>
> ----------------------------------------------------------------------------------
> >> >> Some important links for excel users:
> >> >> 1. Follow us on TWITTER for tips tricks and links :
> >> >> http://twitter.com/exceldailytip
> >> >> 2. Join our LinkedIN group @
> http://www.linkedin.com/groups?gid=1871310
> >> >> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> >> >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> >> >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
> >> >>
> >> >> To post to this group, send email to excel-macros@googlegroups.com
> >> >>
> >> >> <><><><><><><><><><><><><><><><><><><><><><>
> >> >> Like our page on facebook , Just follow below link
> >> >> http://www.facebook.com/discussexcel
> >> >>
> >> >
> >> >
> >> >
> >> > --
> >> > Regards
> >> >
> >> > Vasant
> >> >
> >> > --
> >> >
> >>
> ----------------------------------------------------------------------------------
> >> > Some important links for excel users:
> >> > 1. Follow us on TWITTER for tips tricks and links :
> >> > http://twitter.com/exceldailytip
> >> > 2. Join our LinkedIN group @
> http://www.linkedin.com/groups?gid=1871310
> >> > 3. Excel tutorials at http://www.excel-macros.blogspot.com
> >> > 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> >> > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
> >> >
> >> > To post to this group, send email to excel-macros@googlegroups.com
> >> >
> >> > <><><><><><><><><><><><><><><><><><><><><><>
> >> > Like our page on facebook , Just follow below link
> >> > http://www.facebook.com/discussexcel
> >> >
> >>
> >> --
> >>
> >>
> ----------------------------------------------------------------------------------
> >> Some important links for excel users:
> >> 1. Follow us on TWITTER for tips tricks and links :
> >> http://twitter.com/exceldailytip
> >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> >> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
> >>
> >> To post to this group, send email to excel-macros@googlegroups.com
> >>
> >> <><><><><><><><><><><><><><><><><><><><><><>
> >> Like our page on facebook , Just follow below link
> >> http://www.facebook.com/discussexcel
> >>
> >
> >
> >
> > --
> > Regards
> >
> > Vasant
> >
> > --
> >
> ----------------------------------------------------------------------------------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> > http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials at http://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
> >
> > To post to this group, send email to excel-macros@googlegroups.com
> >
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> > http://www.facebook.com/discussexcel
> >
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Regards
Vasant
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
