Got a problems & Solutions database created in Excel for Standard Life's internal websites. The dasebase is mostly fronted by a userform GUI. Here you can search for a problem, and once a problem is selected a solution is suggested in a textbox underneath.
Problem:
sometimes the solution offered suggests the user to seek more information from a website. For easy use I want a control box to appear when this is the case that the user can click on and it will take you to the website suggested. However I do not want the control box to be visible unless there is a website as part of the solution.
What I'm not doing:
Creating a separate control box for every solution that offers a website for further info.
Solution:
First, I created a control box with the default visible value to false.
Then, I added the following code to the click event of the problem selection box:
Private Sub txtsol_Change()
If Left(txtdetsol, 4) = "http" Then
cmdgo.Visible = True
Else
cmdgo.Visible = FalseEnd If
End Sub
Then added the following code to the contrl box that will take the user to the website:
Private Sub cmdgo_Click()
Dim sTxt As String
Dim x As Variant
txtbox = Me.txtdetsol.Value
x = Split(txtbox, " ")
ThisWorkbook.FollowHyperlink x(0)
End Sub
What this code effectively does is this:-
everytime a problem is selected and the solution appears, the code looks at the solution and says 'if the first 4 letters of this solutoin is "http" then show the link control box'.
The code behind the link control then splits all the text before the first space in the solutions box and creates this text as the hyperlink.
The only downside being that this only works when the web address is the first thing written in the solution. However this is a compromise that will still save alot of time.
Hopefully can further this improvement so that this will happen no matter where the link appears in the solution, whether it be the start, middle or end of a string.
Watch this space...
