Add, Edit and Delete Data using Combo Boxes

vba beg

New Member
Joined
Nov 22, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.

1606408558145.png
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
379
Office Version
  1. 2007
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Nov 22, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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

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

Watch MrExcel Video

Forum statistics

Threads
1,126,934
Messages
5,621,688
Members
415,851
Latest member
JohnZ1156

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
Top