Case Statement Usage

[Rocky0201]

Please help me code the proper method to using a Case Statement. Here's what I have coded:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'----------------------------------------------
' Test where the double click occurred
'----------------------------------------------
Select Case Target.Row And Target.Column
Case Target.Row = 2 And Target.Column = 1
SortProhibit = 2
Case Is = 4 And 1
SortProhibit = 1
End Select
End Sub

I have 60 test that I need to perform to dertime which cell the double click happened. Is there a better way of dong this test? I started using If/Then but thought the Case would be more efficent.

The problem with the above statements is that although the Target.Row is 4 and Target.Column is 1, It still falls into setting the SortProhibit variable to 2.

Thanks...

 

[Phox]

What are your rules to determine whether you want SortProhibit to be 2 or 1?

 

[xld]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'----------------------------------------------
' Test where the double click occurred
'----------------------------------------------
    Select Case Target.Address

        Case "$A$2":		SortProhibit = 2

	Case "$A$4":		SortProhibit = 1
	
	'etc
    End Select
End Sub

 

[lenze]

Not sure what you want either, but have you thought about

Select Case Target.Address
Case "$A$2":SortProhibit = 2
Case "$A$4":SortProhibit = 1
'etc
End Select

I assume SortProbit is a Boolean to allow/disallow a sort. What is this based on? Certain Rows? Is Column "A" the only Column involved?

lenze

 

[Rocky0201]

Thank you Phox and Xld...

Xld, Your example works great!!

Phox, to answer your question, depending on which cell is double clicked, I would set SortProbhit with a value of 1 through 7 where that value is then used to lockout a command button (button 1 through 7) in my userform.

Thanks again...

 

[Rocky0201]

Thanks lenze...

The SortProhibit is a Integer that I defined as Public in Module 4. Once I kickoff UserForm2, I will use the value to lock a command button (1 through 7) from being selected. The command buttons represent the Key1:Range() value in my sort statement ("B1" thru "H1").

Thanks again.

 

[Phox]

What I'm trying to get at is whether or not there may be a better way to set SortProhibit than specific cell by cell rules so that we can reduce our operations to a handful of comparisons rather than 60+.
IE:

If Target.Row > 10 Then
     SortProhibit = 1
ElseIf Target.Column Mod 2 = 1 Then
     Sort Prohibit = 2
Else
     SortProhibit = 3
End If

Basically, I'm curious if there is any way to be more efficient in setting your value.

 

[lenze]

What I'm trying to get at is whether or not there may be a better way to set SortProhibit than specific cell by cell rules so that we can reduce our operations to a handful of comparisons rather than 60+.
IE:

If Target.Row > 10 Then
     SortProhibit = 1
ElseIf Target.Column Mod 2 = 1 Then
     Sort Prohibit = 2
Else
     SortProhibit = 3
End If

Basically, I'm curious if there is any way to be more efficient in setting your value.

That's exactly what I was getting at with my questions. There should be a better way.

lenze