If - then headache

51DegreesNorth

New Member
Joined
Mar 29, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
This piece of code has a problem:

VBA Code:
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 !
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,159
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 30, 2012
Messages
1,101
Office Version
  1. 2016
Platform
  1. Windows
Your macro needs some changes to have some sense, like this:
VBA Code:
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:
VBA Code:
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.
 
Last edited:

EXCEL MAX

Well-known Member
Joined
Nov 11, 2020
Messages
672
Office Version
  1. 2016
Platform
  1. Windows
When use "Select" in code, event "SelectionChange" become activated again.
I think you need to temporarily disable events and release them in the end.
VBA Code:
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
 
Last edited:
Solution

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,159
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
Nov 11, 2020
Messages
672
Office Version
  1. 2016
Platform
  1. Windows
Micron, maybe you don't have any number in the cell "O35".
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,159
Office Version
  1. 365
Platform
  1. Windows
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. :oops:
Thanks.
 

51DegreesNorth

New Member
Joined
Mar 29, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Thank you rollis13, EXCEL MAX, and Micron. It's late here now - I'll make changes to my code tomorrow and report back.
 

51DegreesNorth

New Member
Joined
Mar 29, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,181,212
Messages
5,928,722
Members
436,625
Latest member
agholson81

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top