Insert Delete Value Likend table VBA

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 need
Dim 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 number
Funzione = 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 worksheet
ActiveSheet.Unprotect Password:=”password”
‘Select the linked table
Dim 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 + 1
If 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) = Versione
Cells(nrighe, 9) = netnetq4
Cells(nrighe, 12) = Listingq4
Cells(nrighe, 15) = Instore4
‘ block worksheet
ActiveSheet.Protect Password:=”Porini_123″, DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
‘ return to first one worksheet
Sheets(“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 vba

Set 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:=Tru
End 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)

 

Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...