Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Code
Question - I have a pull down menu in Cell T. I want excel to copy Cell T to
Cell U when I double click on Cell T after selecting something from the pull down menu (similar to what it does below). Can you add another procedure to the sheet code already created for this workbook or is this not possible? See below for current sheet code used. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) If Not Application.Intersect(Range("n8:n207,q8:q207,y8:y2 07,ab8:ab207,ae8:ae207,ah8:ah207,ak8:ak207,an8:an2 07,aq8:aq207,at8:at207,aw8:aw207,az8:az207,bc8:bc2 07"), Target) Is Nothing Then 'For a range use 'If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then On Error GoTo endit Application.EnableEvents = False Application.ScreenUpdating = False With Target .Value = "R" .Offset(0, 1).Copy .Offset(0, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks _ :=False, Transpose:=False End With Application.CutCopyMode = False End If Cancel = True Range("as7").Select endit: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Thanks for any help you can provide Robert B. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Code
This is standalone and will copy a double-clicked cell in column T into
column U: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set r = Range("T:T") If Intersect(r, Target) Is Nothing Then Exit Sub Target.Copy Target.Offset(0, 1) Cancel = True End Sub -- Gary''s Student - gsnu200754 "Robert B." wrote: Question - I have a pull down menu in Cell T. I want excel to copy Cell T to Cell U when I double click on Cell T after selecting something from the pull down menu (similar to what it does below). Can you add another procedure to the sheet code already created for this workbook or is this not possible? See below for current sheet code used. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) If Not Application.Intersect(Range("n8:n207,q8:q207,y8:y2 07,ab8:ab207,ae8:ae207,ah8:ah207,ak8:ak207,an8:an2 07,aq8:aq207,at8:at207,aw8:aw207,az8:az207,bc8:bc2 07"), Target) Is Nothing Then 'For a range use 'If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then On Error GoTo endit Application.EnableEvents = False Application.ScreenUpdating = False With Target .Value = "R" .Offset(0, 1).Copy .Offset(0, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks _ :=False, Transpose:=False End With Application.CutCopyMode = False End If Cancel = True Range("as7").Select endit: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Thanks for any help you can provide Robert B. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Code
Gary's Student,
Thanks for the help. I have another question: I received from another discussion response this code: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo endit If Target.Column = 20 Then Application.EnableEvents = False Target.Offset(0, 1).Value = Target.Value End If endit: Application.EnableEvents = True End Sub It works but I would like to modify it to copy Cell U to Cell V and paste as value when something is selected in Cell T from a Drop Down Menu. Ultimately I would like it to When something is selected in Column T from a drop menu to copy it to either the adjacent cell or next one and paste as a value so that when the Cell in Column T is deleted the data is still there. The code at the bottom of this works great but if I include Column T in it it will give me an R. Can we write an additional sheet code with the same type information only getting it to when column T is double clicked it will copy column U to Column V and paste as a value? Thanks for any help you can provide Robert B. "Gary''s Student" wrote: This is standalone and will copy a double-clicked cell in column T into column U: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set r = Range("T:T") If Intersect(r, Target) Is Nothing Then Exit Sub Target.Copy Target.Offset(0, 1) Cancel = True End Sub -- Gary''s Student - gsnu200754 "Robert B." wrote: Question - I have a pull down menu in Cell T. I want excel to copy Cell T to Cell U when I double click on Cell T after selecting something from the pull down menu (similar to what it does below). Can you add another procedure to the sheet code already created for this workbook or is this not possible? See below for current sheet code used. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) If Not Application.Intersect(Range("n8:n207,q8:q207,y8:y2 07,ab8:ab207,ae8:ae207,ah8:ah207,ak8:ak207,an8:an2 07,aq8:aq207,at8:at207,aw8:aw207,az8:az207,bc8:bc2 07"), Target) Is Nothing Then 'For a range use 'If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then On Error GoTo endit Application.EnableEvents = False Application.ScreenUpdating = False With Target .Value = "R" .Offset(0, 1).Copy .Offset(0, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks _ :=False, Transpose:=False End With Application.CutCopyMode = False End If Cancel = True Range("as7").Select endit: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Thanks for any help you can provide Robert B. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet Name Changes how do i code for that | Excel Worksheet Functions | |||
VB code to copy sheet format to another sheet | Excel Discussion (Misc queries) | |||
VBA code to identify last row in a sheet | Excel Discussion (Misc queries) | |||
Sheet Protection and VBA Code | Excel Discussion (Misc queries) | |||
Select sheet with VB code | Excel Discussion (Misc queries) |