PDA

View Full Version : select case



aoc
04-21-2007, 02:45 PM
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

tstom
04-21-2007, 02:54 PM
Assuming that I understand what you are trying to accomplish... :)

Cancel = Not Intersect(Target, [d8:i8]) Is Nothing

Should replace all of your current code...

If SetTick is a procedurem then use this...


If Not Intersect(Target, [d8:i8]) Is Nothing Then
Cancel = True
SetTick Target
End If

aoc
04-21-2007, 02:58 PM
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

aoc
04-21-2007, 03:05 PM
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

tstom
04-21-2007, 03:08 PM
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.

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

aoc
04-21-2007, 03:15 PM
Hi,

your code is working, but for example it includes D9 as well.
only d8 e8 f8 g8 h8 i8 will be.

aoc
04-21-2007, 03:31 PM
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

mdmackillop
04-21-2007, 04:04 PM
See this thread (http://www.vbaexpress.com/forum/showthread.php?t=12420).
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

aoc
04-21-2007, 04:32 PM
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

johnske
04-21-2007, 06:37 PM
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...

aoc
04-21-2007, 11:46 PM
hi,

then what is the code ?

johnske
04-22-2007, 12:44 AM
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 it
hi,

do I have to write

SetTick Target
Cancel = True

for each cell...so my reply refers to using
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'put your own code here
End Sub

mdmackillop
04-22-2007, 01:45 AM
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.

johnske
04-22-2007, 02:26 AM
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.Ahhh, starting to get the idea now, then this should be close...

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