Mixed Bag - VBA index/match, ComboBox Text derived from Named List based on cell formula, etc...

[kkinsj]

Afternoon everyone,

I'm really struggling to figure out what is going on now. I've tried to create an excel document that will act as a job tracker throughout various phases of construction. The initial worksheet layout is based on shapes used as tabs, which determine which columns are visible when clicked from a VBA Module. (Unhide All Columns to review)

From there, I've inserted 3 ActivX ComboBoxes that are meant to be used as alternative search fields. The user will rarely have/know all three of the values so I want to make sure they can populate all of the available data by using one of these 3. I initially attempted to use 3 option buttons that would alternate linkedcell and rowsource via macro, but that started to get funky. For now, I'm stalled at the ComboBox1_Change event and haven't proceeded with the other two in VBA . When the combobox1_change event starts, the other two comboboxes are hidden and textboxes are visible instead, and will populate with the related data.

When the ComboBoxes are selected and the user starts typing, the rowsource is formula driven based on what is typed, and eliminates all options not containing the data typed, dynamically reducing the dropdown list. Extra columns were added to generate this effect and those in-cell formulas can be found in their respective "If found, Frequency, & Searchable List" columns.

The current data range begins with Column FV and extends through NE

This whole setup feels like a frankenstein of all sorts of approaches and now when I type in ComboBox1, excel crashes. Yesterday morning I had only 5 Dim'd ranges in the ComboBox1_Change macro and I was able to get the dynamic dropdown.

All dim'd ranges are tied to their respective named ranges found in the name manager. This may be uneccesary or overkill and maybe a table is needed instead?

Right now I think I just need someone to tell me what the heck I'm doing wrong, and point me in the right direction. I'm a VBA novice and have scrapped together what I have from various google results, and tutorials that I could find. Some of the Macros were tailored to fit my needs and may just be too much for Excel to process?

I may need some advice on which subroutines I should be assigning certain macros to eliminate the current headache.

This is the file I am working with.
https://www.dropbox.com/s/829rehgft...ith Userform and separate Srch CBOs.xlsm?dl=0

These are the current Macros I have at a glance

Private Sub AddBttn_Click()
UserForm1.Show
End Sub
Private Sub WorkSheet1_Initialize()
    With Sheet1
        TextBox4.Visible = False
        TextBox5.Visible = False
        TextBox6.Visible = False
    End With
End Sub


Private Sub ClrBttn_Click()
    With Sheet1
        ComboBox1.Value = Null
        ComboBox1.Visible = True
        TextBox4.Visible = False
        ComboBox2.Value = Null
        ComboBox2.Visible = True
        TextBox5.Visible = False
        ComboBox3.Value = Null
        ComboBox3.Visible = True
        TextBox6.Visible = False
    End With
End Sub

