Making one entry field hyperlink of another

[oogits]

Hello,

I have created (copied, modified and designed...) a nice contact book for my office, which includes a simple form.
It works very nice but, suddenly I had an idea to make it even nicer.

I would like to add to the form a field for a website address. Then, I would like this data to be the hyperlink of the name of contact, instead of creating a new column for this data.

Is there a way to do this?


Thanks a lot,

Uri.

[3xe1]

Can you attach your file or post the vba code please?

[oogits]

Yes of course

So I want the value entered in the "comp" to become a web link, if there is an url entered in the folowing field (i did not add a field to enter the url yet).

This is the code:

Private Sub button_add_Click()
ActiveSheet.Unprotect
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("גיליון1")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 1).Value = Me.comp.Value
.Cells(lRow, 2).Value = Me.exp.Value
.Cells(lRow, 3).Value = Me.city.Value
.Cells(lRow, 4).Value = Me.address.Value
.Cells(lRow, 5).Value = Me.office.Value
.Cells(lRow, 6).Value = Me.fax.Value
.Cells(lRow, 7).Value = Me.contact.Value
.Cells(lRow, 8).Value = Me.job.Value
.Cells(lRow, 9).Value = Me.mobile.Value
.Cells(lRow, 10).Value = Me.email.Value
End With

Me.comp.Value = ""
Me.exp.Value = ""
Me.city.Value = ""
Me.address.Value = ""
Me.office.Value = ""
Me.fax.Value = ""
Me.contact.Value = ""
Me.job.Value = ""
Me.mobile.Value = ""
Me.email.Value = ""
ActiveSheet.Protect
End Sub

Thanks a lot.

[3xe1]

Please add this code after the "With.. End With" block. This should serve your purpose...

I assume you'll add the 11th field (say 'website') in your form before adding this code.

Please Login or Register  to view this content.

Pl let me know if it works...

Cheers!

[oogits]

It doesn't work.

The code looks like this:

Please Login or Register  to view this content.

It shows that there is an error in Cells(IRow, 1).Select

[3xe1]

I wronged the variable lRow (as IRow). Same is corrected now..

Further, have you added the 11th field as I said I have assumed?


Pl try the code below -

Please Login or Register  to view this content.

[oogits]

Ok, I copied the code and paste (it didn't work otherwise) and it works very nice.
I have removed the ".Cells(lRow, 11).Value = Me.website.value" line because it added a column to my table.

Now, is it possible to have "http://" added automatically or maybe other solution, because it doesn't open the link. (it looks locally for the address).

Thank you very much!
So cool!

[oogits]

Mmm...

Well I just continued and found out that the link is "empty".
Is it because I removed the column? Should I leave the column and hid it?

Thanks again

[3xe1]

Please repost the file also... let's see what's wrong with it!

[oogits]

http://www.tsairi.com/share/contactbook.xlsm

Can't get the attachment work...

[oogits]

I have edited a bit the code and now things work better but, the link does not open.
I think because it's missing the "http://" part.

Please Login or Register  to view this content.

Thanks again,
Uri.

[3xe1]

Woe.. Uri.. you got me there... I just can't understand the language of this file.. I guess it's Hebrew (not at all sure)..

can you tell me in which field you are taking the URL as input?

[3xe1]

However, looking at your specific question above (regarding adding http:\\), pl try following line -

Please Login or Register  to view this content.

[oogits]

Forgot to translate these fields
You got it right about the language

And I figured out the & part.

It's difficult to understand the syntax... why in the msdn website it says "Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)"
while in the real code there are no "()"

Very annoying.

The file is complete now, only wondering if:

1. Is it possible to make the add update a line if the company with the contact person already exist?
2. Maybe, add a button for editing the line I select (the cell in column A for the line I want to edit)

I will change the labels of everything and upload again

Thanks a lot,

Uri.

[oogits]

http://www.tsairi.com/share/contactbooken.xlsm

[3xe1]

It is definitely possible in VBA, but I will prefer to do the editing part directly in the excel sheet itself. Why... because it is plain easy. I'd just Ctrl+F the contact (or better, use the autofilters which you have already provided).

For that, you just may have to add an 'Unlock' button somewhere. It should act like a switcher (when you click 'unlock', it's title becomes 'lock' & once it is locked, it reverts to 'unlock' so on & so forth)

BTW, I tested your file & it is working like a charm now.. congratulations!