inpubox userform to add and delete data in range

[KARKHI78]

hello.
i have a sheet in which i record some projects, each one with a different color tag. i.e. the range C3:C21 is where the projects names are stored. I would need a macro that after pressing a button, show the items in the cells range like a list and allows me to add or delete dynamically text in that same cells. it should find the first empty cell in the column and fill it if i add an item or delete the selected item in the menu that opens after pressing the button. is it possible with vba?

thanks.

 

[mumps]

Do you want to add new items to the bottom of the column? Do you want the option to delete or modify an existing item in the list?

 

[KARKHI78]

Do you want to add new items to the bottom of the column? Do you want the option to delete or modify an existing item in the list?

hello. I would like to insert the name in the first empty cell starting from the top. I don't care the color tag of the project it can be random. but I need to delete the list names with a combo box that opens with the same button used to insert the item.

 

[mumps]

You will need a userform that contains a combobox with the list names and you will need a text box to enter any new names to be added. Do you want to insert the name in the first empty cell even if is not at the bottom of the list? For example, if you delete the second value in a list of 10 names, do you want the next new item to be inserted in that cell? Also, if the color tag is random, you risk that the color will be too dark or the same as the font color which will make the text in that cell invisible. It would be better and safer if the cell colors in C3:C21 were already there.

 

[KARKHI78]

You will need a userform that contains a combobox with the list names and you will need a text box to enter any new names to be added. Do you want to insert the name in the first empty cell even if is not at the bottom of the list? For example, if you delete the second value in a list of 10 names, do you want the next new item to be inserted in that cell? Also, if the color tag is random, you risk that the color will be too dark or the same as the font color which will make the text in that cell invisible. It would be better and safer if the cell colors in C3:C21 were already there.

yes. i don't care the position of the names. it can be the first empty cell in any position. if I delete the project 'x' in the 2th cell it's not a problem if I add a new one in this same cell. but if the 3rd,4th,5th are filled, the next one must go in the sixth and so on. for the colours, I already filled the cells as you can see in the image. the issue for me is to add/delete names dynamically with combo box and inputbox

 

[mumps]

Create a userform. In the userform, insert a combobox, a textbox and a command button. Double click the userform and paste this code in the window that pops up. Change the sheet name (in red) to suit your needs.

Private Sub CommandButton1_Click()
    Dim desWS As Worksheet, fnd As Range, LastRow As Long, x As Long
    Set desWS = Sheets("Sheet1")
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set fnd = desWS.Range("C3:C" & LastRow).Find(ComboBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        fnd.ClearContents
    End If
    x = desWS.Range("C3:C" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
    If x < LastRow Then
        Range("C" & x) = TextBox1.Value
    Else
        desWS.Cells(desWS.Rows.Count, "C").End(xlUp).Offset(1) = TextBox1.Value
    End If
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.List = Sheets("Sheet1").Range("C3", Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp)).Value
End Sub

Place this macro in a regular module.

Sub ShowUserform()
    UserForm1.Show
End Sub

Next insert a shape on your sheet and assign the macro to the shape. When you click the shape, the userform will display. Select a value to delete from the combobox. Enter a value to add in the textbox. Click the command button.

 

[KARKHI78]

thank you very much mumps. it works perfectly. i just wanted to ask u: the way the macro is written, it goes down in the column indefinitely, without a bottom limit? that is, i can add as many cells i want in the combobox list items?

 

[mumps]

You are very welcome.

i can add as many cells i want in the combobox list items?

That is correct.

 

[KARKHI78]

ok. thanks a lot again.

 

[mumps]

My pleasure.