Results 1 to 5 of 5

Add / remove column of table with user input

Threaded View

  1. #1
    Forum Contributor
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    MS 365
    Posts
    167

    Add / remove column of table with user input

    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
    Last edited by MagnusNovak; 02-09-2021 at 07:13 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Do not allow user to input different value than in row above in table
    By jaryszek in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-04-2020, 06:04 AM
  2. User input to create a table, then user input to populate that table
    By cjsearles in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-14-2019, 11:43 AM
  3. A Dynamic User-Input Table?
    By whahmira in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-17-2019, 05:10 AM
  4. [SOLVED] Adjust VBA macro for a fixed location and remove user input
    By spyac in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-21-2017, 09:11 PM
  5. [SOLVED] Formula to remove spacebars at the end of user input text
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-01-2014, 06:39 PM
  6. Using text box input to lookup number and replace based on user input into new column
    By harl3y412 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-28-2011, 03:15 PM
  7. User Input to Split the Table
    By rakesh1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-19-2010, 08:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1