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:
This the code I have so far:
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:
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
