Excel how to auto run vba code?

[huiyin9218]

Hi,

May i know how enable vba code to run automatically when i open my workbook? and run the code again when changes is made in one of the cell?

I would be so grateful for your help.

 

[My Aswer Is This]

Add sheet change into what code?

Your wanting the script to run when the Workbook is opened.

Now what are you wanting?

A sheet change event code runs when something changes in a cell. Not when the workbook is opened.

So now I'm confused.

I showed you earlier how a sheet change event code works.
Maybe you should explain in detail what your wanting. I'm a little slow.

 

[My Aswer Is This]

Are you saying you want both of these scripts to run on workbook open.
And then anytime a change is made in these sheets?

I would need to know when what changes?

One of your script is doing something in numerous sheets.

 

[huiyin9218]

Are you saying you want both of these scripts to run on workbook open.
And then anytime a change is made in these sheets?

I would need to know when what changes?

One of your script is doing something in numerous sheets.

I'm so sorry for confusing you. Actually I want result to auto correct when the input is changed, because everytime the input is changed, i have to manually run the code again.

Sub correct()

   Dim ws As Worksheet
   
    For Each ws In ThisWorkbook.Worksheets
   
    Set ws = ThisWorkbook.Sheets("Sheet1")


    'Const Text2Find = "AM"
    Dim cel As Range
    For Each cel In ws.UsedRange.Columns("A").Cells
        If InStr(cel.Value, " AM") > 0 Then
            cel.Offset(0, 2) = 1
        ElseIf InStr(cel.Value, " PM") > 0 Then
            cel.Offset(0, 2) = 1
        ElseIf InStr(cel.Value, " 2018") > 0 Then
            cel.Offset(0, 2) = 0
        Else
            cel.Offset(0, 2).ClearContents
        End If
    Next cel
    Next ws


End Sub

​

Sub total()
    
For Each cell In Range("C:C")
Range("C1").Value = "CORRECT"
Range("D1").Value = "FINAL"


If cell = "" And cell.Offset(2, 0) = "" Then Exit For
If IsEmpty(cell.Value) Or cell.Value = "" Or cell.Row = Cells(Rows.Count, "C").End(xlUp).Row Then cell.Offset(0, 1).Value = "x"
Next cell
For Each cell In Range("D:D")
If cell.Row = Cells(Rows.Count, "C").End(xlUp).Row Then
cell.Value = ""
Exit For
End If
If cell.Value = "x" Then
Debug.Print cell.Address(0, 0)
nextx = Range("D:D").Find("x", Range(cell.Address(0, 0))).Offset(0, -1).Address(0, 0)
cell.Value = WorksheetFunction.Sum(Range(cell.Offset(0, -1).Address(0, 0) & ":" & nextx))
End If
Next cell


End Sub

The first code, the purpose is to detect the presence of word "AM", "PM" and "2018", and assign 1 at the cell beside. When it detects "2018", it will assign 0
The second code is used to calculate the number of 1

When there is changes in the number of AM, PM and 2018, it will affect the second code when calculating number of 1 assigned.
So i would like the number 1 assigned at each AM, PM and 2018 and the total number of 1s to autocorrect when there is changes in the number of AM, PM and 2018

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]January[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AM[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]AM[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In second column, the numbers in pink is generated by code 1
In third column, the numbers in red is generated by code 2

 

[My Aswer Is This]

Your original question was:
How do I get a script to run when my Workbook is opened.
And we showed you how to do that.
You did not tell us what the script was suppose to do.
And your other question was how do I get a sheet change event code to work.
And we showed you how to do that.
Then you asked how do I get two scripts to run on Workbook Open.
And we showed you how to do that.

Now your showing two different scripts and asking questions about these scripts.

I believe you should now start a new posting and tell us what your now trying to do.

 

[huiyin9218]

Your original question was:
How do I get a script to run when my Workbook is opened.
And we showed you how to do that.
You did not tell us what the script was suppose to do.
And your other question was how do I get a sheet change event code to work.
And we showed you how to do that.
Then you asked how do I get two scripts to run on Workbook Open.
And we showed you how to do that.

Now your showing two different scripts and asking questions about these scripts.

I believe you should now start a new posting and tell us what your now trying to do.

OMG, i'm so sorry for asking unrelated stuff. The initial problem is solved, thank you so much!!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

I will look at your new posting when you post it and see if I can help you with it.
Please explain in detail what your wanting to do.
A lot of times we may want to write you a whole new script to do what you want.

If I'm not able to help I'm sure someone else here will be able to help you.