Hi Vasant
Thanks for your reply.I don't know exactly what is flag in VBA,
however I think it is something like a condition. Within in my
limitation I just modify the code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if range("AH1")<>0 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()
Sheets(1).Select
range("AH1").Value = 1
Range("A1:AG100").Select
Selection.Copy
sheets(13).select
Range("d10").Select
activecell.PasteSpecial :xl paste special values
sheets(1).select
range("AH1").Value=0
end sub
Its working now , Thank you very much
Regards
Rajesh Kainikkara
On 6/23/11, Vasant <vasant...@gmail.com> wrote:
> 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
>
--
----------------------------------------------------------------------------------
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
