If - then headache

[51DegreesNorth]

This piece of code has a problem:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim Loc_Var As String
    LocVar = "AFR"

'First step: select location

    If Not Intersect(Target, Range("F9")) Is Nothing Then
        Range("A" & Sheets("VBA Data").Range("O35")).Select
        LocVar = "XFD"
    End If
    
    If Not Intersect(Target, Range("F10")) Is Nothing Then
        Range("A" & Sheets("VBA Data").Range("O36")).Select
        LocVar = "BVC"
    End If
        
    If Not Intersect(Target, Range("F11")) Is Nothing Then
        Range("A" & Sheets("VBA Data").Range("O37")).Select
        LocVar = "SDF"
    End If

End Sub

I want LocVar to have the correct value. My code stubbornly refuses to change LocVar to the specified value.
Is it right that the If_Then statement only accepts one line of code and if so, is there a workaround ?

Thank you !

 

[Micron]

This is what happens to those who don't have Option Explicit at the top of their modules. Why it is an vb editor option that defaults to no is something I'll never understand.

Dim Loc_Var As String

LocVar = "AFR"

Not sure what you're trying to do there. Once the value is passed to the variable, you're not doing anything with it.

 

[rollis13]

Your macro needs some changes to have some sense, like this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim LocVar As String                          '<- fixed (wrong spelling)
    LocVar = "AFR"
    'First step: select location
    Application.EnableEvents = False              '<- added (avoids ridundant triggering of event)
    If Not Intersect(Target, Range("F9")) Is Nothing Then
        Sheets("VBA Data").Range("O35").Select    '<- changed
        LocVar = "XFD"
    End If
    If Not Intersect(Target, Range("F10")) Is Nothing Then
        Sheets("VBA Data").Range("O36").Select    '<- changed
        LocVar = "BVC"
    End If
    If Not Intersect(Target, Range("F11")) Is Nothing Then
        Sheets("VBA Data").Range("O37").Select    '<- changed
        LocVar = "SDF"
    End If
    Application.EnableEvents = True               '<- added
End Sub

but if you want to assign the new value of LocVal to the new location you need to change it to this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim LocVar As String
    LocVar = "AFR"
    'First step: select location
    Application.EnableEvents = False
    If Not Intersect(Target, Range("F9")) Is Nothing Then
        LocVar = "XFD"                            'assign new value to LocVar then ...
        Sheets("VBA Data").Range("O35") = LocVar  'paste LocVar to new location
    End If
    If Not Intersect(Target, Range("F10")) Is Nothing Then
        LocVar = "BVC"
        Sheets("VBA Data").Range("O36") = LocVar
    End If
    If Not Intersect(Target, Range("F11")) Is Nothing Then
        LocVar = "SDF"
        Sheets("VBA Data").Range("O37") = LocVar
    End If
    Application.EnableEvents = True
End Sub

PS. Hi to all.

 

[EXCEL MAX]

When use "Select" in code, event "SelectionChange" become activated again.
I think you need to temporarily disable events and release them in the end.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim LocVar As String
'    LocVar = "AFR"

'First step: select location
    Application.EnableEvents = False
    If Not Intersect(Target, Range("F9")) Is Nothing Then
        Range("A" & Sheets("VBA Data").Range("O35")).Select
        LocVar = "XFD"
    End If

    If Not Intersect(Target, Range("F10")) Is Nothing Then
        Range("A" & Sheets("VBA Data").Range("O36")).Select
        LocVar = "BVC"
    End If

    If Not Intersect(Target, Range("F11")) Is Nothing Then
        Range("A" & Sheets("VBA Data").Range("O37")).Select
        LocVar = "SDF"
    End If
    Application.EnableEvents = True
    MsgBox LocVar

End Sub

 

[Micron]

Trying to learn Excel model here, so asking, not challenging. In the posted code this raises error for me:

Range("A" & Sheets("VBA Data").Range("O35")).Select

"method range of object worksheet failed". I've already read over a dozen responses for this error and no one seemed to be using this in the same fashion so those were of no help. FWIW, I agree that without disabling events, this event refires the event. I managed to avoid getting stuck in a loop because I was stepping through.
I wanted to see if Select Case could trim the number of lines, but not really.

Select Case ActiveCell.Address
   Case "$F$9"
      Sheets("VBA Data").Range("AO35").Select
      LocVar = "XFD"

The purpose of LocVar remains a mystery though.

 

[EXCEL MAX]

Micron, maybe you don't have any number in the cell "O35".

 

[Micron]

True. I thought the code was just for selecting a range, not that it had to contain data. Entered a value in O and it didn't fail.
Thanks.

 

[51DegreesNorth]

Thank you rollis13, EXCEL MAX, and Micron. It's late here now - I'll make changes to my code tomorrow and report back.

 

[51DegreesNorth]

Re-activating the event was indeed the problem. In this code, I try to set two variables, LocVar for location and Dt_Req for date requested. These two are then passed to a sub for further processing. That sub is already finished and working just the way I want. The code you saw runs not on Sheet VBA Data but on another sheet. So what I've learned is that you can't be careful enough when playing with worksheet change events. Micron, like you, I am learning. Can I borrow your expression "Just asking" ? I like option explicit. But it runs only on module level, this is sheet level. I've tested it before writing this - you never know after all - sure enough, compilation error. I'll need a simpler apporoach I think.

rollis13, EXCEL MAX and Micron, thanks again for your help.

 

[EXCEL MAX]

Thank you too ...