Add / remove column of table with user input

[MagnusNovak]

Hi everyone,

I have a table in a locked sheet with a certain number of columns by default. Given that the sheet is locked, the user can't add or remove columns so I'm looking for a way to do something like that in VBA:
When the user clicks on the "Add a column" button, a pop-up window appears, asking for the column name. Once entered by the user, a new column is created (with the name entered by the user as its header) and added to the table (on the right side).
Inversely, a user can click on the "Remove a column" button, which requires to provide the name of the column to be removed. Once entered (and if the column does exist), then the column is deleted.

One particular thing is that the remove button can only remove the columns that were added by the user previously (not the default columns that I created initially).

Here's what I have for now but I don't know if I'm making any mistakes here or if there is a cleaner way to write it:

Add a column:

Sub AddAColumn()
Dim myValue As Variant
Dim Table As ListObject
Set Table = Sheet1.ListObjects("Table1")
showInputBox:
myValue = Application.InputBox("Please enter your column name (make sure this name does not already exist)", "Add a Column")
If myValue = False Then
Exit Sub
ElseIf myValue = "" Then
MsgBox "You must enter a valid name to add a column.", 48, "You clicked Ok but entered nothing."
GoTo showInputBox
Else
Table.ListColumns.Add (4)
Table.HeaderRowRange(4) = myValue
End If
End Sub

TO remove the column:

Sub RemoveAColumn()
Dim myValue As Variant
Dim Table As ListObject
Set Table = Sheet1.ListObjects("Table1")
showInputBox:
myValue = Application.InputBox("Please enter the name of the column you wish to remove", "Remove a Column")
If myValue = False Then
Exit Sub
ElseIf myValue = "" Then
MsgBox "You must enter a valid name to remove a column.", 48, "You clicked Ok but entered nothing."
GoTo showInputBox
Else
    Table.ListColumns(myValue).Delete
End If
End Sub

This code does not cover the situation where the user tries to delete one of the default columns (that I dont want him to remove) or if he tries to delete a column that does not exist.


I feel like only VBA can help with that routine but let me know if you think about an alternative!

Thanks a lot

[AlphaFrog]

This wont delete the 3 original columns to the left of the table.

Sub RemoveAColumn()
Dim myValue As Variant
Dim Table As ListObject
Dim LC As ListColumn
Set Table = Sheet1.ListObjects(1)
showInputBox:
myValue = Application.InputBox("Please enter the name of the column you wish to remove", "Remove a Column")
If myValue = False Then
Exit Sub
ElseIf myValue = "" Then
MsgBox "You must enter a valid name to remove a column.", 48, "You clicked Ok but entered nothing."
GoTo showInputBox
Else
    On Error Resume Next
    Set LC = Table.ListColumns(myValue)
    On Error GoTo 0
    If LC Is Nothing Then
        MsgBox "Header '" & myValue & "' does not exist.", vbExclamation, "Invalid Entry"
    ElseIf LC.Index <= 3 Then
        MsgBox "Cannot delete the three original columns.", vbExclamation, "Invalid Entry"
    Else
        LC.Delete
    End If
End If
End Sub

[MagnusNovak]

That's great, thank you very much.

One related question. Would there be a way to modify this code to prevent deleting the default columns even if new columns are inserted in-between.
Example:
I have the following default columns:
A - B - C - D - T
T is a total column that will always be the last column of the table (i.e. the add button inserts custom columns between D & T)

I add the button to add custom columns named 1 & 2. The table then becomes:
A - B - C - D - 1 - 2 - T

In this situation, the remove button must allow for deletion of 1 & 2 without being able to delete A, B, C, D and more importantly T.

I don't know if there is an easy way to do so ( I can live without it but that would answer my issue perfectly)

Thanks again for the help, much appreciated

[AlphaFrog]

This checks the header before deleting a column. If it's one of the default columns, it wont delete it. Change A,B,C,D,T in the code to the headers you want protected.

Sub RemoveAColumn()
    Dim myValue   As Variant
    Dim Table     As ListObject
    Dim LC        As ListColumn
    Set Table = Sheet1.ListObjects(1)
showInputBox:
    myValue = Application.InputBox("Please enter the name of the column you wish to remove", "Remove a Column")
    If myValue = False Then
        Exit Sub
    ElseIf myValue = "" Then
        MsgBox "You must enter a valid name to remove a column.", 48, "You clicked Ok but entered nothing."
        GoTo showInputBox
    ElseIf InStr(1, "A,B,C,D,T", myValue, 1) > 0 Then
        MsgBox "Cannot delete '" & myValue & "' column.", 48, "Protected Column"
    Else
        On Error Resume Next
        Set LC = Table.ListColumns(myValue)
        On Error GoTo 0
        If LC Is Nothing Then
            MsgBox "Header '" & myValue & "' does not exist.", vbExclamation, "Invalid Entry"
        Else
            LC.Delete
        End If
    End If
End Sub

[MagnusNovak]

That's perfect! Thanks a lot AlphaFrog