hi,
do I have to write
SetTick Target
Cancel = True
for each cell is there a short way ?
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$D$8"
SetTick Target
Cancel = True
Case "$E$8"
SetTick Target
Cancel = True
Case "$F$8"
SetTick Target
Cancel = True
Case "$G$8"
SetTick Target
Cancel = True
Case "$H$8"
SetTick Target
Cancel = True
Case "$I$8"
SetTick Target
Cancel = True
End Select
End Sub
Assuming that I understand what you are trying to accomplish...
[VBA]Cancel = Not Intersect(Target, [d8:i8]) Is Nothing[/VBA]
Should replace all of your current code...
If SetTick is a procedurem then use this...
[VBA]
If Not Intersect(Target, [d8:i8]) Is Nothing Then
Cancel = True
SetTick Target
End If
[/VBA]
hi,
tried but does not work
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$D$8"
Case "$E$8"
Case "$F$8"
Case "$G$8"
Case "$H$8"
Case "$I$8"
Cancel = Not Intersect(Target, [d8:i8]) Is Nothing
Case "$N$1"
SetDate Target
Cancel = True
End Select
End Sub
hi,
tried but does not work.I have to go old way (: ?
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$D$8"
Case "$E$8"
Case "$F$8"
Case "$G$8"
Case "$H$8"
Case "$I$8"
If Not Intersect(Target, [d8:i8]) Is Nothing Then
SetTick Target
Cancel = True
End If
End Select
End Sub
Sub SetTick(Target As Range)
With Target
If .Value = "" Then
.Value = 1
.NumberFormat = "a;;"
.Font.Name = "Marlett"
Else
.Value = ""
.Font.Name = "arial"
.NumberFormat = ""
End If
End With
End Sub
huh? If this does not solve it, please explain what you are trying to do as opposed to how you are trying to do it.
[VBA]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$D$8" To "$I$8"
SetTick Target
Cancel = True
Case "$N$1"
SetDate Target
Cancel = True
End Select
End Sub[/VBA]
Hi,
your code is working, but for example it includes D9 as well.
only d8 e8 f8 g8 h8 i8 will be.
Hİ,
I found.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$D$8", "$E$8", "$F$8", "$G$8", "$H$8", "$I$8"
SetTick Target
Cancel = True
Case "$N$1"
SetDate Target
Cancel = True
End Select
End Sub
See this thread.
AOC,
Your first question in the other thread didn't mention any particular cells, then you needed two different double click actions for two different cells, now you have an array of cells. When you post a question, can you please be specific about what you are trying to achieve so that we can consider the question as a whole.
Regards
MD
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Hi,
if D10 is double clicked I want N9 to be 0,75, how can I do it ?
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$D$10", "$E$10", "$F$10", "$G$10", "$H$10", "$I$10"
SetTick Target
Cancel = True
End Select
End Sub
Sub SetTick(Target As Range)
With Target
If .Value = "" Then
.Value = 1
.NumberFormat = "a;;"
.Font.Name = "Marlett"
Else
.Value = ""
.Font.Name = "arial"
.NumberFormat = ""
End If
End With
End Sub
Double-clicking a cell is normally used to take you into "edit-mode", so the double-click event does just that. If you want to escape from the edit-mode and do something else with the double-click event you use Cancel=True.
i.e. you only need to 'Cancel' once, and it can be the very 1st line of code in the procedure if you want to use the event for something else...
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
hi,
then what is the code ?
I've read through this thread and I'm damned if I know exactly what you're trying to do. I was only answering your 1st question, where I noticed you were using the Worksheet_BeforeDoubleClick event to run some code and had multiple instances of Cancel = True and you had the following question regarding itso my reply refers to using [VBA]Originally Posted by aoc
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'put your own code here
End Sub
[/VBA]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Hi John,
AOC also seems to use double-click on cells to enter them for editing, hence the multiple Cancels in my code in the first thread.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Ahhh, starting to get the idea now, then this should be close...
[VBA]
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Application
.EnableEvents = False
If Not .Intersect(Target, [D8:I8]) Is Nothing Then
SetTick Target
Cancel = True
ElseIf Not .Intersect(Target, [N1]) Is Nothing Then
SetDate Target
Cancel = True
End If
.EnableEvents = True
End With
End Sub
[/VBA]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Posting Permissions
Reply With Quote
