Updating associated value to an item selected from a comboBox in VBA

Question

I have an excel sheet which contains two columns ( the column A contains Names and the column B contains Number Phones ). I have created a comboBox with the list of names and I want to select a name from this comboBox and then update its corresponding phone number. I tried this code but it does not work.

Sub UpdateNumber()
Dim Ans As String, Index As Integer

Ans = InputBox("What is " & NameForm.ComboBox1.Value & " 's new phone number?")
If Ans <> "" Then 
    Index = NameForm.ComboBox1.ListIndex
    Sheets("Names").Range("A" & Index).Offset(0, 1).Value = Ans
       
End If
End Sub

Could someone help me, please ?

What does *Does not work* mean? Runtime error? Sheet is not updated? Wrong cell updated? Sub not called at all? — FunThomas, Mar 01 '21 at 10:37
I think that the problem was with the sub because after choosing the name from the ComboBox and clicking on the update phone number button, nothing is happening ! — wej, Mar 01 '21 at 10:43
did you add `call UpdateNumber` instruction to this "update phone number button" onclick event? — Tomasz, Mar 01 '21 at 10:50

score 0 · Answer 1 · answered Mar 01 '21 at 13:03

try below code

Sub UpdateNumber(Optional boxShow As Boolean = True)
Dim Ans As String, Index As Integer

If boxShow = false Then exit sub
Ans = InputBox("What is " & NameForm.ComboBox1.Value & " 's new phone number?")
If Ans <> "" Then 
    Index = NameForm.ComboBox1.ListIndex
    Sheets("Names").Range("A" & Index).Offset(0, 1).Value = Ans
       
End If
End Sub

sub btn_onclick()
     boxShow True   'no msgbox
     boxShow False  'with msgbox
     boxShow 'no msgbx

end sub

found there

Updating associated value to an item selected from a comboBox in VBA

1 Answers1