Re: $$Excel-Macros$$ Private sub selection-change preventing copy paste
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 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
> 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 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
>> > 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 NAME1 2 3 4 5 6 7
>> >> 3 3 3 1 0 0 0
>> 0
>> >> 1 RAJESH X X X
>> >> 2 SANUX X
>> >> 3 ANILX 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.P
Re: $$Excel-Macros$$ Private sub selection-change preventing copy paste
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 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 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
> > 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 NAME1 2 3 4 5 6 7
> >> 3 3 3 1 0 0 0
> 0
> >> 1 RAJESH X X X
> >> 2 SANUX X
> >> 3 ANILX 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 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)
Re: $$Excel-Macros$$ Private sub selection-change preventing copy paste
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 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
> 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 NAME1 2 3 4 5 6 7
>> 3 3 3 1 0 0 0 0
>> 1 RAJESH X X X
>> 2 SANUX X
>> 3 ANILX 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 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
>> > 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 g
Re: $$Excel-Macros$$ Private sub selection-change preventing copy paste
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 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 NAME1 2 3 4 5 6 7
> 3 3 3 1 0 0 0 0
> 1 RAJESH X X X
> 2 SANUX X
> 3 ANILX 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 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
> > 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 lin
Re: $$Excel-Macros$$ Private sub selection-change preventing copy paste
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 NAME1 2 3 4 5 6 7
3 3 3 1 0 0 0 0
1 RAJESH X X X
2 SANUX X
3 ANILX 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 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
> 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
Re: $$Excel-Macros$$ Private sub selection-change preventing copy paste
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 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
