vba commandbutton calling an worksheet update

[BobtBuilder]

I have a worksheet update that simply takes a line resorts it and then brings you to the line that was entered.
The problem is I have a command button that adds lines based on an action, so it calls the worksheet update but the code that takes me to the line should not be run if a commandbutton is used just the code that resorts.
So I was wondering if there was a way to identify that the code was activated from a commandbutton, tried a variable but it gets deinitialised when leaving the commandbutton.

Not sure if this is clear

Thanks

 

[Joe4]

So "Worksheet_Change" and "Worksheet_SelectionChange" are event procedure VBA codes that are run automatically when something happens ("Worksheet_Change" when a cell is manually updated and "Worksheet_SelectionChange" runs when a cell is selected.

If you want your command button code to run, and not invoke these events procedures to run, you can do that by temporarily disabling events from firing. You can do that by placing this line of code near the top of your command button code:

Application.EnableEvents = False

However, you need to turn it back on at the end of your code so that your automatic event procedure code will continue to work.
You do that by putting this line near the end of your command button code:

Application.EnableEvents = True

So it sounds like adding those two lines to your Command Button code should do what you want.

 

[BobtBuilder]

So "Worksheet_Change" and "Worksheet_SelectionChange" are event procedure VBA codes that are run automatically when something happens ("Worksheet_Change" when a cell is manually updated and "Worksheet_SelectionChange" runs when a cell is selected.

If you want your command button code to run, and not invoke these events procedures to run, you can do that by temporarily disabling events from firing. You can do that by placing this line of code near the top of your command button code:

Application.EnableEvents = False

However, you need to turn it back on at the end of your code so that your automatic event procedure code will continue to work.
You do that by putting this line near the end of your command button code:

Application.EnableEvents = True

So it sounds like adding those two lines to your Command Button code should do what you want.

Thank you, but it does have to run some of it, ie automatic resorting, it is about 1/3 that it does not need to run, and one line that gives me an error when it does. It is the origcell .

 

[Joe4]

Thank you, but it does have to run some of it, ie automatic resorting, it is about 1/3 that it does not need to run, and one line that gives me an error when it does. It is the origcell .

I would then recommend taking the sorting out of the current event Worksheet_Change procedure code and put it in its own procedure, like this:

Sub MySort()

Dim tbl As ListObject
Set tbl = Me.ListObjects(1) ' Assuming your table is the first ListObject on the sheet
Set rngIntersect = Intersect(Target, tbl.ListColumns("Date").DataBodyRange)

' Sort the table based on the "Date" column in ascending order
tbl.Sort.SortFields.Clear
tbl.Sort.SortFields.Add Key:=tbl.ListColumns("Date").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending
tbl.Sort.Apply

End Sub

Then, you can explicitly call this procedure from BOTH your Worksheet_Change event procedure, and your Comman Button procedure with a line of code like this:

Call MySort

 

[BobtBuilder]

I would then recommend taking the sorting out of the current event Worksheet_Change procedure code and put it in its own procedure, like this:

Sub MySort()

Dim tbl As ListObject
Set tbl = Me.ListObjects(1) ' Assuming your table is the first ListObject on the sheet
Set rngIntersect = Intersect(Target, tbl.ListColumns("Date").DataBodyRange)

' Sort the table based on the "Date" column in ascending order
tbl.Sort.SortFields.Clear
tbl.Sort.SortFields.Add Key:=tbl.ListColumns("Date").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending
tbl.Sort.Apply

End Sub

Then, you can explicitly call this procedure from BOTH your Worksheet_Change event procedure, and your Comman Button procedure with a line of code like this:

Call MySort

Thanks for that. I am not much of a coder and have put this together by reading and the help of others. I tried putting it where I thought it would work and I keep getting errors, where would you put the call mysort in command and update? Thanks

 

[Joe4]

Put the "MySort" procedure ina general module, so it is accessible from everywhere in the workbook.
Follow steps 1-4 here to create the a new general module for storing this "MySort" procedure in: Insert and run VBA macros in Excel - step-by-step guide.

 

[Joe4]

I just noticed one thing though - you are using the "Target" range in your sort. So we probably will need to pass the range as a parameter, i.e.

Sub MySort(myTarget as Range)

Dim tbl As ListObject
Set tbl = Me.ListObjects(1) ' Assuming your table is the first ListObject on the sheet
Set rngIntersect = Intersect(myTarget, tbl.ListColumns("Date").DataBodyRange)

' Sort the table based on the "Date" column in ascending order
tbl.Sort.SortFields.Clear
tbl.Sort.SortFields.Add Key:=tbl.ListColumns("Date").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending
tbl.Sort.Apply

End Sub

So whenever you call it, you will need to pass in the appropriate range value, i.e. from the "Worksheet_Change" procedure, it would be:

Call MySort(Target)

 

[BobtBuilder]

I just noticed one thing though - you are using the "Target" range in your sort. So we probably will need to pass the range as a parameter, i.e.

Sub MySort(myTarget as Range)

Dim tbl As ListObject
Set tbl = Me.ListObjects(1) ' Assuming your table is the first ListObject on the sheet
Set rngIntersect = Intersect(myTarget, tbl.ListColumns("Date").DataBodyRange)

' Sort the table based on the "Date" column in ascending order
tbl.Sort.SortFields.Clear
tbl.Sort.SortFields.Add Key:=tbl.ListColumns("Date").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending
tbl.Sort.Apply

End Sub

So whenever you call it, you will need to pass in the appropriate range value, i.e. from the "Worksheet_Change" procedure, it would be:

Call MySort(Target)

I simply used the origcell as a variable and if it existed it would run and if it did not it would not.
Thanks everyone