Select Case Code . .

[Evagrius Ponticus]

Hi All,

I am not sure why this isn't working. When I step through the target and the range "Ratio" are equal but the code skips the line that selects the sheet. Thank you for any help.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("MyRange")) Is Nothing Then
Select Case Target
    Case Target.Address = Range("Ratio").Address
        Sheet1.Select
    End Select
End If
End Sub

 

[Hermanito]

You cannot use a condition in a Case statement...

Either you need to do this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("MyRange")) Is Nothing Then
Select Case Target.Address
    Case Range("Ratio").Address
        Sheet1.Select
    End Select
End If
End Sub

or you need to rewrite it as a simple If Then

 

[Evagrius Ponticus]

That was flawless!! Thank you for the lesson and help good sir!

 

[Hermanito]

Purely syntactical, your code was okay... but just for the sake of, let's explain why it didn't work

Select Case Target
this says you want to make a decision based on the value of Target
Case Target.Address = Range("Ratio").Address
this says you want to compare the Target value to the expression Target.Address = Range("Ratio").Address
That expression may result in true or false... but neither of those will ever be equal to target (a range), so the case is not fulfilled and the code in it is skipped.

So, when I said you cannot use a condition in a case statement, I really meant: it seldom makes any sense to use a condition in a case statement

 

[Evagrius Ponticus]

Ahh - I see now. So what I wrote was generating a boolean - therefore a boolean will not equal a "range", right? But is it acceptable to use "IF" statements within a Select Case? Thank you!!

 

[Hermanito]

Exactly, you tried to compare a boolean to a range.

You can use any code you like within the Select Case, also If Then, but if you think you need to, you might want to search for a better way to express your intent.

Simple decisions can be done with If Then ElseIf Else Endif, nested if necessary. When you have a lot of if then statements on the same condition, you can better use the Select Case construction.
For each case within the Select Case, you can create a whole block of code, with more If Then, or even another Select Case structure...

What you cannot do is have those constructions as a value for a Case. But the code in a case 'block' can of course contain all valid VB syntax...

I hope I made things clearer with that explanation

 

[Evagrius Ponticus]

Thank you Hermanito for the thorough explanation - it was great. I printed the thread to keep in my VBA folder