+ 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
    Forum Contributor
    Join Date
    10-01-2018
    Location
    Virginia Beach, VA
    MS-Off Ver
    365
    Posts
    129

    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)

Similar Threads

  1. [SOLVED] Excel 2007 : Shortcut/Macro to tick next entry in auto-filter list
    By starbearer in forum Excel General
    Replies: 13
    Last Post: 04-04-2020, 05:13 AM
  2. Replies: 0
    Last Post: 03-06-2017, 03:55 PM
  3. Data Entry duplicated in chronological order
    By kosti in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-29-2014, 07:27 AM
  4. Changing order of entries in auto filter
    By nicolepag in forum Excel General
    Replies: 0
    Last Post: 01-09-2012, 01:46 PM
  5. Data order entry in protected sheet
    By BRISBANEBOB in forum Excel General
    Replies: 4
    Last Post: 11-20-2010, 08:20 PM
  6. Calculate after cell entry/auto filter change
    By Drew Watson in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2010, 10:22 AM
  7. data entry for auto filter
    By Parry in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-14-2005, 06:00 AM

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