When you use Excel and Linked table in powerpivot you want that your user can’t edit column name or cells data, because their change will create problem to your model. But if you block your worksheet, how permit your user to delete or insert new row? With VBA
To insert Data we can use for example Vba Cells Function. in this case we create different dimension to contain value in specific cell and after that we populate the linked table with stored value, an example with explenation:
Sub InsertValueCells()
‘ declare the variable that we needDim nrighe As Integer
Dim cliente As String
Dim Funzione As String
Dim Scenario As String‘ Using cells fucntion in the first value go row number in the second column numberFunzione = Cells(6, 4)
cliente = Cells(7, 4)
Scenario = “CE Business Review Q3”
Versione = Cells(9, 4)
netnetq4 = Cells(23, 23)
Listingq4 = Cells(26, 23)
Instore4 = Cells(27, 23)‘ Go to the sheet where is our linked table
Sheets(“Tabella CE Business Review”).Select‘UNprotect the worksheetActiveSheet.Unprotect Password:=”password”‘Select the linked tableDim tbl As ListObject
Set tbl = ActiveSheet.ListObjects(“NetNet_Previsionale”)‘ find the last row inserted, the +1 is the distance from le rows a (+1 because i count the column label)
nrighe = tbl.Range.Rows.Count + 1If Cells(2, 2) = “” Then nrighe = 2‘ write in the last row in selected column
Cells(nrighe, 2) = cliente
Cells(nrighe, 3) = Funzione
Cells(nrighe, 4) = Scenario
Cells(nrighe, 5) = VersioneCells(nrighe, 9) = netnetq4
Cells(nrighe, 12) = Listingq4
Cells(nrighe, 15) = Instore4‘ block worksheetActiveSheet.Protect Password:=”Porini_123″, DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True‘ return to first one worksheetSheets(“CE Business review Q3”).Select‘ Refresh linked table
ActiveWorkbook.Connections(“LinkedTable_ NetNet_Previsionale”).Refresh
MsgBox (“Combinazione Salvata”)End Sub
This example work if your data aren’t in a table or are in different cells not placed side by side.
To delete rows from linked table you can use this example of Vba sub:
Sub deleterow()
‘ dim to select range
Dim ActSheet As Worksheet
Dim SelRange As Range
Dim iCntr
‘ Unlock your workshhet
ActiveSheet.Unprotect Password:=”PASSWORD”
‘ save the cells ( single o group fo cells) that you have selected before start vbaSet ActSheet = ActiveSheet
Set SelRange = Selection‘ delete complete each row of selected range
For iCntr = SelRange.row + SelRange.Rows.Count – 1 To SelRange.row Step -1
Rows(iCntr).EntireRow.Delete
Next
‘ block worksheet
ActiveSheet.Protect Password:=”PASSWORD”, DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=TruEnd Sub
To use this Vba macro you have to create a simple button on the same page. The user select one or more cells of row that he wants to delete and click button ( the button have to be connected to the macro)