+ Reply to Thread
Results 1 to 3 of 3

VBA - Data entry auto filter into alphateical order

  1. #1
    Registered User
    Join Date
    02-11-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    4

    VBA - Data entry auto filter into alphateical order

    Good afternoon all

    Struggling with some coding. When I submit an entry into a form, it currently dumps that information at the bottom of my table and have to manually filter A-Z. What I would like to do is when that form is submitted, for that entry to automatically populated into a my listin its rightful place alphabetically.

    Still learning here but is it this piece of code that I need changing below?

    n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row

    sh.Unprotect "1234"

    sh.Range("A" & n + 1).Value = Me.TextBox1.Value
    sh.Range("B" & n + 1).Value = Me.TextBox2.Value
    sh.Range("C" & n + 1).Value = Me.TextBox3.Value
    sh.Range("D" & n + 1).Value = Me.ComboBox1.Value
    sh.Range("E" & n + 1).Value = Me.TextBox4.Value
    sh.Range("F" & n + 1).Value = Me.ComboBox2.Value
    sh.Range("G" & n + 1).Value = Me.TextBox5.Value
    sh.Range("H" & n + 1).Value = Me.TextBox6.Value
    sh.Range("I" & n + 1).Value = Me.ComboBox3.Value
    sh.Range("J" & n + 1).Value = Me.TextBox7.Value
    sh.Range("K" & n + 1).Value = Me.TextBox8.Value
    sh.Range("L" & n + 1).Value = Me.TextBox9.Value
    sh.Range("M" & n + 1).Value = Me.TextBox10.Value
    sh.Range("N" & n + 1).Value = Me.TextBox11.Value
    sh.Range("O" & n + 1).Value = Me.ComboBox4.Value
    sh.Range("P" & n + 1).Value = Me.TextBox12.Value
    sh.Range("Q" & n + 1).Value = Me.ComboBox5.Value
    sh.Range("R" & n + 1).Value = Me.TextBox13.Value
    sh.Range("S" & n + 1).Value = Me.ComboBox6.Value
    sh.Range("T" & n + 1).Value = Me.TextBox14.Value

    I have recorded a macro below which is a bit clunky but does the trick but doesn't give for a good user experience

    Sheets("SA Master").Select
    ActiveWorkbook.Worksheets("SA Master").ListObjects("Table1").Sort.SortFields. _
    Clear
    ActiveWorkbook.Worksheets("SA Master").ListObjects("Table1").Sort.SortFields. _
    Add Key:=Range("Table1[[#All],[Site Code]]"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("SA Master").ListObjects("Table1").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    I look forward to your responses

  2. #2
    Registered User
    Join Date
    02-11-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    4

    Re: VBA - Data entry auto filter into alphateical order

    Forgot to mention, Column A is where I have or will be applying my filtering to for alphabetical order

  3. #3
    Registered User
    Join Date
    10-01-2018
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2013
    Posts
    99

    Re: VBA - Data entry auto filter into alphateical order

    Are you using a VBA userform to enter the data? If so, do you have a command button coded for "submit"?

    If the answer to both is yes, then it may be as simple as adding a few lines to the click event for that button that apply an autofilter. Need to see the code for your userform to be sure.


    Also, when posting code, use the board's code tags like so:


    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1