Case Statement Usage

Rocky0201

Active Member
Joined
Aug 20, 2009
Messages
278
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

Well-known Member
Joined
Jul 26, 2004
Messages
522
What are your rules to determine whether you want SortProhibit to be 2 or 1?
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Code:
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

Legend
Joined
Feb 18, 2002
Messages
13,690
Not sure what you want either, but have you thought about
Code:
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

Active Member
Joined
Aug 20, 2009
Messages
278
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

Active Member
Joined
Aug 20, 2009
Messages
278
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

Well-known Member
Joined
Jul 26, 2004
Messages
522
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:
Code:
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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:
Code:
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
 

Forum statistics

Threads
1,082,334
Messages
5,364,677
Members
400,810
Latest member
elbashka

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top