+ Reply to Thread
Results 1 to 6 of 6

How can I add Hyperlink for Thisworkbook.Worksheet with userform textbook

Hybrid View

  1. 04-06-2015, 08:01 AM #1
    sanjaypalsjp
    sanjaypalsjp is offline
    Registered User
    Join Date
    08-15-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    9

    How can I add Hyperlink for Thisworkbook.Worksheet with userform textbook

    Hey friends, is it possible:

    Add Hyperlink with userform textbook for Thisworkbook.Worksheet

    How can I add

    'Add hyperlink in
wsd.Cells(iRow, 1).Hyperlinks.Add = Sheets(Me.TextBox0).Activate
    'Add hyperlink
Sheet3.Hyperlinks.Add _
    Anchor:=wsd.Cells(iRow, 1), _
    Address:=ws.Cells(1, 1).Active, _
    TextToDisplay:=Me.TextBox0.Value
    'Add hyperlink
Sheet3.Hyperlinks.Add _
    Anchor:=wsd.Cells(iRow, 1), _
    Address:=Worksheets(Me.TextBox0).Active, _
    TextToDisplay:=Me.TextBox0.Value
    Above all not work

    2nd is Run-time error '438':

    3rd is Run-time error '9':

    Any idea will be appreciated.
    If you have the time, I really appreciate the help.
    Last edited by sanjaypalsjp; 04-07-2015 at 04:21 AM.
    Reply With QuoteRegister To Reply
  2. 04-06-2015, 08:09 AM #2
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: How can I add Hyperlink for Thisworkbook.Worksheet with userform textbook

    Why do you have Activate/Active in the midst of that code?
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  3. 04-06-2015, 08:27 AM #3
    sanjaypalsjp
    sanjaypalsjp is offline
    Registered User
    Join Date
    08-15-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: How can I add Hyperlink for Thisworkbook.Worksheet with userform textbook

    Thanks for your reply
    But not add link in Worksheet Cell ref. to open the Worksheet name as ws.Cells(1, 1)
    When I click on cell
    Error is cannot open the specified file.
    Reply With QuoteRegister To Reply
  4. 04-06-2015, 08:45 AM #4
    sanjaypalsjp
    sanjaypalsjp is offline
    Registered User
    Join Date
    08-15-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: How can I add Hyperlink for Thisworkbook.Worksheet with userform textbook

    This is my code of userform

    Private Sub CommandButton2_Click()
    Dim newSheetName As String, strPrefix As String
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Database")
    Sheets("Database").Activate

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

If Trim(Me.TextBox0.Value) = "" Then
  Me.TextBox00.Value = ""
  Me.TextBox0.SetFocus
Me.Hide
  MsgBox "Please Enter."
Me.Show
Exit Sub
End If

If WorksheetFunction.CountIf(ws.Range("A3", ws.Cells(iRow, 1)), _
 Me.TextBox0.Value) >= 1 Then
        Me.TextBox00.Value = ""
        Me.TextBox0.Value = ""
        Me.TextBox0.SetFocus
Me.Hide
        MsgBox "Duplicate Found", vbCritical
Me.Show
        Exit Sub
    End If

If Trim(Me.TextBox00.Value) = "" Then
  Me.TextBox00.SetFocus
Me.Hide
  MsgBox "Please enter"
Me.Show
  Exit Sub
End If

If Trim(Me.TextBox000.Value) = "" Then
  Me.TextBox000.SetFocus
Me.Hide
  MsgBox "Please enter"
Me.Show
  Exit Sub
End If
    
If Trim(Me.TextBox0000.Value) = "" Then
  Me.TextBox0000.SetFocus
Me.Hide
  MsgBox "Please enter"
Me.Show
  Exit Sub
End If
    
'confirm Create New Worksheet
If MsgBox("Create New Worksheet " & vbCrLf & _
        "" & TextBox0, vbYesNo, "Notification") = vbNo Then
Exit Sub
Else
    
    MsgBox "New Worksheet " & TextBox0 & vbCrLf & _
    "Create Successfully"

If Trim(Me.TextBox1.Value) = "" Then
  Me.TextBox1.SetFocus

'Create New Worksheet with Member ID
    newSheetName = Me.TextBox0
    strPrefix = newSheetName

    Sheet4.Copy Before:=Sheet4
    With ActiveSheet
        On Error GoTo DupName
            .Name = newSheetName
        On Error GoTo 0
    End With
    Exit Sub
DupName:
    newSheetName = strPrefix & (Val(Replace(newSheetName, strPrefix, vbNullString, 1, 1)) + 1)
    Resume

    Set ws = Worksheets("newSheetName")

Sheets("newSheetName").Activate

End If
End If
    
End Sub

Private Sub CommandButton3_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim wsd As Worksheet
    Set wsd = Worksheets("Database")
 
If Trim(Me.TextBox1.Value) = "" Then
  Me.TextBox1.SetFocus
Me.Hide
  MsgBox "Please enter"
Me.Show
  Exit Sub
End If
 
If Trim(Me.TextBox2.Value) = "" Then
  Me.TextBox2.SetFocus
Me.Hide
  MsgBox "Please enter"
Me.Show
  Exit Sub
End If

If Trim(Me.TextBox3.Value) = "" Then
  Me.TextBox3.SetFocus
Me.Hide
  MsgBox "Please enter"
Me.Show
  Exit Sub
End If

If Trim(Me.ComboBox4.Value) = "" Then
  Me.ComboBox4.SetFocus
Me.Hide
  MsgBox "Please enter"
