How to Run Macro when Refresh Report?

[Former Member]

Hi,

We are using below Macro code to Hide and Unhide Rows in BPC report if a column value is 0 using a Button.
We want this code to run every time when we refresh the report instead of a Button.

Sub HideRows()
   
    Dim cell As Range
    For Each cell In Range("B1:B33")
        If Not IsEmpty(cell) Then
            If cell.Value = 0 Then
                cell.EntireRow.Hidden = Flase
            End If
        End If
    Next
   
    Dim cell1 As Range
    For Each cell1 In Range("B1:B33")
        If Not IsEmpty(cell1) Then
            If cell1.Value = 0 Then
                cell1.EntireRow.Hidden = True
            End If
        End If
    Next
End Sub

I tried using Function After_Refresh but didn't work.

Please suggest how to do it.

Thanks,

Anitha

[margaridamalheiro]

Hi

Put your code in Macro1

and put "Macro1"  in end of Sub CallBack

[former_member186338]

And what is the meaning of "Sub CallBack"?

[margaridamalheiro]

When you create a report, the system runs behind several procedures, when you have an open report go to the macro, choose Macro view and edit the callback

as I am Portuguese menu is in Portuguese

[former_member186338]

Unfortunately "CallBack" is a custom macro written in your specific workbook

[margaridamalheiro]

Just run a query and save as report to appear this callback in every report I did for years

[former_member186338]

Nothing in my environment

Can you show VBA code of this "CallBack"?

[former_member186338]

How it has to look like:

[gajendra_moond]

Are you referring to callback in BEx or Analysis reports?

[margaridamalheiro]

I use Microsoft excel

I ran a query (without recording as a report) and did not do anything, follow the printscreens 1-4 before seeing the following code

'----------------------------------------------------------------------------------------
'--- Call-Back Macro: Executed on Refresh for each Table Item
'----------------------------------------------------------------------------------------
Sub CallBack(ParamArray varname())
  'varname(0): BExExcelItem.DataProvider.Name
  'varname(1): BExExcelItem.Range
  'varname(2): BExExcelItem.Name
 
  'Remember current EnableEvents setting and set to False (disable)!
  '  we need to apply the default formats to the items - nothing else
  Dim lEnableEvents As Boolean
  lEnableEvents = Application.EnableEvents
  Application.EnableEvents = False

  Dim lTitle As String
  Dim lName As Name
  Dim lRange As Range
  Dim lColumn As Integer

  Dim lItem As Integer
  Const cItemGrid As Integer = 1
  Const cItemNavPane As Integer = 2
 
  On Error Resume Next
 
  If InStr(1, varname(2), "GRID") Then
    lItem = cItemGrid
  ElseIf InStr(1, varname(2), "NAVPANEL") Then
    lItem = cItemNavPane
  End If

  'Get Workbook Name Object
  '  If Name exists then:
  '    - retrieve old Range of Item
  '    - clear contents and formats of old Range Item
  '    - set new Range of Item
  lTitle = "DF_" & varname(2)
  Set lName = getName(lTitle)
  If Not lName Is Nothing Then
    'Get Items old Range
    Set lRange = lName.RefersToRange
    Set lRange = lRange.Offset(-1, 0).Rows(1)
    'Clear Contents and Formats of Items old Range
    If Not lRange Is Nothing Then
      If lRange.Cells(1, 1).Value = "Table" Then
        lRange.Cells(1, 1).ClearContents
        lRange.ClearFormats
      End If
    End If
    'Save new position of Name Object
    lName.Delete
    ThisWorkbook.Names.Add lTitle, "=" & varname(1).Worksheet.Name & "!" & varname(1).Address
  End If
 
  If lItem = cItemGrid Then
    'Set Table Header Style
    With varname(1).Offset(-1, 0).Rows(1)
      .Font.Size = 10
      .Font.FontStyle = "Bold"
      .Interior.ColorIndex = 51
      .Interior.Pattern = xlSolid
      .Interior.PatternColorIndex = xlAutomatic
      .Cells(1, 1) = "Table"
    End With
  End If
 
  'Currently this callback routine is only called for Grid Items
  'For this reason we have to always render the NavigationPane Item
  If lItem = cItemNavPane Then
    'Set NavigationPane Header Style
    With varname(1).Offset(-1, 0).Rows(1)
      .Cells(1, 1) = "Filter"
      .Font.Size = 10
      .Font.FontStyle = "Bold"
      .Interior.ColorIndex = 51
      .Interior.Pattern = xlSolid
      .Interior.PatternColorIndex = xlAutomatic
    End With
    'Set NavigationPane Dimension Interior Color (white)
    With varname(1).Offset().Columns(1)
      .Interior.ColorIndex = 2
      .Interior.Pattern = xlSolid
      .Interior.PatternColorIndex = xlAutomatic
    End With

    'Default NavigationPane
    '  is always located in Range: Cells(15, 3) + Title(14, 3)
    '  if an item is possibly overlapping the Default NavigationPane then reapply Default NavigationPane
    Dim lX1 As Integer
    Dim lX2 As Integer
    lX1 = varname(1).Offset().Column
    lX2 = varname(1).Offset().Column + varname(1).Offset().Columns.Count - 1
    If (varname(1).Offset().Cells(1, 1).Column = 3 And varname(1).Offset().Cells(1, 1).Row = 15) Or _
       (lX1 >= 3 And lX1 <= 5 Or lX2 >= 3 And lX2 <= 5) Then
      Call setFilterStyle
      Call adjustCells
    End If
  Else
    Call setFilterStyle
    Call adjustCells
  End If

  'Adapt Title in Title Box
  Sheet2.Shapes("TextQueryTitle").DrawingObject.Text = Sheet2.Range("F1").Value
  Sheet2.Shapes("TextQueryTitle").DrawingObject.Font.Size = 14
  Sheet2.Shapes("TextQueryTitle").DrawingObject.Font.Bold = True
  Sheet3.Shapes("TextQueryTitle").DrawingObject.Text = Sheet2.Range("F1").Value
  Sheet3.Shapes("TextQueryTitle").DrawingObject.Font.Size = 14
  Sheet3.Shapes("TextQueryTitle").DrawingObject.Font.Bold = True

  If lItem = cItemGrid Then
    'Set Print Area in Table Worksheet (min Columns A - K)
    If (varname(1).Columns.Count + varname(1).Column > 13) Then
      lColumn = varname(1).Columns.Count + varname(1).Column
    Else
      lColumn = 13
    End If
    varname(1).Worksheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(varname(1).Rows.Count + varname(1).Row, lColumn)).Address
    varname(1).Worksheet.DisplayAutomaticPageBreaks = False
  End If
 
  'Add Accessibility Features
  If Accessibility.paccessibility Then
    Accessibility.reset_menu
    Accessibility.show_menu
  End If
 
  'Reset EnableEvents
  Application.EnableEvents = lEnableEvents

