How to sort columns automatically when any cell is updated?

M

Msears

Board Regular
Joined
Apr 14, 2022
Messages
56
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
I looked over previous threads and can't seem to find on solution to tackle this. I want this sheet to auto sort by Date (Oldest to Newest), Domain (Alphabetical), Goal (Smallest to Largest), every time new data is entered? And if there is code for it to separate by domains entered that would be great to have too, for example an empty row between different domains? I hope this makes sense?
 

Attachments

  • 50DA1AE6-741C-4BDF-9059-1F68E6FF9698.jpeg
    50DA1AE6-741C-4BDF-9059-1F68E6FF9698.jpeg
    149.1 KB · Views: 11

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
"every time new data is entered?"
how new data is enterred? by manual input in the sheet, or by running macro in the button?
Could you share the code of the button named "Click here to enter data"
???
 
Upvote 0
Below code run a separate sub, run it manually whenever needed:
VBA Code: 
Option Explicit
Sub test()
Dim lr&, i&
lr = Cells(Rows.Count, "B").End(xlUp).Row
Range("B2:G" & lr).Sort key1:=Range("c2"), key2:=Range("b2"), key3:=Range("D2")
    For i = lr To 2 Step -1
        If i > 2 And Cells(i, 3).Value <> Cells(i - 1, 3).Value Then Cells(i, 3).EntireRow.Insert
    Next
End Sub
before.JPG
after.JPG
 
Upvote 0
bebo021999 said:
"every time new data is entered?"
how new data is enterred? by manual input in the sheet, or by running macro in the button?
Could you share the code of the button named "Click here to enter data"
???
Click to expand...
I have attached the code below
Private Sub UserForm_Initialize()

'Empty DateTextBox
DateTextBox.Value = ""

'Empty SettingTextBox
SettingTextBox.Value = ""

'Empty GoalTextBox
GoalTextBox.Value = ""

'Empty NotesTextBox
NotesTextBox.Value = ""

'Empty TimeTextBox
TimeTextBox.Value = ""

'Empty TrialsTextBox
TrialsTextBox.Value = ""

'Empty OpportunitiesTextBox
OpportunitiesTextBox.Value = ""

'Empty RatioTextBox
RatioTextBox.Value = ""

'Empty LimitHoldTextBox
LimitHoldTextBox.Value = ""

'Empty BxTextBox1
BxTextBox1.Value = ""

'Empty BxTextBox2
BxTextBox2.Value = ""

'Empty BxTextBox3
BxTextBox3.Value = ""

'Empty BxTextBox3
BxTextBox3.Value = ""

'Empty BxTextBox4
BxTextBox4.Value = ""

'Empty BxTextBox6
BxTextBox6.Value = ""

'Empty BxTextBox9
BxTextBox9.Value = ""

'Empty InTextBox8
InTextBox8.Value = ""

'Empty InTextBox7
InTextBox7.Value = ""

'Empty InTextBox10
InTextBox10.Value = ""

'Empty InTextBox11
InTextBox11.Value = ""

'Empty InTextBox12
InTextBox12.Value = ""

'Empty InTextBox13
InTextBox13.Value = ""

'Empty DomainComboBox1
DomainComboBox1.Clear

'Empty PromptComboBox2
PromptComboBox2.Clear

'Empty ScheduleComboBox3
ScheduleComboBox3.Clear

'Fill DomainComboBox1
With DomainComboBox1
.AddItem "Echoic"
.AddItem "Group Skills"
.AddItem "Imitation"
.AddItem "Independent Play"
.AddItem "Intraverbal"
.AddItem "Listener Responding"
.AddItem "Linguistics"
.AddItem "LRFFC"
.AddItem "Mand"
.AddItem "Match to Sample"
.AddItem "Math"
.AddItem "Personal Independence"
.AddItem "Reading"
.AddItem "Social"
.AddItem "Tact"
.AddItem "Writing"
End With

'Fill PromptComboBox2
With PromptComboBox2
.AddItem "Verbal"
.AddItem "Gestural"
.AddItem "Model"
.AddItem "Partial"
.AddItem "Full"
End With

'Fill ScheduleComboBox3
With ScheduleComboBox3
.AddItem "FI"
.AddItem "FR"
.AddItem "NCR"
.AddItem "VI"
.AddItem "VR"
End With

'Uncheck NoCheckBox
NoCheckBox.Value = False

'Set Focus on DateTextBox
DateTextBox.SetFocus

End Sub

Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet8 active
Sheet8.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1

