How to sort columns automatically when any cell is updated?

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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
"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
"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"
???
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
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
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
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
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
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?
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
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?
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

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

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