Add, Edit and Delete Data using Combo Boxes

[vba beg]

Hello,

I'm trying to create a ComboBox where I update a database. I've managed to get the drop down to display the items as well as the delete item and done buttons to function properly.

What I'm unable to figure is the add Name and Update Stock buttons. I need the Update Stock button to do is update the identification code (located in column B) for the selected item in the dropdown. The new ID code will be entered in the box above the drop down.

I also need to be able to add new items to the list by adding the item to the empty box and then having a new input box pop up asking for the ID code.



I know the names aren't great and the formatting of the boxes could be better but that can be fixed after the macro works.

 

[EXCEL MAX]

Hello VbaBeg,
lets go step by step.
I don't have vision how your worksheet looks,
so I have create some simple code hoping it will be helpful.
If we do not understand each other,
explaine little bit better what is the problem.

Option Explicit

Dim varNRows, varInputID As String
Dim varWorksheet As Worksheet
Dim varItemUpdate

Private Sub UserForm_Initialize()
    
     Set varWorksheet = Sheets("YourSheetName")
     varNRows = varWorksheet.Cells(Rows.Count, 2).End(xlUp).Row
     cboItems.RowSource = "YourSheetName!A1:A" & varNRows
     
End Sub
    
Private Sub btnAddName_Click()
    
    varInputID = Application.InputBox("Enter item ID.", "NEW ITEM", , 500, 500)
    varNRows = varWorksheet.Cells(Rows.Count, 1).End(xlUp).Row
    If varInputID = "" Or TextBox1.Text = "" Then
        MsgBox ("Some fields are empty!")
    Else
        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 If
    
End Sub

Private Sub btnUpdateStock_Click()
    
     varNRows = varWorksheet.Cells(Rows.Count, 2).End(xlUp).Row
     Set varItemUpdate = varWorksheet.Range("A1:A" & varNRows).Find(cboItems.Text)
     If Not varItemUpdate Is Nothing Then
        varWorksheet.Range("B" & varItemUpdate.Row) = TextBox1.Text
        cboItems.RowSource = "YourSheetName!A1:A" & varNRows
        TextBox1.Text = ""
        cboItems.Text = ""
        MsgBox ("Item " & varItemUpdate & " is updated")
     End If
    
End Sub

 

[vba beg]

I'm a little stuck on the cboItems that I see a couple of times within your code

So Far I have the below for the add Name the below

Sub AddName()
Dim nRows As Integer, i As Integer, pn As String
If NameForm.NewName = "" Then
    MsgBox "Name field cannot be left blank!"
    Exit Sub
Else
 'I need whatever I enter the box to appear in the new row that I created 

End Sub

Call AddName
Dim AddIndex As String
AddIndex = ComboBox1.ListIndex
NameForm.ComboBox1.AddItem AddIndex
Rows(AddIndex + 1 & ":" & AddIndex + 1).Select
Selection.Insert Shift:=xlDown
ComboBox1.Text = Range("A1")
ActiveCell.Select
ActiveCell.Offset(0, 1) = InputBox("Enter Phone Number:")

For the other issue I have the below. Instead of the inputbox I also want to use the empty box

ActiveCell.Offset(0, 1) = InputBox("Enter Number:")