Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Viewing links through User Form in Excel Databse

  1. #1

    Viewing links through User Form in Excel Databse



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,

    I am really hoping someone can help me with this, I have a Databse I have created with help which also has a user form to add and view data to the spreadsheet, In Text Box 10,11 & 12 you should be able to click the text (which is set up to show as a link) and it takes you to the link, it used to work perfectly fine but since adding code to the database to incorporate a Combo Box to be able to search for a name it does not work and I do not understand why.

    I am really desperate to get this database up and running but just can not figure out the problem, I have had alot of help from others with regards to the coding so I can not take the credit for the entire Database unfortunatley.

    The password is "test" for the data sheet access.

    Please can someone help me!!

    Shazz
    xx
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    You have a couple of issues here.
    1) You're trying to use a MouseUp event, and should be using a DoubleClick event
    2) Your "Data" variable is out of scope (hasn't been set)

    Here's code for TextBox11:
    Code:
    Private Sub TextBox11_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        Dim R As Long
        Dim lTB As Long
            
            lTB = 11
            R = CLng(RowNumber)
            Set Data = Worksheets("Data Form")
            
          ' There must be a row number and the user must left click with the mouse.
            If R <> 0 And Len(Me.Controls("TextBox" & lTB).Text) > 0 Then
               On Error Resume Next
                  Data.Cells(R, lTB).Hyperlinks(1).Follow True
                  If Err <> 0 Then MsgBox "Unable to open '" & Data.Cells(R, lTB).Value & "'."
               On Error GoTo 0
            End If
    End Sub
    To port this to TextBox12, just change the Private Sub line to TextBox12... and update the lTB variable to 12.

    I believe that should get you sorted.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Thanks for you reply but it is still not working, below is what i ahve changed Text Boxes 10,11 and 12 too, is it correct or have I done something wrong?

    Code:
    Private Sub TextBox10_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        Dim R As Long
        Dim lTB As Long
            
            lTB = 10
            R = CLng(RowNumber)
            Set Data = Worksheets("Data Form")
            
          ' There must be a row number and the user must left click with the mouse.
            If R <> 0 And Len(Me.Controls("TextBox" & lTB).Text) > 0 Then
               On Error Resume Next
                  Data.Cells(R, lTB).Hyperlinks(1).Follow True
                  If Err <> 0 Then MsgBox "Unable to open '" & Data.Cells(R, lTB).Value & "'."
               On Error GoTo 0
            End If
    End Sub
    
    Private Sub TextBox11_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        Dim R As Long
        Dim lTB As Long
            
            lTB = 11
            R = CLng(RowNumber)
            Set Data = Worksheets("Data Form")
            
          ' There must be a row number and the user must left click with the mouse.
            If R <> 0 And Len(Me.Controls("TextBox" & lTB).Text) > 0 Then
               On Error Resume Next
                  Data.Cells(R, lTB).Hyperlinks(1).Follow True
                  If Err <> 0 Then MsgBox "Unable to open '" & Data.Cells(R, lTB).Value & "'."
               On Error GoTo 0
            End If
    End Sub
    
    Private Sub TextBox12_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        Dim R As Long
        Dim lTB As Long
            
            lTB = 12
            R = CLng(RowNumber)
            Set Data = Worksheets("Data Form")
            
          ' There must be a row number and the user must left click with the mouse.
            If R <> 0 And Len(Me.Controls("TextBox" & lTB).Text) > 0 Then
               On Error Resume Next
                  Data.Cells(R, lTB).Hyperlinks(1).Follow True
                  If Err <> 0 Then MsgBox "Unable to open '" & Data.Cells(R, lTB).Value & "'."
               On Error GoTo 0
            End If
    End Sub

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    What kind of error are you getting back?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Hi Ken, I am not getting any error, when I click on the text box (Text Box 11 - Engineers Photos) it does nothing, the link works fine on the Datasheet though.

    Are you able to make the nessccary correction on the database and attach?

    Shazz
    xx
    Last edited by Shazz; 2012-05-09 at 04:41 PM. Reason: Added Text

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Here you go.

    FYI, I'm running on a 64bit Office system here, so I had to add a conditional compilation to wrap all your API calls in order to open your project. You can leave them there, but those calls should really be checked before you use this file in a 64 bit environment or it could blow up. As long as you run in 32bit Office though, (just because you run a 64 bit Windows does NOT mean you are running 32bit Office,) you'll be fine.

    I get an error telling me that the file couldn't be opened, but that's to be expected as I don't have the file your link points to.

    HTH,
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    It is still not working on the user form?? does it work for you by clicking the Engineers Photo Link in the Actual User Form?

    Shazz
    xx

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Yes, sure does.

    I opened the workbook, clicked "Open Data Form", and clicked "Next" to get to "Test". There is a link in the Engineers Photos there (View Photos). When I double click that it tries to open the file (and fails since I don't have it.)

    You're not seeing the same?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    Sorry yes it does work, I did not realise you had to double click, normally for links it is a single click.

    Being a bit cheeky here, is there a way to make it single click at all, as the other users probably wont realise either.

    Thanks so much for your help, your a star.

    Shazz
    xx

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Okay, so there is no Click event for a textbox, but there is for a button. So what you could do is:
    -Create a button that fits right on top of the textbox (I set up CommandButton4 on top of TextBox11)
    -Clear it's default caption
    -Set it's BackStyle to 0-fmBackStyleTransparent

    Now paste in the following code:
    Code:
    Private Sub CommandButton4_Click()
        Dim R As Long
        Dim lTB As Long
            
            lTB = 11
            R = CLng(RowNumber)
            Set Data = Worksheets("Data Form")
            
          ' There must be a row number and the user must left click with the mouse.
            If R <> 0 And Len(Me.Controls("TextBox" & lTB).Text) > 0 Then
               On Error Resume Next
                  Data.Cells(R, lTB).Hyperlinks(1).Follow True
                  If Err <> 0 Then MsgBox "Unable to open '" & Data.Cells(R, lTB).Value & "'."
               On Error GoTo 0
            End If
    End Sub
    
    Private Sub CommandButton4_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        Call CommandButton4_Click
    End Sub
    At this point you can comment out the old Textbox11 code as it is no longer necessary. Now if you single or double click the button (which is almost invisible on top of your textbox) it will fire the launch.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 1 of 2 1 2 LastLast

Posting Permissions

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