Create Name in Name Box

[GolfingTitan]

Hey all... I'm in way over my head on this one, so I'm throwing myself at the mercy of the community.

I'm on Office 365, Windows 10.

I have a spreadsheet which is a form (on a tab called Task Entry Form). The end user fills out that form, hits a button, and that button takes the information and places it in the correct columns in the tracker, which is on another worksheet (called Task List). This process repeats what could be several hundred times with hundreds of tasks, which we then assign a priority and sort by that priority to get the order the project has to go in.

So, what happens once we're done with all that is, we'll be sitting in a meeting and want to talk about a certain task... but not knowing exactly where that task is, we're scrolling up and down, up and down, looking for this task so we can look at it. Huge time sink.

What I would like to have happen is... when the end user creates the task on the Entry Form and hits the button, it pastes the data and then takes the first line of what it pasted and before it adds "Summary" (or after, honestly doesn't matter) and CREATES the named range to that cell. This would allow us to use the Name Box to jump to tasks, and everything in the Name Box would be alphabetical, which will be much easier to find, but not mess up the priority order by sorting the data alphabetically.

I have tried several ways and I'm just getting absolutely no where. I'm pretty new to VBA, but I'm learning it. But there's just so much here, I'm not sure where to even start.

Below is (part of) my current code with some commented lines where I've failed to get it to do anything. I also have the recorded Macro there for reference.

The line

    .Cells(n, "D") = InstalDesc & " Summary"

takes the cell I want to be in the name box (cell D3 on the Task Entry worksheet) and adds "Summary" to the end of whatever is in Cell D3. (InstalDesc is the cell from the Entry Form that creates the Summary line the other data goes under.) So I'm trying to play off that in failed code below.

I can go on explaining, but hopefully the commented code below and my pseudo-explanation above is enough to explain what's going on.

Sub CAD_Task_Entry()Dim InstalDesc As String
Dim Model As Range
Dim Drawing As Range
Dim Index As Long
Dim m As Long, n As Long


Application.ScreenUpdating = False
'Copy data from the input screen to the task list.
Sheets("Task Entry Form").Select
InstalDesc = Range("D3")
Set Model = Range("D5", Cells(Rows.Count, "D").End(xlUp)).Resize(, 2)
Set Drawing = Range("I5", Cells(Rows.Count, "I").End(xlUp)).Resize(, 2)
    
Index = Range("Q2")
With Sheets("Task List")
   
    'get first row
    n = .Range("D:X").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If n = 3 Then n = 4 Else n = n + 2
    
    'color first row
    .Range("A" & n & ":Z" & n).Interior.Color = 15189684
    .Range("A" & n & ":Z" & n).Font.Bold = True
    .Cells(n, "D") = InstalDesc & " Summary"


' Recorded Macro


'    Sub Macro1()
'
' Macro1 Macro
'
'    Range("D4").Select
'    ActiveWorkbook.Names.Add Name:="AirDuct", RefersToR1C1:="='Task List'!R4C4"
'End Sub


    
    Range(n, "D").Select
    InstalDesc = Replace(strString, " ", "_") 'trying to get rid of spaces and replace with underscore
    ActiveWorkbook.Names.Add Name:="InstalDesc", RefersToR1C1:="='Task List'!R4C4"
    
    
'    Sheets("Task List").Select.Names.Add Name:=InstalDesc, RefersTo:=n (debugged)


'    Worksheets("Task List").Names.Add Name:=InstalDesc, RefersTo:=n (did nothing)


'The named range has to use D3 on the Task Entry Form, but the actual named range has to appear on the Task List worksheet.
'Spaces - either condense the words or do underscores, either is fine... would prefer underscores

Range("A2").Select
End With
Application.ScreenUpdating = True
Sheets("Task Entry Form").Select
Range("D3").Select
End Sub