Add Name and Update the Number in the excel spreadsheet via User Form

[325Sam]

Please help me in VBA sub for "Update Phone Number", "Add Name" and "Done".

• 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.

• 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.

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

'Place your code here!

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
    
    'Place your code here!
    
End If
End Sub

Private Sub DoneButton_Click()
'Place your code here!
End Sub

 

[Max1616]

Perhaps something like this? (I didn't test this)

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

pn = InputBox("Please Enter " & NameForm.NewName & "'s phone number.", "New Name")

'Add phone validation if you want

nRows = Cells(Rows.Count, 1).End(xlUp).Row

Cells(nRows, 1) = NameForm.NewName
Cells(nRows, 2) = pn

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
    
    Index = WorksheetFunction.Match(NameForm.ComboBox1, Range("A:A"), False)
    Cells(Index, 1) = Ans
    
End If
End Sub

Private Sub DoneButton_Click()
'Place your code here!
End Sub

 

[325Sam]

Perhaps something like this? (I didn't test this)

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

pn = InputBox("Please Enter " & NameForm.NewName & "'s phone number.", "New Name")

'Add phone validation if you want

nRows = Cells(Rows.Count, 1).End(xlUp).Row

Cells(nRows, 1) = NameForm.NewName
Cells(nRows, 2) = pn

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
   
    Index = WorksheetFunction.Match(NameForm.ComboBox1, Range("A:A"), False)
    Cells(Index, 1) = Ans
   
End If
End Sub

Private Sub DoneButton_Click()
'Place your code here!
End Sub

1. Add Name - Code is updating Name and number in 6th row only instead of adding new row.
2. Add Name - New Name is not populated in ComboBox1 for update field
3. Update Number - Updated number is reflected in Column A instead of Column B

 

[Max1616]

I made the edits for point 1 & 3, I'm unsure what you meant by point 2. I'm grabbing the new name from NameForm.NewName.

Thanks!

        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

pn = InputBox("Please Enter " & NameForm.NewName & "'s phone number.", "New Name")

'Add phone validation if you want

nRows = Cells(Rows.Count, 1).End(xlUp).Row +1

Cells(nRows, 1) = NameForm.NewName
Cells(nRows, 2) = pn

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
   
Index = WorksheetFunction.Match(NameForm.ComboBox1, Range("A:A"), False)
Cells(Index, 2) = Ans
   
End If
End Sub

Private Sub DoneButton_Click()
'Place your code here!
End Sub

 

[325Sam]

I made the edits for point 1 & 3, I'm unsure what you meant by point 2. I'm grabbing the new name from NameForm.NewName.

Thanks!

Thank you Max!