Userform textbox email address not 'live' link after transfer to worksheet

[AyurvedaPura]

Hi All

I have a userform where users enter name, email and telephone details then click a commandbutton to transfer values to a worksheet after finding the first blank row in a range. Here is the code:

Dim wsEnrol as Worksheet
Dim irow as Long

Set wsEnrol = Sheets("Enrol Schedule")irow = wsEnrol.Range("A57").End(xlUp).Offset(1, 0).Row


Application.ScreenUpdating = False


With wsEnrol
    .Range("A" & irow) = FullName
    .Range("B" & irow) = Email
    .Range("C" & irow) = Tel
    .Range("D" & irow) = StartDate


End With

The thing is, the email address doesn't appear on the worksheeet as a live link - its just the text. If I enter the cell and press enter, it recognises it as an email adddress and makes it a link, but how do I make it do this from vba?

It doesn't have to appear on the userform, but in the sheet i need the email address to be an email address, not text that looks like one but cannot be clicked without first entering the cell and pressing Enter.

Many thanks for any assistance with this, I'm sure it must be simple but I can't seem to find anything useful searching around.

Best wishes

Daniel

 

[Trevor G]

Daniel the email address would need to have MailTo: before the entry to make this work so you can click on it and it sends an email address so perhaps this will resolve it, change your line

.Range("B" & irow) = Email

To

.Range("B" & irow) = "MailTo:" & Email
</pre>

 

[Kyle123]

@Trever that still won't resolve automatically, you need to insert it as a hyperlink:

With wsEnrol    
    .Range("A" & irow) = FullName
    .Hyperlinks.Add .Range("B" & irow), Email, , , Email
    .Range("C" & irow) = Tel
    .Range("D" & irow) = StartDate
End With

 

[AyurvedaPura]

Kyle, thanks a lot!!

Once I removed that period before 'Range' in your formula, it works a treat.

So it should read:

[LEFT][COLOR=#333333]With wsEnrol    [/COLOR][/LEFT]
    .Range("A" & irow) = FullName 
   .Hyperlinks.Add Range("B" & irow), Email, , , Email 
   .Range("C" & irow) = Tel 
   .Range("D" & irow) = StartDate
End With

Big help, thanks!