Excel 2003 - Keep a Macro Running While the Workbook is Open

[MaxRoberts22]

I have a macro I linked to a drop-down data validation list containing
dates from another an index sheet in the workbook. My macro reads the
day and month of the date selected on the drop-down list and displays
the proper columns. The marcro is set to run when the workbook is
opened, as there are other things in the marco that determine what
columns are hidden/shown upon it opening.

The problem is that I have to manually run the macro after I choose a
new date. Is there some way to run the marco continuously so that when
I choose a new date the sheet will automatically update?

Ben

 

[Ron de Bruin]

You can use the Change event for this

See this page for more information
http://www.cpearson.com/excel/events.htm

Example for cell B7

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B7"), Target) Is Nothing Then
Call yourmacro
End If
End Sub

 

[MaxRoberts22]

I have the following subroutines setup. I does not seem to execute my
macro when I execute the cell included in your macro. I should note
that my subroutine is contained in the "ThisWorkbook" module. Will that
make it a problem?


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B33"), Target) Is Nothing Then
Call Workbook_Open
End If
End Sub


Public Sub Workbook_Open()

Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim Index As Worksheet
Dim Month As Integer
Dim Day As Integer
Dim PayDay1 As Integer
Dim PayDay1A As Integer
Dim PayDay2 As Integer
Dim UserDay As Integer
Dim UserMonth As Integer
Dim MonthInt As Integer
Dim StartCol As Integer
Dim EndCol As Integer
Dim Refrow As Integer

Set Index = Worksheets("Index")
Set wks1 = Worksheets("Paychecks & Deductions - 2006")
Set wks2 = Worksheets("CCs & Bank Accts. - 2006")
Month = wks1.Range("C1").Value
Day = wks1.Range("D1").Value
UserDay = Index.Range("C1").Value
UserMonth = Index.Range("D1").Value

wks2.Range("K2Z2").Select
Selection.EntireColumn.Hidden = True

MonthInt = 1
Refrow = 1
PayDay1 = Index.Cells(Refrow, 1).Value
PayDay1A = PayDay1 + 1
PayDay2 = Index.Cells(Refrow + 1, 1).Value
StartCol = 11
EndCol = 15

Do Until MonthInt > 12

If UserDay <= PayDay1 And UserMonth = MonthInt Then
wks2.Range(Cells(2, StartCol), Cells(2, EndCol)).Select
Selection.EntireColumn.Hidden = False
Exit Do
Else
If UserDay > PayDay1A And UserDay <= PayDay2 And UserMonth
= MonthInt Then
wks2.Range(Cells(2, StartCol + 5), Cells(2, EndCol +
5)).Select
Selection.EntireColumn.Hidden = False
Exit Do
Else
MonthInt = MonthInt + 1
Refrow = Refrow + 2
End If
End If
Loop

ActiveWorkbook.Save

End Sub

 

[Ron de Bruin]

Copy your code in a macro in a normal module and in the
Workbook_Open event in the thisworkbook module also call the macro

Private Sub Workbook_Open()
Call yourmacro
End Sub

Note:

Private Sub Worksheet_Change(ByVal Target As Range)

you must copy this into the sheet module

 

[MaxRoberts22]

Does my code look like it will work. I have a set of paydates that can
change and they show up in a drop down list. My code should update with
the new date (actually and integer of the day or month of that date). I
could select the date from the drop down list and display the
corresponding columns (every five columns from K:O, P:T, U:Y, etc.).