Private Sub ComboBox1_Change()
    With Sheet1
    
        ComboBox1.DropDown
        ComboBox1.LinkedCell = "GA1"
        ComboBox1.ListFillRange = "FNAME"
        ComboBox2.Visible = False
        ComboBox3.Visible = False
        TextBox4.Visible = False
        TextBox5.Visible = True
        TextBox6.Visible = True
        
            Dim rng1 As Range
                Set rng1 = ActiveWorkbook.Names("FILE_NAME").RefersToRange
            
            'Entry Number used as reference for Index Match as follows...
            Dim rng2 As Range
                Set rng2 = ActiveWorkbook.Names("ENTRY").RefersToRange
                    EntryNo = Application.IfError(Application.Index(rng2, Application.Match(ComboBox1.Text, rng1, 0)), "")
            
            'File Name TextBox
                    TextBox4.Text = Application.IfError(Application.Index(rng1, Application.Match(EntryNo, rng2, 0)), "")
            'Hub Name
            Dim rng3 As Range
                Set rng3 = ActiveWorkbook.Names("HUB").RefersToRange
                    TextBox1.Text = Application.IfError(Application.Index(rng3, Application.Match(EntryNo, rng2, 0)), "")
            'NFID
            Dim rng4 As Range
                Set rng4 = ActiveWorkbook.Names("NFID").RefersToRange
                    TextBox5.Text = Application.IfError(Application.Index(rng4, Application.Match(EntryNo, rng2, 0)), "")
            'Date Received
            Dim rng5 As Range
                Set rng5 = ActiveWorkbook.Names("DATE_RECEIVED").RefersToRange
                    TextBox2.Text = Application.IfError(Application.Index(rng5, Application.Match(EntryNo, rng2, 0)), "")
            'Aerial Polyset(1)
            Dim rng6 As Range
                Set rng6 = ActiveWorkbook.Names("Aerial_POLYSET__1").RefersToRange
                    TextBox6.Text = Application.IfError(Application.Index(rng6, Application.Match(EntryNo, rng2, 0)), "")
            'FQN ID
            Dim rng7 As Range
                Set rng7 = ActiveWorkbook.Names("FQN_ID").RefersToRange
                    TextBox3.Text = Application.IfError(Application.Index(rng7, Application.Match(EntryNo, rng2, 0)), "")
            'Primary Site
            Dim rng8 As Range
                Set rng8 = ActiveWorkbook.Names("Primary_Site").RefersToRange
                    TextBox7.Text = Application.IfError(Application.Index(rng8, Application.Match(EntryNo, rng2, 0)), "")            
            'Secondary Sites
            Dim rng9 As Range
                Set rng9 = ActiveWorkbook.Names("Secondary_Sites").RefersToRange
                    TextBox8.Text = Application.IfError(Application.Index(rng8, Application.Match(EntryNo, rng2, 0)), "")
            'Aerial Polyset(2)
            Dim rng10 As Range
                Set rng10 = ActiveWorkbook.Names("Aerial_POLYSET__2").RefersToRange
                    TextBox9.Text = Application.IfError(Application.Index(rng10, Application.Match(EntryNo, rng2, 0)), "") 
            'Aerial Polyset(3)
            Dim rng11 As Range
                Set rng11 = ActiveWorkbook.Names("Aerial_POLYSET__3").RefersToRange
                    TextBox10.Text = Application.IfError(Application.Index(rng11, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Aerial Polyset(4)
            Dim rng12 As Range
                Set rng12 = ActiveWorkbook.Names("Aerial_POLYSET__4").RefersToRange
                    TextBox11.Text = Application.IfError(Application.Index(rng12, Application.Match(EntryNo, rng2, 0)), "")
                        
            'Aerial Polyset(5)
            Dim rng13 As Range
                Set rng13 = ActiveWorkbook.Names("Aerial_POLYSET__5").RefersToRange
                    TextBox12.Text = Application.IfError(Application.Index(rng13, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Aerial Polyset(6)
            Dim rng14 As Range
                Set rng14 = ActiveWorkbook.Names("Aerial_POLYSET__6").RefersToRange
                    TextBox13.Text = Application.IfError(Application.Index(rng14, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Aerial Polyset(7)
            Dim rng15 As Range
                Set rng15 = ActiveWorkbook.Names("Aerial_POLYSET__7").RefersToRange
                    TextBox14.Text = Application.IfError(Application.Index(rng15, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Aerial Polyset(8)
            Dim rng16 As Range
                Set rng16 = ActiveWorkbook.Names("Aerial_POLYSET__8").RefersToRange
                    TextBox15.Text = Application.IfError(Application.Index(rng16, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Underground Polyset(1)
            Dim rng17 As Range
                Set rng17 = ActiveWorkbook.Names("Underground_POLYSET__1").RefersToRange
                    TextBox16.Text = Application.IfError(Application.Index(rng17, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Underground Polyset(2)
            Dim rng18 As Range
                Set rng18 = ActiveWorkbook.Names("Underground_POLYSET__2").RefersToRange
                    TextBox17.Text = Application.IfError(Application.Index(rng18, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Underground Polyset(3)
            Dim rng19 As Range
                Set rng19 = ActiveWorkbook.Names("Underground_POLYSET__3").RefersToRange
                    TextBox18.Text = Application.IfError(Application.Index(rng19, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Underground Polyset(4)
            Dim rng20 As Range
                Set rng20 = ActiveWorkbook.Names("Underground_POLYSET__4").RefersToRange
                    TextBox19.Text = Application.IfError(Application.Index(rng20, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Underground Polyset(5)
            Dim rng21 As Range
                Set rng21 = ActiveWorkbook.Names("Underground_POLYSET__5").RefersToRange
                    TextBox20.Text = Application.IfError(Application.Index(rng21, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Underground Polyset(6)
            Dim rng22 As Range
                Set rng22 = ActiveWorkbook.Names("Underground_POLYSET__6").RefersToRange
                    TextBox21.Text = Application.IfError(Application.Index(rng22, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Underground Polyset(7)
            Dim rng23 As Range
                Set rng23 = ActiveWorkbook.Names("Underground_POLYSET__7").RefersToRange
                    TextBox22.Text = Application.IfError(Application.Index(rng23, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Underground Polyset(8)
            Dim rng24 As Range
                Set rng24 = ActiveWorkbook.Names("Underground_POLYSET__8").RefersToRange
                    TextBox23.Text = Application.IfError(Application.Index(rng24, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Plan Type
            Dim rng25 As Range
                Set rng25 = ActiveWorkbook.Names("Plan_Type").RefersToRange
                    TextBox24.Text = Application.IfError(Application.Index(rng25, Application.Match(EntryNo, rng2, 0)), "")
                    
            'CITY
            Dim rng26 As Range
                Set rng26 = ActiveWorkbook.Names("CITY").RefersToRange
                    TextBox25.Text = Application.IfError(Application.Index(rng26, Application.Match(EntryNo, rng2, 0)), "")
                    
            'County
            Dim rng27 As Range
                Set rng27 = ActiveWorkbook.Names("County").RefersToRange
                    TextBox26.Text = Application.IfError(Application.Index(rng27, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Jurisdiction
            Dim rng28 As Range
                Set rng28 = ActiveWorkbook.Names("Jurisdiction").RefersToRange
                    TextBox27.Text = Application.IfError(Application.Index(rng28, Application.Match(EntryNo, rng2, 0)), "")
                    
            'Date CONSTRUCTION START
            Dim rng29 As Range
                Set rng29 = ActiveWorkbook.Names("Date_CONSTRUCTION_START").RefersToRange
                    TextBox28.Text = Application.IfError(Application.Index(rng29, Application.Match(EntryNo, rng2, 0)), "")
                    
     
    End With
End Sub

 

[kkinsj]

As a side note... There are three command buttons on the worksheet. (Add entry, Update Entry, and Clear Fields.) Add entry brings up a userform that works. Clear Entry clears all combobox values which then clears the dependant textboxes.

One additional issue is getting date values to populate as dates in the linked textboxes. I don't see how to format the textbox values to reflect this format.

 

[kkinsj]

Just noticed an interesting thing... If I start typing "RDHA" the dropdown list seems unaffected. However, if I start to type "NWHL", excel crashes when I type N every time. I haven't gone through the alphabet, but I wonder if there is some significance to that...

 

[kkinsj]

*Bump* I'm not sure if I've asked these questions in the correct forum. I've had a lot of views but no luck with any response? Can someone please provide a redirect if that is needed? Thanks!