Looking for ComboBox Masters!! Please

[Aaron DOJ]

I have ran into many road blocks trying to figure out how to combobox while keeping my original (pre-combobox) settings.

First off let me explain why I am using combobox, the document I have made, there are complaints about the dropdown lists being too small (Font size). I have tried the zoom commands and they were not liked so now I am using combobox.

My pre-settings were as follows:
-K7 has Data Validation (DV) =$AE$17:$AE$26—User chooses their Department

-O7 has DV =INDIRECT(K7) —User chooses their Section and then S7 will auto fill with accounting information. These tables for the sections are in AF thru AN.

-W7 has DV =Names and VBA code (see below) to instantly change the name of a work location to the physical address location once a selection has been made.

If Target.Address(0, 0) = "W7" Then
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo exitHandler:
    Target.Value = Worksheets("TEDS").Range("AR16") _
        .Offset(Application.WorksheetFunction _
        .Match(Target.Value, Worksheets("TEDS").Range("Names"), 0), 0)
End If
 
 
exitHandler:
    Application.EnableEvents = True

I added in the code for combobox (see below), put in the combobox and everything looked great when I went to K7, list was bigger and had all the departments. I moved on to O7 and no combobox so I went to DV and got the following “The selection contains more than one type of validation. Erase current settings and continue?” OK or Cancel. Well I figured clearly I can’t do cancel as nothing will change and I so I have to go with OK and start over. I put in the =INDIRECT(K7) back in and it will no longer show me the updated dropdown list for a user to select their section based on what department they chose...it's just a white box. How can I fix this issue?

Lastly, W7 has the drop-down list but won’t convert to address after selection is made. How can I fix this issue?

Private Sub ComboBox1_Change()
On Error Resume Next
xRg.Value = Me.ComboBox1.Text
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo LblExit
With Me.ComboBox1
.Visible = False
If Target.Validation.Type = xlValidateList Then
Target.Validation.InCellDropdown = False
.ListWidth = 120
.ListFillRange = ""
.ListFillRange = Target.Validation.Formula1
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width
.Height = Target.Height
.Font.Size = 16
.Visible = True
Set xRg = Target
End If
End With
LblExit:
Exit Sub

-Aaron

 

[Aaron DOJ]

Community,

Is there anything I can add to assist someone in helping me solve these issues?

 

[Aaron DOJ]

Still hoping to find someone to help me out, this is literally now the last thing I need to finalize my project I've been working on for seven months.