Avatar of lhutton
lhutton
 asked on

Intersect, and refer to column by name

  1. With the intersect, how can I prevent the code from being activated if there is no Site? (The commented code affects the whole worksheet.)
  2. How can I refer to Selection.Offset(0, -1) by the column's name, Site?

Dim sSite As String
If Not Intersect(Target, tb.ListColumns("Variable").DataBodyRange) Is Nothing Then
' If Intersect(Target, tb.ListColumns("Variable").DataBodyRange) <> "" And Selection.Offset(0, -1) <> "" Then
    Cancel = True
    sSite = WorksheetFunction.Substitute(WorksheetFunction.Index(Range("Sites[[Base]]") _
    , WorksheetFunction.Match(Selection.Offset(0, -1), Range("Sites[[Site]]"), 0)), "*", "" & ActiveCell.Value & "")
    ThisWorkbook.FollowHyperlink (sSite)
End If

Open in new window

Microsoft OfficeVBAMicrosoft Excel

Avatar of undefined
Last Comment
lhutton

8/22/2022 - Mon
Fabrice Lambert

Simply test the sSite variable:
if(sSite <> vbNullString) Then
    ThisWorkbook.FollowHyperlink sSite
End If

Open in new window


PS:
Do you really need the Selection and ActiveCell objects ?
Be warned that they are major source of troubles.
lhutton

ASKER
  1. Only the Site column may be empty; never sSite.
  2. My second question asks how can I avoid the Selection. For the ActiveCell, how else should I refer to it?
Norie

What is the code meant to do?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
lhutton

ASKER
The Variable column contains either a whole or part of a hyperlink; the adjacent Site column (if not empty for the active row) refers to another table for the other part of the hyperlink.

  1. The intersect means the code can only be activated if the Variable column is selected. I also want to prevent the code from being activated if no Site is specified for the active row (because it is a whole hyperlink).
  2. Instead of the Selection I want to refer to the Site column by name in the way that I have used names for the ranges. This will save me having to update the code when the table structure changes.
Fabrice Lambert

A Variable column ???
How are we supposed to understand that ? A column that can be anywhere ?

I still don't understand !!
Upload a sample workbook, and explain from the beginning (not just the part you attempt to solve).
lhutton

ASKER
The column is named Variable; hence
tb.ListColumns("Variable").DataBodyRange

Open in new window

.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Fabrice Lambert

Upload a sample workbook, and explain from the beginning (not just the part you attempt to solve).
For now, the little chunk of code you provided isn't enough to offer a solution.
There are other things involved, but you didn't told us about it.
ASKER CERTIFIED SOLUTION
lhutton

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.