Problem with hyperlink

[TrueRise]

Dear everybody
here is my code which copy an existing sheet and make a hyperlink to it in the main sheet with friendly name.
but when press the button of that form i always have a error message, and the compiler stope at the hyperlink line code.

the message tell am that the add method for hyperlink is failed.

anyhelp

[vba]

Private Sub cmdOKNewCust_Click()
Dim strCustName As String
Dim FirstPeriodSum As Double
Dim sbAdd As String
Dim scTip As String
Dim txToDsply As String
Dim Cust_Sheet_Name As String
Dim Cust_Number As Integer
Dim j As Integer
Dim i As Integer
Dim strReceipt As String, strGPhone As String, strMobile As String, strFax As String, strAddress As String
'Cust Name Check
If txtCustName <> "" Then
strCustName = txtCustName
Else
MsgBox "No name", vbOKOnly, "try again"
txtCustName.SetFocus
Exit Sub
End If

'Fist Account
If txtCustFirstPeriod = "" Then
FirstPeriodSum = 0
Else
FirstPeriodSum = txtCustFirstPeriod
End If
strReceipt = txtReceiptNum
strGPhone = txtCustGPhone
strMobile = txtCustMobile
strFax = txtCustFax
strAddress = txtCustAddress
'
Application.ScreenUpdating = False

'Copy Cust Sheet
Sheets("Cust").Copy After:=Sheets("Customers")
'change new sheet name
Cust_Sheet_Name = "c" & Application.Sheets.Count + 1

'check new sheet name
For j = 1 To ActiveWorkbook.Sheets.Count
If Cust_Sheet_Name = ActiveWorkbook.Sheets(j).Name Then
Cust_Sheet_Name = "c" & Application.Sheets.Count + Int(Rnd() * 10)
j = 1
End If
Next
'change sheet name
ActiveSheet.Name = Cust_Sheet_Name
'fill some field
ActiveSheet.Range("A5") = "Mr." & Trim(strCustName)
ActiveSheet.Range("B11").Value = FirstPeriodSum
ActiveSheet.Range("D11").Value = strReceipt
ActiveSheet.Range("E11").Value = "Bill"
ActiveSheet.Range("F11").Value = Format(Now(), "dd/mm/yyyy")
'First account border
ActiveSheet.Range("b11:f11").Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

'_____Customers Sheet_____
Sheets("Customers").Activate
If Not IsNull(Sheets("Customers").Range("B500").End(xlUp)) Then
i = Sheets("Customers").Range("B500").End(xlUp).Offset(1, 0).Row
Else
i = Sheets("Customers").Range("B500").End(xlUp).Row
End If
'
sbAdd = Cust_Sheet_Name & "!A5"
scTip = "Go to Ctstomer " & strCustName
txToDsply = strCustName

'the hyperlink
With Application.Sheets("Customers")
.Hyperlinks.Add Anchor:=.Range("B" & i), Address:="", SubAddress:=sbAdd, ScreenTip:=scTip, TextToDisplay:=txToDsply
End With


'Cust Account past
Sheets("Customers").Activate
ActiveSheet.Range("C" & i).Select
ActiveCell.Formula = "=" & Cust_Sheet_Name & "!$C$5"
'format the account
With Selection.Font
'.Name = Arial
.Size = 13
.Bold = True
.Underline = False
Selection.Style = "Comma"
Selection.NumberFormat = "_-* #,##0_-;_-* #,##0-;_-* ""-""??_-;_-@_-"
End With

'resotr customers sheet
Call Cust_Names_Sort

'reFill the serial num
Cust_Number = Sheets("Customers").Range("Customers_List").Count
i = 10
For j = 1 To Cust_Number
ActiveSheet.Range("A" & i).Value = j
i = i + 1
Next j

'_________ AlDaleel Sheet ________
Sheets("Daleel").Activate
If Not IsNull(Sheets("Daleel").Range("B1000").End(xlUp)) Then
i = Sheets("Daleel").Range("B1000").End(xlUp).Offset(1, 0).Row
Else
i = Sheets("Daleel").Range("B10500").End(xlUp).Row
End If
ActiveSheet.Range("A" & i).Value = "Ò"
ActiveSheet.Range("B" & i).Value = strCustName
ActiveSheet.Range("C" & i) = strGPhone
ActiveSheet.Range("D" & i) = strMobile
ActiveSheet.Range("E" & i) = strFax
ActiveSheet.Range("F" & i) = strAddress
'Resort al-daleel
Call Sort_Phone
'
Application.ScreenUpdating = True
Unload frmNewCust
Sheets(Cust_Sheet_Name).Activate
MsgBox "Add: " & vbCrLf & "Cust: " & strCustName & vbCrLf & "Successfuly", , "message"
'empty frmNewCust fields
txtCustName = ""
txtCustFirstPeriod = ""
txtReceiptNum = ""
txtCustGPhone = ""
txtCustMobile = ""
txtCustFax = ""
txtCustAddress = ""
End Sub


[/vba]



I'm using MS Excel 2007
and the unreadding text here that becouse of copy past and it's just string ""

[Bob Phillips]

Can you post the workbook? There is clearly a form which it would help to have.

[TrueRise]

here is the work book
and the form name is frmNewCust

[TrueRise]

the workbook
sorry the corrupted, u can find the new one down there

thank's

[GTO]

Greetings TruRise,

I realize that Minnesota is a ways North of me and all - fact is, my mom's side of the family grew up there... but what language is that?

Also - least for me, there was no file extension, and tossing an .xls just resulted in being told it was unrecognizable.

Mark

[TrueRise]

Greetings TruRise,

