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.

 

[JoeMo]

You would use a Workbook_Open event code module placed in Thisworkbook to run something automatically when you open the workbook.

To run code automatically when a change is made to a cell or cells you would use a worksheet_change event code module.

 

[My Aswer Is This]

To answer your first question:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu
Double click on ThisWorkbook
Paste the code in the VBA edit window

Save your document and next time you open the Workbook you will get a message saying Hello
Modify this later to exactly what you want to happen when you open the workbook.

Private Sub Workbook_Open()
'Modified  8/9/2018  9:54:59 PM  EDT
MsgBox "Hello"
End Sub

 

[My Aswer Is This]

Here is a example of a sheet change event script:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on your sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window


When you enter any value in column A of your sheet Todays date will be entered in column B in the same row

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/9/2018  10:04:23 PM  EDT
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(, 1).Value = Date
End If
End Sub

 

[huiyin9218]

You would use a Workbook_Open event code module placed in Thisworkbook to run something automatically when you open the workbook.

To run code automatically when a change is made to a cell or cells you would use a worksheet_change event code module.

How do I insert more than one code in ThisWorkbook?

 

[huiyin9218]

To answer your first question:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu
Double click on ThisWorkbook
Paste the code in the VBA edit window

Save your document and next time you open the Workbook you will get a message saying Hello
Modify this later to exactly what you want to happen when you open the workbook.

Private Sub Workbook_Open()
'Modified  8/9/2018  9:54:59 PM  EDT
MsgBox "Hello"
End Sub

Is it possible to create more Thisworkbook? I have two different codes that i wish to auto generate when i open excel

 

[My Aswer Is This]

Show me the two codes you want to run when Workbook opens

 

[huiyin9218]

Show me the two codes you want to run when Workbook opens

Sub correctvalue()
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 finalvalue()


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

 

[My Aswer Is This]

Try this:

Private Sub Workbook_Open()
'Modified  8/9/2018  11:11:59 PM  EDT
Call correctvalue
Call finalvalue
End Sub

 

[huiyin9218]

Try this:

Private Sub Workbook_Open()
'Modified  8/9/2018  11:11:59 PM  EDT
Call correctvalue
Call finalvalue
End Sub

It works! Thank you!!
Is it possible to add sheet change into the code?