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

 

[reberryjr]

I do.

=OFFSET(Bios!$O$2,0,0,COUNTA(Bios!$O:$O)-1,1)

 

[Fluff]

It may be the the scope is sheet rather than workbook, so try

Private Sub Worksheet_Activate()

Dim Cl As Range

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

End Sub

 

[reberryjr]

Well, it's still failing at this line.

For Each Cl In Sheets("Bios").Range("cEmail")

 

[Fluff]

Alternatively just add the hyperlink direct from the userform

Private Sub CommandButton1_Click()
With Sheets("Bios").Range("O" & Rows.Count).End(xlUp).Offset(1)
   .Hyperlinks.Add anchor:=.Offset(), Address:="mailto:" & [COLOR=#ff0000]tb1[/COLOR].Value, _
   TextToDisplay:=[COLOR=#ff0000]tb1[/COLOR].Value
End With
End Sub

Where tb1 is the name of your textbox

 

[reberryjr]

Fluff, I'm going to have to put you on retainer. LOL! Seriously though, you've been such a huge help in this and other posts.

Here is how the value from the textbox is currently coming over (along with several other fields/values), when the cmd_Submit button is clicked.

ws1.Range("O" & NextRow).Value = (Me.txt_Email)

Can I add the code to the same line, but obviously change it up some? I tried this, but am getting an "Expected end of statement" error on "anchor".

ws1.Range("O" & NextRow).Value = .Hyperlinks.Add _    anchor:=.Offset(), _
    address:="mailto:"&Me.txt_Email.Value, _
    texttodisplay:=me.txt_Email.Value

 

[Fluff]

Like this

With ws1.Range("O" & nextrow)
   .Hyperlinks.Add anchor:=.Offset(), Address:="mailto:" & tb1.Value, _
   TextToDisplay:=tb1.Value
End With

 

[reberryjr]

I got an "invalid use of property" error on the range portion of ws1.range

 

[Fluff]

Could you post the entire code for the command button

 

[reberryjr]

Private Sub cmd_Submit_Click()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet


Dim NextRow As Long
Dim NextRow2 As Long
Dim NextRow3 As Long


Set ws1 = ThisWorkbook.Sheets("Bios")
Set ws2 = ThisWorkbook.Sheets("Stats")
Set ws3 = ThisWorkbook.Sheets("Financials")
Set ws4 = ThisWorkbook.Sheets("Variables")


NextRow = ws1.Range("D" & Rows.Count).End(xlUp).Row + 1
NextRow2 = ws2.Range("C" & Rows.Count).End(xlUp).Row + 1
NextRow3 = ws3.Range("D" & Rows.Count).End(xlUp).Row + 1


ws1.Range("A" & NextRow).Value = "=Today()"
ws1.Range("B" & NextRow).Value = CDate(Me.txt_Updated)
ws1.Range("C" & NextRow).Value = "Active"
ws1.Range("D" & NextRow).Value = CInt(Me.txt_Key)
ws1.Range("E" & NextRow).Value = Me.txt_ClientID
ws1.Range("F" & NextRow).Value = Me.txt_First
ws1.Range("G" & NextRow).Value = Me.txt_Last
ws1.Range("H" & NextRow).Value = Me.txt_Suff
ws1.Range("I" & NextRow).Value = Me.txt_Name
ws1.Range("J" & NextRow).Value = Me.cobo_Gender
ws1.Range("K" & NextRow).Value = CDate(Me.txt_DoB)
ws1.Range("L" & NextRow).Value = CInt(Me.txt_SignupAge)
ws1.Range("M" & NextRow).Value = "=IF(RC[-2]="""","""",INT(RC[-12]-RC[-2])/365.25)"
ws1.Range("N" & NextRow).Value = (Me.txt_Phone)


'ws1.Range("O" & NextRow).Value = (Me.txt_Email)
ws1.Range ("O" & NextRow)
    .Hyperlinks.Add _
    anchor:=.Offset(), _
    Address:="mailto:" & Me.txt_Email.Value, _
    TextToDisplay:=Me.txt_Email.Value


ws2.Range("A" & NextRow2).Value = "=Today()"
ws2.Range("B" & NextRow2).Value = CDate(Me.txt_Updated)
ws2.Range("C" & NextRow2).Value = Me.txt_ClientID
ws2.Range("D" & NextRow2).Value = Me.txt_Name
ws2.Range("E" & NextRow2).Value = "Initial"
ws2.Range("F" & NextRow2).Value = Me.txt_Height
ws2.Range("G" & NextRow2).Value = CStr(Me.txt_Weight)
ws2.Range("H" & NextRow2).Value = CStr(Me.txt_Chest)
ws2.Range("I" & NextRow2).Value = CStr(Me.txt_Waist)
ws2.Range("J" & NextRow2).Value = CStr(Me.txt_Hips)
ws2.Range("K" & NextRow2).Value = CStr(Me.txt_BicepL)
ws2.Range("L" & NextRow2).Value = CStr(Me.txt_BicepR)
ws2.Range("M" & NextRow2).Value = CStr(Me.txt_ThighL)
ws2.Range("N" & NextRow2).Value = CStr(Me.txt_ThighR)
ws2.Range("O" & NextRow2).Value = CStr(Me.txt_CalfL)
ws2.Range("P" & NextRow2).Value = CStr(Me.txt_CalfR)


ws3.Range("A" & NextRow3).Value = "=Today()"
ws3.Range("B" & NextRow3).Value = CDate(Me.txt_Updated)
ws3.Range("C" & NextRow3).Value = "Initial"
ws3.Range("D" & NextRow3).Value = Me.txt_ClientID
ws3.Range("E" & NextRow3).Value = Me.txt_Name
ws3.Range("F" & NextRow3).Value = "=IF(RC[6]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_DPStart) = 0 Then ws3.Range("G" & NextRow3).Value = CDate(Me.txt_DPStart)
If Not Len(Me.txt_DP1stPymt) = 0 Then ws3.Range("H" & NextRow3).Value = CDate(Me.txt_DP1stPymt)
If Not Len(Me.txt_DPPymtAmt) = 0 Then ws3.Range("I" & NextRow3).Value = CCur(Me.txt_DPPymtAmt)
ws3.Range("J" & NextRow3).Value = "=IF(RC[-4]=""Inactive"","""",IF(RC[-2]<>"""",RC[-2],IF(R[-1]C[1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
If Not Len(Me.cobo_DPFreq) = 0 Then ws3.Range("K" & NextRow3).Value = Me.cobo_DPFreq
ws3.Range("M" & NextRow3).Value = "=IF(RC[6]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_DCStart) = 0 Then ws3.Range("N" & NextRow3).Value = CDate(Me.txt_DCStart)
If Not Len(Me.txt_DC1stPymt) = 0 Then ws3.Range("O" & NextRow3).Value = CDate(Me.txt_DC1stPymt)
If Not Len(Me.txt_DCPymtAmt) = 0 Then ws3.Range("P" & NextRow3).Value = CCur(Me.txt_DCPymtAmt)
ws3.Range("Q" & NextRow3).Value = "=IF(RC[-4]=""Inactive"","""",IF(RC[-2]<>"""",RC[-2],IF(R[-1]C[1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
If Not Len(Me.cobo_DCFreq) = 0 Then ws3.Range("R" & NextRow3).Value = Me.cobo_DCFreq
ws3.Range("T" & NextRow3).Value = "=IF(RC[6]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_OCStart) = 0 Then ws3.Range("U" & NextRow3).Value = CDate(Me.txt_OCStart)
If Not Len(Me.txt_OC1stPymt) = 0 Then ws3.Range("V" & NextRow3).Value = CDate(Me.txt_OC1stPymt)
If Not Len(Me.txt_OCPymtAmt) = 0 Then ws3.Range("W" & NextRow3).Value = CCur(Me.txt_OCPymtAmt)
ws3.Range("X" & NextRow3).Value = "=IF(RC[-4]=""Inactive"","""",IF(RC[-2]<>"""",RC[-2],IF(R[-1]C[1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
If Not Len(Me.cobo_OCFreq) = 0 Then ws3.Range("Y" & NextRow3).Value = Me.cobo_OCFreq
ws3.Range("AA" & NextRow3).Value = "=IF(RC[6]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_CTIStart) = 0 Then ws3.Range("AB" & NextRow3).Value = CDate(Me.txt_CTIStart)
If Not Len(Me.txt_CTI1stPymt) = 0 Then ws3.Range("AC" & NextRow3).Value = CDate(Me.txt_CTI1stPymt)
If Not Len(Me.txt_CTIPymtAmt) = 0 Then ws3.Range("AD" & NextRow3).Value = CCur(Me.txt_CTIPymtAmt)
ws3.Range("AE" & NextRow3).Value = "=IF(RC[-4]=""Inactive"","""",IF(RC[-2]<>"""",RC[-2],IF(R[-1]C[1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
If Not Len(Me.cobo_CTIFreq) = 0 Then ws3.Range("AF" & NextRow3).Value = Me.cobo_CTIFreq
ws3.Range("AH" & NextRow3).Value = "=IF(RC[6]<>"""",""Inactive"",IF(RC[1]="""",""Inactive"",""Active""))"
If Not Len(Me.txt_CTOStart) = 0 Then ws3.Range("AI" & NextRow3).Value = CDate(Me.txt_CTOStart)
If Not Len(Me.txt_CTO1stPymt) = 0 Then ws3.Range("AJ" & NextRow3).Value = CDate(Me.txt_CTO1stPymt)
If Not Len(Me.txt_CTOPymtAmt) = 0 Then ws3.Range("AK" & NextRow3).Value = CCur(Me.txt_CTOPymtAmt)
ws3.Range("AL" & NextRow3).Value = "=IF(RC[-4]=""Inactive"","""",IF(RC[-2]<>"""",RC[-2],IF(R[-1]C[1]=""B"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+14),IF(R[-1]C[1]=""W"",DATE(YEAR(R[-1]C),MONTH(R[-1]C),DAY(R[-1]C)+7),IF(R[-1]C[1]=""M"",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,DAY(R[-1]C)))))))"
If Not Len(Me.cobo_CTOFreq) = 0 Then ws3.Range("AM" & NextRow3).Value = Me.cobo_CTOFreq
ws3.Range("AO" & NextRow3).Value = "=RC[-32]+RC[-25]+RC[-18]+RC[-11]+RC[-4]"
ws3.Range("AP" & NextRow3).Value = "0"
ws3.Range("AQ" & NextRow3).Value = "0"
ws3.Range("AR" & NextRow3).Value = "0"
ws3.Range("AS" & NextRow3).Value = "0"
ws3.Range("AT" & NextRow3).Value = "0"
ws3.Range("AU" & NextRow3).Value = "=RC[-5]+RC[-4]+RC[-3]+RC[-2]+RC[-1]"
ws3.Range("AV" & NextRow3).Value = "=RC[-7]-RC[-1]"
ws3.Range("AW" & NextRow3).Value = "=IF(RC[-1]=0,""Paid"",IF(OR(RC[-39]<RC[-48],RC[-32]<RC[-48],RC[-25]<RC[-48],RC[-18]<RC[-48],RC[-11]<RC[-48]),""Late"",""Current""))"


ws3.Copy after:=ws4
ActiveSheet.Name = ws3.Range("D2").Value


ws3.Activate
Rows(2).EntireRow.Delete


End Sub

 

[Fluff]

You've missed out the With and the End With