Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
KC KC is offline
external usenet poster
 
Posts: 55
Default Worksheet_beforedoubleclick

Hi

I wish to be advised why it failed, and how to rectify please.

Listing A was tested working as expected.
sub t()
Rows("9:9").Select
Selection.Copy
Sheets(Range("D1").Text).Select
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("CData").Select
end sub

Once this sub was transferred to Worksheet_BeforeDoubleClick, it failed with
Run-time Error "1004"
Select method of range class failed

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row).Select
Selection.Copy
Sheets(Range("D1").Text).Select
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select <<<<<<<<
ActiveSheet.Paste
Sheets("CData").Select
End Sub

Also I do not seem to be able to condense the code without using so many
"Select".
Are these restriction applicable to event handling please?

Thank you for your advice
Regards
KC


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,510
Default Worksheet_beforedoubleclick

Hi KC,

Two examples. The first is your code edited. The second to answer your
second question:-

I don't really know why ActiveSheet is required to select the paste cell but
it won't work without it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row).Copy
Sheets(Range("D1").Text).Select
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("CData").Select
End Sub

Second Example. If Sheets("CData") is where the macro is called from then
the last line
Sheets("CData").Select
is not required.

Note that the copy and destination is one line. The space and underscore
after copy is only a line break in the code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row).Copy _
Destination:=Sheets(Range("D1").Text) _
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Sheets("CData").Select
End Sub


Regards,

OssieMac


"KC" wrote:

Hi

I wish to be advised why it failed, and how to rectify please.

Listing A was tested working as expected.
sub t()
Rows("9:9").Select
Selection.Copy
Sheets(Range("D1").Text).Select
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("CData").Select
end sub

Once this sub was transferred to Worksheet_BeforeDoubleClick, it failed with
Run-time Error "1004"
Select method of range class failed

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row).Select
Selection.Copy
Sheets(Range("D1").Text).Select
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select <<<<<<<<
ActiveSheet.Paste
Sheets("CData").Select
End Sub

Also I do not seem to be able to condense the code without using so many
"Select".
Are these restriction applicable to event handling please?

Thank you for your advice
Regards
KC



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default Worksheet_beforedoubleclick

Hi OssieMac

Yes, both tested OK
I come across quite often unwritten rules we have to "know" before the lines
will work.
Before we "know" it, it is simply frustrating.

--
Regards
KC

"OssieMac" wrote in message
...
Hi KC,

Two examples. The first is your code edited. The second to answer your
second question:-

I don't really know why ActiveSheet is required to select the paste cell
but
it won't work without it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row).Copy
Sheets(Range("D1").Text).Select
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("CData").Select
End Sub

Second Example. If Sheets("CData") is where the macro is called from then
the last line
Sheets("CData").Select
is not required.

Note that the copy and destination is one line. The space and underscore
after copy is only a line break in the code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row).Copy _
Destination:=Sheets(Range("D1").Text) _
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Sheets("CData").Select
End Sub


Regards,

OssieMac


"KC" wrote:

Hi

I wish to be advised why it failed, and how to rectify please.

Listing A was tested working as expected.
sub t()
Rows("9:9").Select
Selection.Copy
Sheets(Range("D1").Text).Select
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("CData").Select
end sub

Once this sub was transferred to Worksheet_BeforeDoubleClick, it failed
with
Run-time Error "1004"
Select method of range class failed

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Rows(Target.Row).Select
Selection.Copy
Sheets(Range("D1").Text).Select
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select <<<<<<<<
ActiveSheet.Paste
Sheets("CData").Select
End Sub

Also I do not seem to be able to condense the code without using so many
"Select".
Are these restriction applicable to event handling please?

Thank you for your advice
Regards
KC





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sub Worksheet_BeforeDoubleClick from elsewhere? Jack Sons Excel Discussion (Misc queries) 2 November 1st 05 09:22 PM


All times are GMT +1. The time now is 09:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"