'Transfer information
Cells(emptyRow, 2).Value = DateTextBox.Value
Cells(emptyRow, 3).Value = SettingTextBox.Value
Cells(emptyRow, 4).Value = DomainComboBox1.Value
Cells(emptyRow, 5).Value = GoalTextBox.Value
Cells(emptyRow, 6).Value = PromptComboBox2.Value
Cells(emptyRow, 7).Value = TrialsTextBox.Value
Cells(emptyRow, 8).Value = OpportunitiesTextBox.Value
Cells(emptyRow, 10).Value = ScheduleComboBox3.Value
Cells(emptyRow, 11).Value = TimeTextBox.Value
Cells(emptyRow, 12).Value = RatioTextBox.Value
Cells(emptyRow, 13).Value = LimitHoldTextBox.Value
Cells(emptyRow, 14).Value = NotesTextBox.Value
Cells(emptyRow, 15).Value = BxTextBox1.Value
Cells(emptyRow, 16).Value = InTextBox8.Value
Cells(emptyRow, 17).Value = BxTextBox2.Value
Cells(emptyRow, 18).Value = InTextBox7.Value
Cells(emptyRow, 19).Value = BxTextBox3.Value
Cells(emptyRow, 20).Value = InTextBox10.Value
Cells(emptyRow, 21).Value = BxTextBox4.Value
Cells(emptyRow, 22).Value = InTextBox11.Value
Cells(emptyRow, 23).Value = BxTextBox6.Value
Cells(emptyRow, 24).Value = InTextBox12.Value
Cells(emptyRow, 25).Value = BxTextBox9.Value
Cells(emptyRow, 26).Value = InTextBox13.Value

If NoCheckBox.Value = True Then Cells(emptyRow, 9).Value = NoCheckBox.Caption

End Sub

Private Sub ClearButton_Click()

Call UserForm_Initialize

End Sub

Private Sub CancelButton_Click()

Unload Me

End Sub
 
Upvote 0
bebo021999 said:
Below code run a separate sub, run it manually whenever needed:
VBA Code: 
Option Explicit
Sub test()
Dim lr&, i&
lr = Cells(Rows.Count, "B").End(xlUp).Row
Range("B2:G" & lr).Sort key1:=Range("c2"), key2:=Range("b2"), key3:=Range("D2")
    For i = lr To 2 Step -1
        If i > 2 And Cells(i, 3).Value <> Cells(i - 1, 3).Value Then Cells(i, 3).EntireRow.Insert
    Next
End Sub
View attachment 62679View attachment 62680
Click to expand...
Thanks so much. Is there a way for it to be automatic?
 
Upvote 0
Add last 4 rows code below the OKButton_Click
VBA Code: 
Private Sub OKButton_Click()
Dim emptyRow As Long, i as long ' "add i as long"
'Make Sheet8 active
Sheet8.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1
'Transfer information
Cells(emptyRow, 2).Value = DateTextBox.Value
Cells(emptyRow, 3).Value = SettingTextBox.Value
Cells(emptyRow, 4).Value = DomainComboBox1.Value
Cells(emptyRow, 5).Value = GoalTextBox.Value
Cells(emptyRow, 6).Value = PromptComboBox2.Value
Cells(emptyRow, 7).Value = TrialsTextBox.Value
Cells(emptyRow, 8).Value = OpportunitiesTextBox.Value
Cells(emptyRow, 10).Value = ScheduleComboBox3.Value
Cells(emptyRow, 11).Value = TimeTextBox.Value
Cells(emptyRow, 12).Value = RatioTextBox.Value
Cells(emptyRow, 13).Value = LimitHoldTextBox.Value
Cells(emptyRow, 14).Value = NotesTextBox.Value
Cells(emptyRow, 15).Value = BxTextBox1.Value
Cells(emptyRow, 16).Value = InTextBox8.Value
Cells(emptyRow, 17).Value = BxTextBox2.Value
Cells(emptyRow, 18).Value = InTextBox7.Value
Cells(emptyRow, 19).Value = BxTextBox3.Value
Cells(emptyRow, 20).Value = InTextBox10.Value
Cells(emptyRow, 21).Value = BxTextBox4.Value
Cells(emptyRow, 22).Value = InTextBox11.Value
Cells(emptyRow, 23).Value = BxTextBox6.Value
Cells(emptyRow, 24).Value = InTextBox12.Value
Cells(emptyRow, 25).Value = BxTextBox9.Value
Cells(emptyRow, 26).Value = InTextBox13.Value
If NoCheckBox.Value = True Then Cells(emptyRow, 9).Value = NoCheckBox.Caption
'sort the table
Range("B2:G" & emptyRow).Sort key1:=Range("c2"), key2:=Range("b2"), key3:=Range("D2")
'Loop from bottom, then insert blank row. If there are too many rows may reduce code speed, ask me to try another code.
    For i = emptyRow To 2 Step -1
        If i > 2 And Cells(i, 3).Value <> Cells(i - 1, 3).Value Then Cells(i, 3).EntireRow.Insert
    Next
