attribute resequence for Master data sheet in IBP excel addin #6
ayanbishnu
started this conversation in
Show and tell
Replies: 0 comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Hello
I have developed a VBA code to resequence attributes for Master data sheet based on client requirement. Hence thought of sharing.
`Function IBPMDAfterRefresh(name As String, editable As Boolean, rows As Long, headers() As String)
Dim idxFrom As Integer
Dim idxTo As Integer
Dim idxWhat As Integer
Dim strTemp As String
Dim add As Integer
Dim intHdrRow As Integer
Dim strFirstCell As String
Dim lngReadOnlyColor As Long
Dim lngEditColor As Long
Dim lngKeyColor As Long
Dim msg As String
Dim strActiveSheet
Dim strLookupA1Value
Dim strLookupA2Value
Application.ScreenUpdating = False
'DETERMINE PLANNING AREA & ENVIRONMENT
Dim goEpmAuto As Object
Dim connection As String
Dim strEnvironment As String
Dim strPlanningArea As String
Set goEpmAuto = Application.COMAddIns("FPMXLClient.Connect").Object
connection = goEpmAuto.GetActiveConnection(ActiveSheet)
If InStr(connection, "KCNA1") > 0 Then
strPlanningArea = "KCNA1"
ElseIf InStr(connection, "KCN3") > 0 Then
strPlanningArea = "KCN3"
ElseIf InStr(connection, "KCZIT1POC") > 0 Then
strPlanningArea = "KCZIT1POC"
ElseIf InStr(connection, "KCAP1") > 0 Then
strPlanningArea = "KCAP1"
Else
strPlanningArea = "OTHER"
End If
If InStr(connection, "my400728") > 0 Then
strEnvironment = "DEV"
ElseIf InStr(connection, "my401112") > 0 Then
strEnvironment = "QUAL"
ElseIf InStr(connection, "my400767") > 0 Then
strEnvironment = "PROD"
Else
strEnvironment = "OTHER"
End If
'Defines the nubmer of header rows and columns that are meant to be "Blank" in non-edit or keys in edit
intRowHeaders = 1
intColHeaders = 1
'In ReadOnly mode insert row on top and column on left and then Hide those rows so the setup is similar
'If you're in Edit or read only mode. This simpliefies the rest of the logic in this
If Not editable Then
End If
'Leveraged for Vlookups that are Y/N
Dim arrYesNoInd(3) As String
arrYesNoInd(0) = ""
arrYesNoInd(1) = "Y"
arrYesNoInd(2) = "N"
'***********************************************************************************************************
'***********************************************************************************************************
'KCZIT1POC LOGIC
'*******************************************************************************************************
'***********************************************************************************************************
If strPlanningArea = "KCZIT1POC" Or strPlanningArea = "KCAP1" Then
End Function
'***********************************************************************************************************
'***********************************************************************************************************
'THE SECTION BELOW HERE IS FOR COMMON USED ROUTINES BETWEEN EACH TAB
'YOU SHOULD TAB LOGIC AND CALLS ABOVE THIS SECTION
'***********************************************************************************
'***********************************************************************************************************
Function SAP_IBP_HeaderRename(strInputKey, strInputText, intRowHeaders, intColHeaders, editable)
'FIND THE LOOKUP VALUE ON KEY
ActiveSheet.rows(1).Select
Selection.Find(What:=strInputKey, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Application.ScreenUpdating = False
idxLookupValueCol = ActiveCell.Column
strLookupValueCol = Cells(1, ActiveCell.Column).Address(RowAbsolute:=False, ColumnAbsolute:=False)
End Function
Function SAP_IBP_GenericFormula(name, strFormula, intLookupValueCol, strStyle, strFormat, editable, rows, intRowHeaders)
End Function
Function SAP_IBP_SUMIFUOM(name, strLookUpSheet, strMaterialtoSUM, strUOMtoSUM, intLookupValueCol, editable, rows, intRowHeaders)
End Function
Function SAP_IBP_Vlookup(name, strKeyColumn, strLookUpSheet, rngLookupTable, intLookupColumn, intLookupValueCol, editable, rows, intRowHeaders)
Dim strLookupValueCol
Dim work As Range
End Function
'PARAMETERS ARE: Number of Header Rows, Number of Column Headers, Number of Columns to keep
Function SAP_IBP_Split_Freeze(intRowHeaders, intColHeaders, intColumnsToKeep)
End Function
'PARAMETERS ARE: Array Value, Attribute to be udpadted, # Header Rows, # Rows in data, # of Extra Rows
Function SAP_IBP_Validation_List(arrPossibleValues, strAttribute, intRowHeaders, rows, extraRows)
End Function
Function SAP_IBP_Validation_Range(intLowValue, intHighValue, strKEY, strText, intRowHeaders, rows, extraRows)
End Function
Function SAP_IBP_Validation_EverydayBonus(strTradeItemCol, strEverydayBonus, strKEY, intRowHeaders, rows, extraRows)
Columns(idxWhat).Select
'This section puts in logic to ensure you validate the Everyday Bonus Values correct.
End Function
Function SAP_IBP_Validation_GreaterThan(intGTValue, strKEY, strText, intRowHeaders, rows, extraRows)
End Function
Function SAP_IBP_Seq_Color(arrInputSeq, intRowHeaders, intColHeaders, editable)
End Function
Function SAP_IBP_StratTac_Conditional(strMonthFirst, strMonthLast, strTransHorizon, intRowHeaders, intColHeaders, rows, extraRows, editable)
End Function
Function SAP_IBP_SingleValue_Conditional(strStartField, strEndField, strString, strFillColor, intRowHeaders, intColHeaders, rows, extraRows, editable)
End Function
Function SAP_IBP_ResetFilter_Criteria(strStartField, strEndField, intRowHeaders, editable)
End Function
Function SAP_IBP_FormatCell(strStartField, strEndField, indHorAlign, indVerAlign, indWrap, indBold, indItalic, strTextColor, intRowHeaders, intColHeaders, rows, extraRows)
'Start Field to format, Last field to format, Horizontal Alignment, Vertical Alignment,(Y/N) Wrap, Bold (Y/N), Italic (Y/N), Text Color, header Rows, Col Headers, Rows in File, Extra Rows to format
Dim idxFirst As Integer
Dim idxLast As Integer
Dim work As Range
End Function
'*************************************************************************************
'EXAMPLE CODE FROM SAP FOR EDITING BEFORE AN UPDATESAVE IF NEEDED
'Function IBPMDBeforeUpdate(name As String, headers() As String, changedItems() As String) As Boolean
' Dim answer As Integer
' Dim changeCount As Integer
' Dim productId As String
' Dim i As Integer
'IBPMDBeforeUpdate = True
'changeCount = UBound(changedItems, 1)
'
'If name = "Product" Then
' For i = 0 To changeCount
' productId = changedItems(i, 0)
' If productId Like "IBP" Then
' IBPMDBeforeUpdate = False
' MsgBox ("Product Id should not contains IBP")
' Exit Function
' End If
' Next i
'End If
'If name = "Location" Then
' changeCount = UBound(changedItems, 1) + 1
' answer = MsgBox("Master Data:" & name & vbNewLine & changeCount & " rows has been modified." & vbNewLine & "Is custom validation OK?", vbYesNo + vbQuestion, "Custom Validation via IBPMDBeforeUpdate")
'
' If answer = vbYes Then
' IBPMDBeforeUpdate = True
' Else
' IBPMDBeforeUpdate = False
' End If
'End If
'End Function
`
Beta Was this translation helpful? Give feedback.
All reactions