Modifying code

shimaa01234

Active Member
Joined
Jun 24, 2014
Messages
446
This code for Mister "MickG"
I want it works Automatic without "commandbutton"

Private Sub CommandButton1_Click()
Dim Ans As String
Dim oSort As String
Ans = MsgBox("""No"" = Male sort" & Chr(10) & """Yes"" = Female sort", vbYesNo + vbInformation)
oSort = IIf(Ans = vbYes, xlAscending, xlDescending)
Range("A2:D16").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1:D16").Select
Selection.Sort Key1:=Range("D2"), Order1:=oSort, Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
What is going to be the trigger for the macro to work "automatic"
 
Upvote 0
Unfortunately Mister "Michael"
I need this code is necessary, and I want to carry out spontaneously "in the work sheet"
Without "command button"
 
Last edited:
Upvote 0
As Michael already mentioned, we would need to know what the trigger would be. Otherwise we will be grasping at straws. If you want it to run upon the opening of a workbook, or upon the update of a cell or of a range or what? What precisely would make it seem automatic in your case.
 
Upvote 0
This this would be called event enabled code.

Replace
Code:
Private Sub CommandButton1_Click()
with
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
A couple lines of code might be necessary if you are making changes to cells on the sheet that contains this code to keep the event from firing over and over again when you make only one 1 change to the sheet. This needs to go at the beginning of the code
Code:
Application.EnableEvents = False
and this needs to be placed just before the end statement of the code
Code:
Application.EnableEvents = True

note that if you this code errors with the code to disable events in place then you will need to re-enable events before events will fire again

Hope this helps.
 
Upvote 0
Which cell is going to change ??
 
Upvote 0
This a fairly straightforward process, but you need to explain to us what cell /s and what action is going to trigger the code !!
Saying "I want to delete a message", tells us nothing about how the code will be fired !
While I appreciate your coding knowledge is limited, we can't help you until you provide adequate information.

We know what the code does, we simply need to know what you will be doing "IN DETAIL" to make the code fire !!
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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