End Sub
 
Upvote 0
Solution
bebo021999 said:
Add last 4 rows code below the OKButton_Click
VBA Code: 
Private Sub OKButton_Click()
Dim emptyRow As Long, i as long ' "add i as long"
'Make Sheet8 active
Sheet8.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1
'Transfer information
Cells(emptyRow, 2).Value = DateTextBox.Value
Cells(emptyRow, 3).Value = SettingTextBox.Value
Cells(emptyRow, 4).Value = DomainComboBox1.Value
Cells(emptyRow, 5).Value = GoalTextBox.Value
Cells(emptyRow, 6).Value = PromptComboBox2.Value
Cells(emptyRow, 7).Value = TrialsTextBox.Value
Cells(emptyRow, 8).Value = OpportunitiesTextBox.Value
Cells(emptyRow, 10).Value = ScheduleComboBox3.Value
Cells(emptyRow, 11).Value = TimeTextBox.Value
Cells(emptyRow, 12).Value = RatioTextBox.Value
Cells(emptyRow, 13).Value = LimitHoldTextBox.Value
Cells(emptyRow, 14).Value = NotesTextBox.Value
Cells(emptyRow, 15).Value = BxTextBox1.Value
Cells(emptyRow, 16).Value = InTextBox8.Value
Cells(emptyRow, 17).Value = BxTextBox2.Value
Cells(emptyRow, 18).Value = InTextBox7.Value
Cells(emptyRow, 19).Value = BxTextBox3.Value
Cells(emptyRow, 20).Value = InTextBox10.Value
Cells(emptyRow, 21).Value = BxTextBox4.Value
Cells(emptyRow, 22).Value = InTextBox11.Value
Cells(emptyRow, 23).Value = BxTextBox6.Value
Cells(emptyRow, 24).Value = InTextBox12.Value
Cells(emptyRow, 25).Value = BxTextBox9.Value
Cells(emptyRow, 26).Value = InTextBox13.Value
If NoCheckBox.Value = True Then Cells(emptyRow, 9).Value = NoCheckBox.Caption
'sort the table
Range("B2:G" & emptyRow).Sort key1:=Range("c2"), key2:=Range("b2"), key3:=Range("D2")
'Loop from bottom, then insert blank row. If there are too many rows may reduce code speed, ask me to try another code.
    For i = emptyRow To 2 Step -1
        If i > 2 And Cells(i, 3).Value <> Cells(i - 1, 3).Value Then Cells(i, 3).EntireRow.Insert
    Next
End Sub
Click to expand...
Thanks Bebo. the code above is working for the most part, except it needs to include Column E in its sorting. That way it will list Goal 1-whatever, within that domain. Can that be incorporated?
 
Upvote 0
Msears said:
Thanks Bebo. the code above is working for the most part, except it needs to include Column E in its sorting. That way it will list Goal 1-whatever, within that domain. Can that be incorporated?
Click to expand...
Also when I change the setting (Col B) to a different setting like home or school, it skips a line from the original lines of data for clinic, but when I do that it will just overwrite the line of data that it just inputed instead of going down another line like it does with the original set? Looking back, I have updated my sheet since the screen shot, I chnaged Col B to setting, instead of Goal. I will adjust the code accordingly and see if it if will work like you intended.
 
Upvote 0
Thanks again for you hard work Bebo. I had to rearrange some of the code since I did not send you my updated sheet. You are awesome!
 
Upvote 0
Msears said:
Thanks Bebo. the code above is working for the most part, except it needs to include Column E in its sorting. That way it will list Goal 1-whatever, within that domain. Can that be incorporated?
Click to expand...
Hello Bebo. I think I have been starring at this too long! When I type in the first domain, in this case is "Echoic" and type in a second domain, in this case is "Imitation", it will enter it, but when I choose another domain, it will just override the second entry and will not let me proceed? Any ideas?
 

Attachments

  • Capture 2.PNG
    Capture 2.PNG
    10 KB · Views: 5
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
How to create an auto sort columns when new data is entered
Replies
0
Views
246
Msears
M
N
  • Question Question
Index Match after sorting
Replies
1
Views
653
Eric W
Eric W
S
  • Solved
Filter with auto refresh and column A filter interfering with B
Replies
4
Views
703
SunValley
S
K
  • Question Question
How to Connect Employees' Individual Excel Workbooks to a Main Workbook for Automatic Data Updates?
Replies
4
Views
861
OaklandJim
O
S
  • Solved
VBA to automatically copy rows to multiple cells
Replies
7
Views
967
kevin9999
K

Forum statistics

Threads
1,224,827
Messages
6,181,198
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top