Running Code On Cell Change in Excel

  


Here's a nice code that I found that runs a code whenever there's a
change in an Excel cell. 
I found it on the following: 


http://www.ozgrid.com/forum/showthread.php?t=68055 


  Private Sub Worksheet_Change(ByVal Target As Range)
      Dim iSect As Range
      Set iSect = Application.Intersect(Target, Range("Change"))
      If Not iSect Is Nothing Then
          Call Code
      End If
  End Sub 


Where "Code" is the macro that runs anytime there's a change in the
"Change" range. 

Linking to pivot table values

  


If you find yourself getting error messages when linking to pivot
table values, it may be because the formula is not seeing the cell as
a text, so you may get a "REF#" error.  In this case, you can replace
the cell - e.g. A1 with T(A1).  The function T() returns the text
referred to by the cell. 
So for example, if you get an error message with the formula 


=GETPIVOTDATA($A3,"PivotTable","MONTH",B$2,"YEAR",$A$1) 


where $A3 contains the variable, B$2 contains the month and $A$1
contains the year, 
You would replace it with 


=GETPIVOTDATA(T($A3),"PivotTable","MONTH",B$2,"YEAR",$A$1) 


which will put in the text for A3 so the formula can calculate.

Commenting out multiple lines in VBA

To comment out multiple lines in VBA, while in the VBA editor, go to
"View" then "Toolbars" and check "Edit". 
In the "Edit" toolbar, there's a button where you can comment a block
of data.

Getting the Last Sunday of the Week

To get the Sunday of the week that just passed, use the following in
VBA: 


    Public Function GetSunday(keyDate As Date) As Date
       GetSunday = DateSerial(Year(keyDate) _
           , Month(keyDate) _
           , Day(keyDate) - DatePart("w", keyDate) + 1)
   End Function 

Drilling down on pivot table cube items when multiple page filters are selected

  


Courtesy of Ralf_from_Europe 


Please see link to thread below.


http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/eab5da48-2335-471a-a597-817ed5e20c71/

Opening up MS Access from Excel with VBA

 

One of my clients had a project where I needed to open up an Access database from Excel with the click of a button.

Here's the code.  "database.mdb" is the name of the database you want to open.

Sub OpenDB()
Dim myDir As String 
myDir = ActiveWorkbook.Path
Shell "C:\Program Files\Microsoft Office\Office11\msaccess.exe " _
& myDir & "\database.mdb"
End Sub