Linking data validation drop down selection to open userform

Copy the code below, right-click the tab of Sheet1, and paste the code into the window that appears. You will need to modify the code to match the names of the userforms and the control that you want your value to appear in, neither of which you gave in your post.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strName As String
    strName = Cells(Target.Row, 1).Value
    On Error GoTo NoGood
    Application.EnableEvents = False
    Sheets(strName).Activate
    Select Case strName
    Case "Project 1"
        Load UserForm1
        UserForm1.TextBox1.Text = Target.Value
        UserForm1.Show
    Case "Project 2"
        Load UserForm2
        UserForm2.TextBox1.Text = Target.Value
        UserForm2.Show
    Case "Project 3"
        Load UserForm3
        UserForm3.TextBox1.Text = Target.Value
        UserForm3.Show
    End Select
NoGood:
    Application.EnableEvents = True
End Sub

Thanks so much for your help! i've adapted the code and it works well. From what i gather, the code works by using the target value as a string in column 4 (in my code below) and then if there is a worksheet with the same name as that string, any data entered in that row will then open the worksheet and the userform. Is that pretty much right?

Yes - that is correct.

2. Party linked to No.1 is that if the user inserts a new column, say in column 1, the strName = cells (target.row, 4) in my code below will not sutomatically change to strName = cells (target.row, 5) - is there a way of locking this column number?

This one first - name a cell in the name column that you use - say "NameCell", and then use code like

strName = Cells(Target.Row, Range("NameCell").Column).Value

And as columns are inserted or deleted, you code will continue to work properly

1. There are certain cells in each row that will be entered manually by the user (i.e. not in a drop down box), and when they do i do not want it to link to the project worksheet/open userform. For example, would you be able to apply the code to columns greater than column 7 (i.e. in columns A:F the user can type in data and it will not link to the project worksheet)?

Start your code with this

If Target.Cells.Count > 1 Then Exit Sub

and then name a cell "StartCol" in the first column you want this to work on and use this as the second line

If Target.Column < Range("StartCol").Column Then Exit Sub

3. Sometimes the user will need to delete an entry in the tracker, but at present, when the delete button is pressed it links to the project worksheet/userform. Is there a way of being able to press delete and the code not running?

Start your code with

Application.EnableEvents = False

and finish it with

Application.EnableEvents = True

4. In the example in my post above, each stage of the project had a different year (2014, 2015, 2016) - when a change is made in the tracker is there a way to link the date in the corresponding column above so that it is entered into the date.textbox in the userform?

Depends on the specifics, but code like this will work if you name the column with the dates "DateCol"

UserForm2.DateTextBox.Text = Format(Intersect(Target.EntireRow,Range("DateCol")).Value,"mm/dd/yyyy")