Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - Keep a Macro Running While the Workbook is Open
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - Keep a Macro Running While the Workbook is Open
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 -- Regards Ron de Bruin http://www.rondebruin.nl "MaxRoberts22" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - Keep a Macro Running While the Workbook is Open
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("K2:DZ2").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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - Keep a Macro Running While the Workbook is Open
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 -- Regards Ron de Bruin http://www.rondebruin.nl "MaxRoberts22" wrote in message oups.com... 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("K2:DZ2").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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - Keep a Macro Running While the Workbook is Open
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.). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running a macro in a open as read-o Workbook | Excel Programming | |||
Running a macro in a open as read-o Workbook | Excel Programming | |||
Running a macro in a open as read-o Workbook | Excel Programming | |||
Application Error when running Macro on Workbook open | Excel Programming | |||
Application Error when running Macro on Workbook open | Excel Programming |