Delete/add/update list in spreadsheet via User form.

Gian86

New Member
Joined
Mar 25, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi all

I newly started to lerne VBA and have some problems to write the code for this Problem. Especially adding and deleting lineitems (names and phone numbers) in the list and updating the phone numbers.
Any help is very much appreciated.

Thank you very much.
Best regards


Problem Statement
Create a user form that allows the user to manage (add, edit, and delete) the names and phone numbers of contacts.

5 separate subroutines are required:

RunForm – the on-sheet button is linked to this sub, which simply opens up the NameForm user form.

PopulateComboBox – Prior to opening NameForm, this sub should populate ComboBox1 on NameForm with the names in column A of the spreadsheet.

AddName – This sub should enable the user to add another record (row). The name of the new contact is entered into the NewName text box and when the AddButton is clicked, an input box should ask the user for the phone number of the new contact. The new name and phone number should be placed in the next empty row of the spreadsheet with the other data.

DeleteItem – After selecting a name from the combo box, the user can delete that contact by clicking on the DeleteButton, which will run the DeleteItem sub. Make sure to remove the deleted item from ComboBox1; otherwise, the deleted item will remain on the drop-down list until the user form is closed.

UpdateNumber – The user can select a name from ComboBox1 and modify/update the phone number of that contact by clicking on the PhoneButton. This should run the UpdateNumber sub, which will ask the user for the new number and replace that contact’s old number on the spreadsheet with the new number.


Screenshots:

1616680164514.png
1616680244942.png


This the code I have so far:
VBA Code:
Option Explicit
Sub RunForm()
PopulateComboBox
NameForm.Show
End Sub

Sub PopulateComboBox()
Dim Names() As String
Dim n As Integer
Dim i As Integer
n = WorksheetFunction.CountA(Columns("A:A"))
ReDim Names (n) As String
For i = 1 To n
    Names(i) = Range("A1:A" & n).Cells(i, 1)
    NameForm.ComboBox1.AddItem Names(i)
Next i
NameForm.ComboBox1.Text = Names(1)
End Sub

Sub AddName()
Dim nRows As Integer, i As Integer, pn As String
'Input validation
If NameForm.NewName = "" Then
    MsgBox "Name field cannot be left blank!"
    Exit Sub
End If

' I am not sure what code to use. I found the part below in  n other forum. 
varWorksheet.Range("B" & varNRows + 1) = varInputID
     cboItems.RowSource = "YourSheetName!B1:B" & varNRows + 1
     varWorksheet.Range("A" & varNRows + 1) = TextBox1.Text
      TextBox1.Text = ""
      MsgBox ("New item is added")
End Sub

Sub DeleteItem()
Dim Index As Integer
Dim Ans As Integer
Dim ReoveIndex As Integer
Ans = MsgBox("Are you sure you want to delete this record?!", 20)
If Ans = 6 Then
'Code does not work!
RemoveIndex = ComboBox1.ListIndex
    NameForm.ComboBox1.RemoveItem RemoveIndex
    Rows(RemoveIndex + 1 & ":" & Index + 1).Select
    Selection.Delete Shift:=xlUp
    NameForm.ComboBox1.Text = Range("A1")
End If
Range("A1").Select
NameForm.ComboBox1.Value = Range("A1")
End Sub

Sub UpdateNumber()
Dim Ans As String, Index As Integer
Ans = InputBox("What is " & NameForm.ComboBox1.Value & "'s new phone number?")
If Ans <> "" Then 'Protects against empty input OR cancel button
  
    'I don't know what code to use!
  
End If
End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi all

I newly started to lerne VBA and have some problems to write the code for this Problem. Especially adding and deleting lineitems (names and phone numbers) in the list and updating the phone numbers.
Any help is very much appreciated.

Thank you very much.
Best regards


Problem Statement
Create a user form that allows the user to manage (add, edit, and delete) the names and phone numbers of contacts.

5 separate subroutines are required:

RunForm – the on-sheet button is linked to this sub, which simply opens up the NameForm user form.

PopulateComboBox – Prior to opening NameForm, this sub should populate ComboBox1 on NameForm with the names in column A of the spreadsheet.

AddName – This sub should enable the user to add another record (row). The name of the new contact is entered into the NewName text box and when the AddButton is clicked, an input box should ask the user for the phone number of the new contact. The new name and phone number should be placed in the next empty row of the spreadsheet with the other data.

DeleteItem – After selecting a name from the combo box, the user can delete that contact by clicking on the DeleteButton, which will run the DeleteItem sub. Make sure to remove the deleted item from ComboBox1; otherwise, the deleted item will remain on the drop-down list until the user form is closed.

UpdateNumber – The user can select a name from ComboBox1 and modify/update the phone number of that contact by clicking on the PhoneButton. This should run the UpdateNumber sub, which will ask the user for the new number and replace that contact’s old number on the spreadsheet with the new number.


Screenshots:

View attachment 35310View attachment 35311

This the code I have so far:
VBA Code:
Option Explicit
Sub RunForm()
PopulateComboBox
NameForm.Show
End Sub

Sub PopulateComboBox()
Dim Names() As String
Dim n As Integer
Dim i As Integer
n = WorksheetFunction.CountA(Columns("A:A"))
ReDim Names (n) As String
For i = 1 To n
    Names(i) = Range("A1:A" & n).Cells(i, 1)
    NameForm.ComboBox1.AddItem Names(i)
Next i
NameForm.ComboBox1.Text = Names(1)
End Sub

Sub AddName()
Dim nRows As Integer, i As Integer, pn As String
'Input validation
If NameForm.NewName = "" Then
    MsgBox "Name field cannot be left blank!"
    Exit Sub
End If

' I am not sure what code to use. I found the part below in  n other forum.
varWorksheet.Range("B" & varNRows + 1) = varInputID
     cboItems.RowSource = "YourSheetName!B1:B" & varNRows + 1
     varWorksheet.Range("A" & varNRows + 1) = TextBox1.Text
      TextBox1.Text = ""
      MsgBox ("New item is added")
End Sub

Sub DeleteItem()
Dim Index As Integer
Dim Ans As Integer
Dim ReoveIndex As Integer
Ans = MsgBox("Are you sure you want to delete this record?!", 20)
If Ans = 6 Then
'Code does not work!
RemoveIndex = ComboBox1.ListIndex
    NameForm.ComboBox1.RemoveItem RemoveIndex
    Rows(RemoveIndex + 1 & ":" & Index + 1).Select
    Selection.Delete Shift:=xlUp
    NameForm.ComboBox1.Text = Range("A1")
End If
Range("A1").Select
NameForm.ComboBox1.Value = Range("A1")
End Sub

Sub UpdateNumber()
Dim Ans As String, Index As Integer
Ans = InputBox("What is " & NameForm.ComboBox1.Value & "'s new phone number?")
If Ans <> "" Then 'Protects against empty input OR cancel button
 
    'I don't know what code to use!
 
End If
End Sub
did you get the full code? can i have it?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top