Me.Show
  Exit Sub
End If
 
If Trim(Me.TextBox5.Value) = "" Then
  Me.TextBox5.SetFocus
Me.Hide
  MsgBox "Please enter"
Me.Show
  Exit Sub
End If

If Trim(Me.ComboBox6.Value) = "" Then
  Me.ComboBox6.SetFocus
Me.Hide
  MsgBox "Please enter"
Me.Show
  Exit Sub
End If

If Trim(Me.TextBox7.Value) = "" Then
  Me.TextBox7.SetFocus
Me.Hide
  MsgBox "Please enter"
Me.Show
  Exit Sub
End If

If Trim(Me.ComboBox8.Value) = "" Then
  Me.ComboBox8.SetFocus
Me.Hide
  MsgBox "Please enter"
Me.Show
  Exit Sub
End If

If Trim(Me.TextBox9.Value) = "" Then
  Me.TextBox9.SetFocus
Me.Hide
  MsgBox "Please enter"
Me.Show
  Exit Sub
End If

If Trim(Me.TextBox10.Value) = "" Then
  Me.TextBox10.SetFocus
Me.Hide
  MsgBox "Please enter"
Me.Show
  Exit Sub
End If

'copy the data to the newSheetName
ws.Cells(1, 1).Value = Me.TextBox0.Value
ws.Cells(1, 2).Value = Me.TextBox00.Value
ws.Cells(1, 3).Value = Me.TextBox000.Value
ws.Cells(1, 4).Value = Me.TextBox0000.Value
ws.Cells(1, 5).Value = Me.TextBox1.Value
ws.Cells(1, 6).Value = Me.TextBox2.Value
ws.Cells(1, 7).Value = Me.TextBox3.Value
ws.Cells(1, 8).Value = Me.ComboBox4.Value
ws.Cells(1, 9).Value = Me.TextBox5.Value
ws.Cells(1, 10).Value = Me.ComboBox6.Value
ws.Cells(1, 11).Value = Me.TextBox7.Value
ws.Cells(1, 12).Value = Me.ComboBox8.Value
ws.Cells(1, 13).Value = Me.TextBox9.Value
ws.Cells(1, 14).Value = Me.TextBox10.Value
ws.Cells(1, 15).Value = Me.TextBox11.Value

iRow = wsd.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
   
Sheet3.Hyperlinks.Add _
    Anchor:=wsd.Cells(iRow, 1), _
    Address:=ws.Cells(1, 1).Activate, _
    TextToDisplay:=Me.TextBox0.Value

'copy the data to the Database
wsd.Cells(iRow, 1).Value = UCase(Me.TextBox0.Value)
wsd.Cells(iRow, 2).Value = UCase(Me.TextBox000.Value)
wsd.Cells(iRow, 3).Value = UCase(Me.TextBox0000.Value)
wsd.Cells(iRow, 4).Value = Me.TextBox00.Value
wsd.Cells(iRow, 5).Value = Me.TextBox1.Value
wsd.Cells(iRow, 6).Value = Me.TextBox2.Value
wsd.Cells(iRow, 7).Value = Me.TextBox3.Value
wsd.Cells(iRow, 8).Value = Me.ComboBox4.Value
wsd.Cells(iRow, 9).Value = Me.TextBox5.Value
wsd.Cells(iRow, 10).Value = Me.ComboBox6.Value
wsd.Cells(iRow, 11).Value = Me.TextBox7.Value
wsd.Cells(iRow, 12).Value = Me.ComboBox8.Value
wsd.Cells(iRow, 13).Value = Me.TextBox9.Value
wsd.Cells(iRow, 14).Value = Me.TextBox10.Value
wsd.Cells(iRow, 15).Value = Me.TextBox11.Value

  Sheets("Login").Activate

    Unload MemberData
    
    'confirm data transferred
    MsgBox "Member ID.:- " & TextBox0 & vbCrLf & _
    "Family Name:- " & TextBox000 & vbCrLf & _
    "Data Transferred Successfully" & vbCrLf & _
    "", vbInformation, "Data transfer"

End Sub
    Last edited by sanjaypalsjp; 04-07-2015 at 04:19 AM.
    Reply With QuoteRegister To Reply
  5. 04-06-2015, 08:31 AM #5
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: How can I add Hyperlink for Thisworkbook.Worksheet with userform textbook

    What do you have in TextBox0?

    Can you upload an example workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.
    Reply With QuoteRegister To Reply
  6. 04-06-2015, 09:38 AM #6
    sanjaypalsjp
    sanjaypalsjp is offline
    Registered User
    Join Date
    08-15-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: How can I add Hyperlink for Thisworkbook.Worksheet with userform textbook

    Quote Originally Posted by Norie View Post
    What do you have in TextBox0?

    Can you upload an example workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.
    Attach my woorkbook for you help

    My Book1.xlsm
    Last edited by sanjaypalsjp; 04-07-2015 at 04:23 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Transfering Userform textbox filepath to worksheet as a hyperlink.
    By SJJ in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-29-2012, 05:17 PM
  2. How to hyperlink cell value to userform created worksheet?
    By jwichern1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-10-2011, 12:37 PM
  3. How to code a Hyperlink that links to a UserForm created worksheet?
    By jwichern1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-17-2011, 01:52 PM
  4. Insert picture into userform from hyperlink on worksheet
    By msommerf in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-16-2010, 12:21 PM
  5. why can't I call a sub that's in UserForm from ThisWorkBook
    By 2Raw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-24-2005, 03:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1