Convert Existing Values in Range to Email Address Hyperlinks

[reberryjr]

I was originally trying to format UserForm text box values, so that when they populate the worksheet, they're in a hyperlink format, but according to what I'm reading, that can't be done. So, I decided to just try and implement code that updates the range in the worksheet, when the worksheet is activated.

I'm getting an error of "Invalid or Unqualified Reference" error and the ".Hyperlinks" is being highlighted.

Private Sub Worksheet_Activate()

Dim cEmail As Range


For Each cell In Range(cEmail)
    .Hyperlinks.Add anchor:=cEmail, _
    Address:="mailto:" & cEmail.Value, _
    TextToDisplay:=cEmail


End Sub

 

[Jon Peltier]

What's cEmail? The code you've posted has not assigned any range to the variable.

 

[reberryjr]

cEmail is the range where the email addresses are actually stored on the worksheet. The only thing I've used ranges for up until now, is to populate combo boxes on UserForms, hence why the code isn't structured properly.

 

[Jon Peltier]

So try something like this:

Private Sub Worksheet_Activate()

Dim cEmail As Range

Set cEmail = ActiveSheet.Range("A1:A5")

For Each cell In Range(cEmail)

    .Hyperlinks.Add Anchor:=cEmail, _
        Address:="mailto:" & cEmail.Value, _
        TextToDisplay:=cEmail

End Sub

 

[reberryjr]

Jon, thanks for the feedback. I made that change, and I'm getting the same error as before.

One question about the the piece you added. The range will constantly expand (which is accounted for in the Name Manager). Since only A1:A5 is being referenced, will that limit what gets updated?

 

[Jon Peltier]

Now that I'm awake, here is some improved code.

Private Sub Worksheet_Activate()

  Dim cEmails As Range  ' range with all email addresses
  Dim cEmail As Range  ' individual cell with email address

  'Set cEmails = ActiveSheet.Range("A1:A5") ' static range
  Set cEmails = ActiveSheet.Range("MyEmailRange")  ' named range (dynamic)

  ' don't use 'Cell' as a looping variable
  For Each cEmail In cEmails.Cells

    ActiveSheet.Hyperlinks.Add Anchor:=cEmail, _
        Address:="mailto:" & cEmail.Value, _
        TextToDisplay:=cEmail.Value

  Next

End Sub

 

[reberryjr]

Jon, thanks for the reply! Unfortunately, it's resulting in the same error. A couple of questions about the code you provided, so that I know I'm implementing it correctly:

1. It looks like I need to create a new named range, that is the same as the cEmail range. Is that accurate?
2. On your Set cEmails line, it looks like I need to name the new range "MyEmailRange". Is that accurate?
3. It looks like you're only setting 1 of the ranges. Why not both?

*I tried to use the reply with quote feature, but I'm barely awake just yet, and wasn't able to get it to multi-quote.

One thing that "feels" off about the code is, I feel like I should be declaring the worksheet, but maybe I shouldn't, since the code is in the worksheet itself. Again, I'm pretty new to VBA, so I'm still struggling with the rules of when to do this, and when to do that, verbiage, etc.

 

[Fluff]

How about

Private Sub Worksheet_Activate()

Dim Cl As Range

For Each Cl In Range("cemail")
   Cl.Hyperlinks.Add anchor:=Cl, Address:="mailto:" & Cl.Value, TextToDisplay:=Cl.Value
Next Cl

End Sub

 

[reberryjr]

I just copied that over and tried it. Still getting the Application-Defined or Object-Defined Error. I noticed that "cEmail" wasn't declared as a range, so I did that and still got the same error.

 

[Fluff]

Do you have a named range called cEmail?