Automated Functions

[Tills13]

Hey all.

I'm in my senior year for highschool and I've been tasked with keeping track of donations for a fundraiser for an organization called World Vision. I thought that the best way forward would be an Excel spreadsheet; well, that was what I thought last year. Recently, I came across this idea of VBA (during my summer last year, I created an Excel Purchase Order database for an oil company in my hometown using VBA). Over the course of the last few months, I've been improving on my spreadsheet from last year using VBA.

I like to think of myself as pretty proficient at it; however, I have a few questions.

First, is there anyway to have a function run whenever the sheet changes? Or perhaps just runs constantly in the background and executing if necessary?

I have a "colorizing" sub-function that I would like to have run at all times and make changes when necessary, but currently, I have it so that you have to interact with another function to have it run.

And second, how do you make it so that something executes whenever the sheet is first loaded? For example, populating a dropdown menu when the sheet loads.

Thanks!

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H5" '<<<< change to suit

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

'do what you gotta do
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[Bob Phillips]

Second one



Private Sub Workbook_BeforeClose(cancel As Boolean)
'do stuff
End Sub

Private Sub Workbook_Open()
'do stuff
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code