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

[georgiboy]

Hi Golf,

I may have missed the point here but looking at the attempts and the recorded macro i would say the issue could be:

n has been defined as long and holds a row number, this means that when n is in the refers to section n may be the number 2.

You could use n as part of the reference for the naming of a range as below:

Sub test()    
    Dim n As Long, InstalDesc As String
    n = 3
    InstalDesc = "Dave"
    
    Worksheets("Task List").Names.Add Name:=InstalDesc, RefersTo:=Range("A" & n)
End Sub

The above will name range A3 as Dave

Hope this helps

[GolfingTitan]

Thanks, georgiboy.

The challenge is, I want the name range to be whatever is in D3 on the Task Entry tab, but each time they put a new task in D3, it would create a new named range.

Say you're on the Task Entry tab and you enter "Bake Cookies" in D3 and it fills in subsequent tasks automatically below (the point of the Task Entry tab is to save time with known subsequent tasks). You hit the button and it puts "Bake Cookies" in cell D4 on the Task List followed by the subsequent tasks. All this works, but where I need help is: I want D4 on the Task List tab to show up in the name box as "Bake_Cookies".

So then you go back to the Entry Form and enter "Measure Cupcakes" in D3 and it fills in all subsequent tasks for measuring cupcakes. You hit the button, and the spreadsheet finds the next empty row, skips it (so there's a space between tasks) and places Measure Cupcakes in cell D12 (again, all this works), and then creates named cell D12 on the Task List tab as "Measure_Cupcakes".

So in my Name Box, if I hit "Bake_Cookies" it goes to D4, if I click on "Measure_Cupcakes" it goes to D12.

So for each task you enter, it creates the named range for that task. Again, this could happen dozens of times, if not hundreds of times, for a project.

I have everything working in regards to transferring the data, all that works. I'm just not sure how to 1) get excel to create a new name range each time a new task is entered. 2) how to call out that named range on the Task List tab, even though it's pulling data from the Entry Tab (that's why I'm trying to add it where I'm trying to add it ... because it's pulling D3 "InstalDesc" and adding the word "Summary" to the end of it... so I'm trying to tell it to take that same cell and add it to the Name Range). 3) How to get rid of the spaces so the Named Range can actually work, but not get rid of the spaces when it pastes the value into the Task List.

Again, as I said, I'm in way over my head here... this may not even be possible. What I have in my code may be not even be close to what I need to get this to work, it's just where I'm at.

I have found plenty of articles showing me how VBA can USE named cells, but not many on how to create them. The few that showed how to create them were static in what they were showing (i.e., change XYZ cell to "this set variable"), but I'm looking for something more dynamic where it CREATES the named cell for EACH task as they enter it.

Thanks for your time though. I do really appreciate it.

[georgiboy]

I See,

Does the below get you any closer to where you need to be?

Sub CAD_Task_Entry()    
    Dim InstalDescOrig As String, InstalDesc As String
    Dim Model As Range
    Dim Drawing As Range
    Dim Index As Long
    Dim m As Long, n As Long
    Dim wsTEform As Worksheet, wsTaskList As Worksheet
    
    Set wsTEform = Sheets("Task Entry Form")
    Set wsTaskList = Sheets("Task List")
    
    Application.ScreenUpdating = False
    'Copy data from the input screen to the task list.
    InstalDescOrig = wsTEform.Range("D3").Value
    InstalDesc = Replace(InstalDescOrig, " ", "_")
    
    Set Model = wsTEform.Range("D5", Cells(Rows.Count, "D").End(xlUp)).Resize(, 2)
    Set Drawing = wsTEform.Range("I5", Cells(Rows.Count, "I").End(xlUp)).Resize(, 2)
        
    With wsTaskList
        '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") = InstalDescOrig & " Summary"
        .Names.Add Name:=InstalDesc, RefersTo:=.Range("D" & n)
        '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
    End With
    
    Application.ScreenUpdating = True
    
End Sub

[GolfingTitan]

I swear, it's like magic sometimes.

THANK YOU so much for taking the time to help... it works perfectly. I know VBA is powerful, but it still amazes me what it's able to do. Thank you again... VERY much appreciated!!

[Jan Karel Pieterse]

Note that adding many range names can slow down your file and you must remember to delete range names once you remove projects from the list. Why don't you just use the filter drop-down, that also provides an alphabetically sorted list which you can even search in. Using range names for this just seems wrong to me.

[GolfingTitan]

Thanks for the concern Jan. There are a few reasons why filtering won't work, but the main one is, the Task List is sorted and formatted with blank rows to keep tasks separate. It also group tasks together with their subtasks, and if we filtered, it would get rid of the formatting and just merge everything together.

I realize it's a stylized things, but this spreadsheet is design for "user friendliness", so it's not just straight data dump.

Again, I appreciate the tip and thought! We will monitor the named ranges and if it bogs down the system, we'll readdress it.

[Jan Karel Pieterse]

Right. I would've repeated the task ID on every row and used a conditional format to make the duplicated ones appear blank. That would enable you to use filtering and sorting without issues.