我的目标是在添加新行时,我想将第一列中最后一行 + 1 中的单元格链接到与值同名的工作表。即添加了一个客户 ID 为 130 的新客户,并创建了一个同名的工作表。现在我想添加一个从客户 ID 到工作表的链接。从用户表单输入中检索数据。也许我应该注意到在我的用户表单中单击一个命令按钮后这个子运行。
当使用下面的代码时,我在最后一行 ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:=Sheets(cs_sht), TextToDisplay:=cs_sht
收到错误消息'5 Invalid procedure or argument'。我尝试使用选择和不选择以及将工作表和锚点更改为 activecell 来摆弄锚点。
Private Sub CB_NewCS_Click()
'Copying data to table
Dim rng As Range
Dim LastRow As Long
Dim cs_sht As String
Dim ws As Worksheet
Dim Ws_K As Worksheet
NewCS.Hide
' Setting ranges and sheets
Set rng = Sheets("Kundeliste").ListObjects("Tabel_Kunde").Range
Set Ws_K = Sheets("Kundeliste")
' Searching for new input line
LastRow = rng.Find(What:=Ó * Ó, _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
' Inserting userform data
With rng.Parent
.Cells(LastRow + 1, 1).Value = Application.WorksheetFunction.Max(rng.Columns(1)) + 1 ' Customer ID in ascending order
.Cells(LastRow + 1, 2).Value = TB_Firstname.Value ' First name
.Cells(LastRow + 1, 3).Value = TB_Lastname.Value ' Last name
.Cells(LastRow + 1, 4).Value = TB_Phone.Value ' Phone number
.Cells(LastRow + 1, 5).Value = TB_Address.Value ' Address
.Cells(LastRow + 1, 6).Value = TB_Housenr.Value ' House / road number
.Cells(LastRow + 1, 7).Value = TB_Floornr.Value ' Floor nr.
.Cells(LastRow + 1, 8).Value = TB_Zipcode.Value ' Zipcode / postal code
.Cells(LastRow + 1, 9).Value = TB_City.Value ' City / town
.Cells(LastRow + 1, 10).Value = LB_Product.Value ' Product for the customer
' Checkbox values:
.Cells(LastRow + 1, 12).Value = -Chb_Contact.Value
.Cells(LastRow + 1, 13).Value = -Chb_Meet1.Value
.Cells(LastRow + 1, 14).Value = -Chb_Accept.Value
.Cells(LastRow + 1, 15).Value = -Chb_Meet2.Value
.Cells(LastRow + 1, 16).Value = -Chb_Revision.Value
.Cells(LastRow + 1, 17).Value = -Chb_Contact2.Value
.Cells(LastRow + 1, 18).Value = -Chb_Followup.Value
cs_sht = .Cells(LastRow + 1, 1).Value
End With
With ThisWorkbook
Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
ws.Name = cs_sht
End With
Ws_K.Activate
Ws_K.Range(Ws_K.Cells(LastRow + 1, 1), Ws_K.Cells(LastRow + 1, 1)).Select
' OBS OBS OBS ERROR OCCURS HERE vvvvvvvv
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:=Sheets(cs_sht), TextToDisplay:=cs_sht
End Sub
子地址必须是引用单元格的字符串,因此您需要这样的东西
SubAddress:=cs_sht & "!A1"
宏记录器对于计算这种语法很有用。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句