I realize that Minnesota is a ways North of me and all - fact is, my mom's side of the family grew up there... but what language is that?

Also - least for me, there was no file extension, and tossing an .xls just resulted in being told it was unrecognizable.

Mark

oh' that right Mark
again I upload the file it's *.xlsb
in *.zip file

sorry for old file.

[hardlife]

working example:

01/ excel_insert_hyperlink

This will create a hyperlink in cell A1

02/ excel_insert_hyperlink2

This will create a hyperlink in cell A2

If you want to use a different cell, then modify as needed.


part from Your working code example:

wsh.Hyperlinks.Add Anchor:=Cells(i, 2), Address:="", _
SubAddress:=sbAdd, _
ScreenTip:=scTip, _
TextToDisplay:=txtToDsply

This code is not working (because of usage Anchor:=Selection in code):

My opinion = You must specify cell where do You want to put hyperlink.

wsh.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=sbAdd, _
ScreenTip:=scTip, _
TextToDisplay:=txToDsply

[vba]

Sub excel_insert_hyperlink()
sbAdd = ""
scTip = ""
txtToDsply = "www.linkhere.com"
Set wsh = ActiveSheet
wsh.Hyperlinks.Add Anchor:=Cells(1, 1), Address:="", _
SubAddress:=sbAdd, _
ScreenTip:=scTip, _
TextToDisplay:=txtToDsply
End Sub

Sub excel_insert_hyperlink2()
sbAdd = ""
scTip = ""
txtToDsply = "www.linkhere.com"
Set wsh = ActiveSheet
wsh.Hyperlinks.Add Anchor:=Range("A2"), Address:="", _
SubAddress:=sbAdd, _
ScreenTip:=scTip, _
TextToDisplay:=txtToDsply
End Sub


[/vba]

HAPPY AND SUNNY DAY

GOOD LUCK

Pavel

[TrueRise]

thnks Pavel
I changed the to this one

[VBA]
'the hyperlink
With Application.Sheets("Customers")
.Hyperlinks.Add Anchor:=.Range("B" & i), Address:="", SubAddress:=sbAdd, ScreenTip:=scTip, TextToDisplay:=txToDsply
End With
[/VBA]

but I've the following error num. 80010108
"the object invoked has disconnected from it's client"

and i upload a 2nd copy of my workbook and it's all in english

[hardlife]

Hi TrueRise,

try this arrangement, HTH

Sheets("Customers").Hyperlinks.Add Anchor:=Range("B" & i), Address:="", _
SubAddress:=sbAdd, _
ScreenTip:=scTip, _
TextToDisplay:=txToDsply

working example:

[vba]

Sub createhyperlink()

Sheets("Customers").Hyperlinks.Add Anchor:=Range("B" & 1), Address:="http://www.google.com/", SubAddress:=""

End Sub

[/vba]
HAPPY AND SUNNY DAY

GOOD LUCK

Pavel

[TrueRise]

Hi Pavel
still have the same Error Msg
"the object invoked has disconnected from it's client"

[VBA]
'with this code
Sheets("Customers").Hyperlinks.Add Anchor:=Range("B" & i), Address:="",_
SubAddress:=sbAdd, _
ScreenTip:=scTip, _
TextToDisplay:=txToDsply
[/VBA]

and when I use Object Variable wsh as WorkSheet
the Error msg become
the add method failed
[VBA]
Dim wsh as WorkSheet
Sheets("Customers").Activate
set wsh =ActiveSheet
wsh.Hyperlinks.Add Anchor:=Range("B" & i), Address:="",_
SubAddress:=sbAdd, _
ScreenTip:=scTip, _
TextToDisplay:=txToDsply
[/VBA]

[hardlife]

TrueRise, please try this code in new workbook, HTH

give me information if it works

HAPPY AND SUNNY DAY

Pavel

[vba]
Sub test()
'with this code
Sheets(1).Hyperlinks.Add Anchor:=Range("B" & 1), Address:="http://www.google.com", _
SubAddress:="", _
ScreenTip:="example", _
TextToDisplay:="test"
End Sub

[/vba]

[TrueRise]

Pavel
the code works fine in an empty workbook
so what do u thnik?

[hardlife]

Hi TrueRise,

it looks like there is some problem with input data of hyperlink
put this in front of hyperlink, we must look at it step by step

[VBA]

MsgBox Sheets("Customers").Name
MsgBox Range("B" & i).Address
MsgBox sbAdd
MsgBox scTip
MsgBox txToDsply

Sheets("Customers").Hyperlinks.Add Anchor:=Range("B" & i), Address:="", _
SubAddress:=sbAdd, _
ScreenTip:=scTip, _
TextToDisplay:=txToDsply

[/VBA]

[TrueRise]

Hi Pavel h r u?

all msg boxes show correct data.
Pavel, when Error msg shown and i click on run after crash the excel
everything goes will, except that the selected cell can't be changed.

[hardlife]

Hi True Rise,

so the question is,
does worksheet "Customers"
allows You, to set the same
data like does the VBA code do?

TrueRise, do You can try to set data
manually to worksheet "Customers"?

Try to write some value, to the cell where
do You want hyperlink or try to enter (write)
hyperlink directly (may be there is some protection
or data validation) or save Your workbook in xls format
and send it again, me would like to help You, but
there is some alphabet me do not understand,
so it is really difficult for me to use code
and understand it at all.

hope we will make some progress

happy and sunny day,
good night for now,

Pavel

[TrueRise]

Hi Pavel

sorry for late but it's work.
I'll try to write data manualy in the "Customers" Sheet
and I'll tell u.

Have a nice day
Firas