Create a DropDown List that Auto Fills as you type

    Step1
    Create a List on sheet 2 Cells A1:A11

    		 Step 2
    On Sheet 1 Put the Dropdownlist in Cell C1

    Step 3
    Next we need to Copy the range from Sheet 2 cells A1:A11
    Then goto Sheet 1 right click and Paste as Link in cell C9.


    Step 4
    Next we need to connect Cell C1 with the Downdown List of names in cell C9.
    Here are two (2) methods.
    1. Place a "X" in cells B1:B9 This is called the connection method.
    2. Place a Double Space bar in cells B1:B9. Same method but you can not see the space bars so cell look empty.
    3. If there is something in cell B1:B9 you can go around using another path like Cells B1 and A1:A9 and B9


    Step 5
    Now start typing in the Dropdown List and it will try and auto complete for you.
    Note that you can color the list on sheet1 the same as the background so it is not seen or you can hide those rows.

    Notes to remember.
    1. The linked list must reside in the same column that the dropdown list is in.
    2. Dropdownlist must be connected to the linked list using text or double spacebar.
    3. With names that are alike, like Dan Carpenter and Daniel Carpenter, you will not be offered Daniel Carpenter until "n" has been typed to exclude Dan.


Drop Down Lists ..... See Excel file "Tabs.xlsm"

SendKeys ... SendKey example

Creating a Dropdown list using Named Ranges

    Dynamic Named Range used as dropdown list and is dynamic to row 999 or beyond.

    Named ranges using formula’s

    =OFFSET(Sheet2!$BB$4,,,996-CountBlank (Sheet2!$BB$4:$BB$999),1) ‘ if cells contain Formula’s (Text and Numbers Ok! as well)

    Named ranges using text or numbers
    =OFFSET(Sheet2!$A$4,,,COUNTA(Sheet2!$A$4:$A$999),1) ‘ if cells contain text or numbers (Note! Can not be used if column contains Formula's)

    The Dynamic Drop down list is activated by Worksheet “On Change“  command VBA editor Sheet1. Which controls the macro Empl_Load.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F2")) Is Nothing Then
    If Range("F2").Value <> Empty And Range("B5").Value <> Empty Then Empl_Load
    ‘This “if” command controls macro Empl_Load
    End If
    End Sub

    The EMPL_Load Macro (this uses row 1 sheet2 as mapped locations


    Sub Empl_Load()
    Dim EmpRow As Long
    Dim EmpCol As Long

    With Sheet1
    If .Range("B5").Value = Empty Then
    MsgBox "Please enter a valid Employee from the dropdown List"
    Exit Sub
    End If
    .Range("B1").Value = True

     EmpRow = .Range("B5").Value ‘ Study this as it is what puts the data in the correct place
     For EmpCol = 1 To 28
        .Range(Sheet2.Cells(1, EmpCol).Value).Value = Sheet2.Cells(EmpRow, EmpCol).Value
      Next EmpCol

    .Range("B1").Value = False
    End With
    End Sub


    Lets look at this more closely
    EmpRow = .Range("B5").Value ‘ So EmpRow becomes Row "4" on Sheet2

    For EmpCol = 1 To 28 ' This will cycle through 28 columns
    .Range(Sheet2.Cells(1, EmpCol).Value).Value = Sheet2.Cells(EmpRow, EmpCol).Value ' Lets look at this broken into 2 parts

    Part1 - The Destination Cell will become cell "J2"
    .Range(Sheet2.Cells(1,EmpCol).Value).Value) would = Sheet2 cells(1 or Row 1, EmpCol or Col 1).Value which is "J2" See cell "A1" above. "J2" is Employee ID

      Part 2 - So "J2" will be filled with the Value in Cells(EmpRow,EmpCol)
      =Sheet2.Cells(EmpRow, EmpCol).Value 'The contents in Sheet2.Cells(4,1).Value
           EmpRow = Cell B5 on sheet1 which is 4
           EmpCol = 1 and will change for each column because EmpCol = 1 to 28

      Next EmpCol

Using a Validation list to control the content of a second validation list

  1. Create a validation list of the header names. In this example that would be cells A1, B1, C1
  2. Create a second validation list. This validation list will be controlled with a formula.
    Note!: The maximum dynamic number of entries in this example will not exceed 200.
    You can change the number 200 to whatever you think is needed.

    The formula for the second list will be a "offset" formula.
    =offset(Referance,rows,cols,[height],[width])

    Here is the formula to use for the Second  validation list.
    =OFFSET(A1,1,MATCH(E1,A1:C1,0)-1,counta(offset(A1,1,MATCH(E1,A1:C1,0)- 1,200,1)),1)

    3. Watch the video

Tabs and "Target"

Remember all the conditional formatting that was done.
Formula: =$B$2=COLUMN()
Then Choose Color and Borders, Text, Text Color and so forth.


    Tab "General Info" and the other 5 tabs are controlled by a Target statment located in the vba editor on the Sheet not in the module and uses the SelectionChange declaration seen below.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("E4:J4")) Is Nothing Then ' This makes each horizontal cell in the range a target button
    Range("B2").Value = Target.Column ' The target column numberis placed in cell B2 - So Column "E" is the 5th column noted in cell B2
    Range("F2").Select
    switchHorizontialTabs ' This is the macro that will run and control what is seen on screen
    End If
    If Not Intersect(Target, Range("E66:E129")) Is Nothing Then ' This is for the Vertical tabs not seen in the picture above
    If Target.Value = Empty Or Target.Value = "Select Option" Then Exit Sub
    SwitchVerticalTabs
    End If

    End Sub