End Sub

[former_member186338]

Sorry, but here we are talking about EPM reports for BPC, and not about BEx!!

[margaridamalheiro]

yes but the screen of vadim does not seem the analyzer

[margaridamalheiro]

ok

[gajendra_moond]

I am afraid you are not in the right forum Margarida. This is related to EPM reports where APIs are different. But with EPM becoming part of Analysis for office, we might be able to leverage Callback but at least not for this post.

[margaridamalheiro]

I know....sorry

[Shrikant_Jadhav]

Hi Anitha,

Place your code in AFTER_REFRESH function . It will execute after every refresh of template.

Shrikant

[Former Member]

Hi Srikanth,

I have tried as below with After_Refresh() function, when Executed from VBScript screen it executed correctly.

But when using from Excel i.e.; Refresh from EPM Tab it did not work.

Function AFTER_REFRESH()

Dim cell As Range
    For Each cell In Range("B1:B33")
        If Not IsEmpty(cell) Then
            If cell.Value = 0 Then
                cell.EntireRow.Hidden = Flase
            End If
        End If
    Next
   
    Dim cell1 As Range
    For Each cell1 In Range("B1:B33")
        If Not IsEmpty(cell1) Then
            If cell1.Value = 0 Then
                cell1.EntireRow.Hidden = True
            End If
        End If
    Next

AFTER_REFRESH = True

End Function

Suggest any changes need to be done, so that it will work from Refresh Report.

[gajendra_moond]

I would go for that too but the original post indicated that it is not working.

Anitha

Your code must look like this-

Public Function AFTER_REFRESH() As Boolean

HideRows

End Function

[Shrikant_Jadhav]

Hi,

Remove AFTER_REFRESH = True

Shrikant

[gajendra_moond]

Hi Anitha

One option could be to create a macro for refreshing the report and call your hide column macro in there after refresh is completed.

[Former Member]

But to Trigger the Refresh Macro, again we need to use a Button.

We want to make use of EPM Refresh Report option and run the Macro code.

[gajendra_moond]

You can disable the refresh button on the EPM tab and have your button on the sheet to refresh. You have to click at least once in either case.