+ Reply to Thread
Results 1 to 5 of 5

Add / remove column of table with user input

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

    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:
    Please Login or Register  to view this content.
    TO remove the column:
    Please Login or Register  to view this content.
    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.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Add / remove column of table with user input

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

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    155

    Re: Add / remove column of table with user input

    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

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Add / remove column of table with user input

    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.

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    155

    Re: Add / remove column of table with user input

    That's perfect! Thanks a lot AlphaFrog

+ Reply to Thread

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