Here are the macros for the Horizontal and Vertical targeted cells

    Sub switchHorizontialTabs()
    Dim SelCol As Long
    Dim FirstRow As Long

    With Sheet1
    SelCol = Range("B2").Value ' SelCol is 5 because cell B2 is 5 which was created by the Target statement above
    .Range("5:144").EntireRow.Hidden = True ' This Hides all rows
    FirstRow = 5 + ((SelCol - 5) * 20) ' This calculates the First Row and names it "FirstRow"
    .Range(FirstRow & ":" & FirstRow + 19).EntireRow.Hidden = False 'This defines the range to unhide. FirstRow = 5 Thru FirstRow +19 or 24 (5:24)
    End With
    End Sub

    Sub SwitchVerticalTabs()
    Dim SelRow As Long
    Dim FirstRow As Long
    SelRow = Right(ActiveCell.Row, 1) - 5
    With Sheet1
    .Range("65:144").EntireRow.Hidden = True
    FirstRow = 65 + ((SelRow - 1) * 20)
    .Range(FirstRow & ":" & FirstRow + 19).EntireRow.Hidden = False
    .Range("B3").Value = SelRow + FirstRow
    .Range("F2").Select
    End With
    End Sub

Conditional Formatting

    Formula is: =MOD(Row(),2)=0
    Tthen Choose Color and Boarders Text,Text Color and so forth.

SendKey Example

    Sub CreatePDFForms()
    Dim PDFTemplateFile, NewPDFName, SavePDFFolder, LastName As String
    Dim ApptDate As Date
    Dim CustRow, LastRow As Long
    With Sheet1
    If .Range("G18").Value = Empty Or .Range("G20").Value = Empty Then
    MsgBox "Both PDF Template and Saved PDF Locations are required for macro to run"
    Exit Sub
    End If

    LastRow = .Range("E9999").End(xlUp).Row 'Last Row
    PDFTemplateFile = .Range("G18").Value 'Template File Name
    SavePDFFolder = .Range("G20").Value 'Save PDF Folder
    ThisWorkbook.FollowHyperlink PDFTemplateFile
    Application.Wait Now + 0.00006

    For CustRow = 5 To LastRow
    LastName = .Range("E" & CustRow).Value 'Last Name
    ApptDate = .Range("G" & CustRow).Value 'Appt Date
    Application.SendKeys "{Tab}", True
    Application.SendKeys LastName, True
    Application.Wait Now + 0.00001

    Application.SendKeys "{Tab}", True
    Application.SendKeys .Range("F" & CustRow).Value, True 'First Name
    Application.Wait Now + 0.00001
    Application.SendKeys "{Tab}", True
    Application.SendKeys "{Tab}", True

    Application.SendKeys .Range("I" & CustRow).Value, True 'Address
    Application.Wait Now + 0.00001
    Application.SendKeys "{Tab}", True

    Application.SendKeys .Range("J" & CustRow).Value, True 'City
    Application.Wait Now + 0.00001
    Application.SendKeys "{Tab}", True

    Application.SendKeys .Range("K" & CustRow).Value, True 'State
    Application.Wait Now + 0.00001
    Application.SendKeys "{Tab}", True

    Application.SendKeys .Range("L" & CustRow).Value, True 'Zip
    Application.Wait Now + 0.00001
    Application.SendKeys "{Tab}", True

    Application.SendKeys .Range("M" & CustRow).Value, True 'Email
    Application.Wait Now + 0.00001
    Application.SendKeys "{Tab}", True
    Application.SendKeys "{Tab}", True

    Application.SendKeys Format(.Range("N" & CustRow).Value, "###-###-####"), True 'Phone
    Application.Wait Now + 0.00001
    Application.SendKeys "{Tab}", True

    Application.SendKeys "^(p)", True
    Application.Wait Now + 0.00003
    Application.SendKeys "{Enter}", True
    Application.Wait Now + 0.00007

    If Dir(SavePDFFolder & "\" & LastName & "_" & Format(ApptDate, "DD_MM_YYYY") & ".pdf") <> Empty Then Kill (SavePDFFolder & "\" & LastName & "_" & Format(ApptDate, "DD_MM_YYYY") & ".pdf")
    Application.SendKeys "%(n)", True
    Application.Wait Now + 0.00002
    Application.SendKeys SavePDFFolder & "\" & LastName & "_" & Format(ApptDate, "DD_MM_YYYY") & ".pdf"
    Application.Wait Now + 0.00003
    Application.SendKeys "%(s)", True
    Application.Wait Now + 0.00002

    Next CustRow
    Application.SendKeys "^(q)", True
    Application.SendKeys "{numlock}%s", True

    End With
    End Sub