Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

show items in listbox after added in worksheet

0

I need to show the newly added items in listbox directly after add a new items

Private Sub cmdAdd_Click()
    Dim cellrange As String
    Dim rowno As Integer
    
    Dim AddFood As ListObject
    Dim AddBev As ListObject
    Dim AddShi As ListObject
    Dim AddOther As ListObject
    
    Set AddFood = Data.ListObjects("T_Food")
    Set AddBev = Data.ListObjects("T_Beverage")
    Set AddShi = Data.ListObjects("T_Shisha")
    
    
    
   
    
    Dim AddedRow As ListRow
'**************************************************

    
    If txtCode.Text = "" Or comDepartment.Text = "" Or txtItemName.Text = "" Or comUnit.Text = "" _
        Or txtPrice.Text = "" Or TxtSalePrice.Text = "" Then
          Beep
       MsgBox " Please Enter Data "
    Exit Sub
  End If
     
     Application.ScreenUpdating = False   'for faster macro
     Application.DisplayAlerts = False

   Set Da = Data
   Set ww = Application.WorksheetFunction

    With Da
    .Unprotect ("8521")
         If Me.comDepartment = "Food" Then
         
      cname = Me.txtItemName.Text
      fc = ww.CountIf(Data.Range("T_Food[[Item Name]]"), cname)
       If fc >= 1 Then
       amedia = MsgBox("  This name already exists  ", vbCritical, "Inventory Program")
     Exit Sub
   End If
   
      Set AddedRow = AddFood.ListRows.Add()
      With AddedRow
     
           AddedRow.Range(1) = Me.txtCode.Text    'Code
           AddedRow.Range(2) = Me.txtItemName.Text  'name
           AddedRow.Range(3) = Me.comUnit.Text         'Unit
           AddedRow.Range(4) = Me.txtPrice.Text        'CostPrice
           AddedRow.Range(5) = Me.TxtSalePrice.Text
           AddedRow.Range(8) = Me.CbSubCategory.Text  'Department
           
  End With
  End If
  '==========================================================================
   
   If Me.comDepartment = "Beverage" Then
      cname = Me.txtItemName.Text
      fc = ww.CountIf(Data.Range("T_Beverage[[Item Name]]"), cname)
       If fc >= 1 Then
       amedia = MsgBox("  This name already exists  ", vbCritical, "Inventory Program")
     Exit Sub
   End If
   
      Set AddedRow = AddBev.ListRows.Add()
      With AddedRow
     
           AddedRow.Range(1) = Me.txtCode.Text    'Code
           AddedRow.Range(2) = Me.txtItemName.Text  'name
           AddedRow.Range(3) = Me.comUnit.Text         'Unit
           AddedRow.Range(4) = Me.txtPrice.Text        'CostPrice
           AddedRow.Range(5) = Me.TxtSalePrice.Text
           AddedRow.Range(8) = Me.CbSubCategory.Text  'Departmen
        
      
     End With
  End If
  '==========================================================================
    
    If Me.comDepartment = "Shisha" Then
 cname = Me.txtItemName.Text
      fc = ww.CountIf(Data.Range("T_Shisha[[Item Name]]"), cname)
       If fc >= 1 Then
       amedia = MsgBox("  This name already exists  ", vbCritical, "Inventory Program")
     Exit Sub
   End If
   
      Set AddedRow = AddShi.ListRows.Add()
      With AddedRow
     
           AddedRow.Range(1) = Me.txtCode.Text    'Code
           AddedRow.Range(2) = Me.txtItemName.Text  'name
           AddedRow.Range(3) = Me.comUnit.Text         'Unit
           AddedRow.Range(4) = Me.txtPrice.Text        'CostPrice
           AddedRow.Range(5) = Me.TxtSalePrice.Text
           AddedRow.Range(8) = Me.CbSubCategory.Text  'Departmen
      
      
      

   If Me.comDepartment = "Other" Then
     cname = Me.txtItemName.Text
      fc = ww.CountIf(Data.Range("T_Other[[Item Name]]"), cname)
       If fc >= 1 Then
       amedia = MsgBox("  This name already exists  ", vbCritical, "Inventory Program")
     Exit Sub
   End If
   
      Set AddedRow = AddOther.ListRows.Add()
      With AddedRow
     
           
           AddedRow.Range(1) = Me.txtCode.Text    'Code
           AddedRow.Range(2) = Me.txtItemName.Text  'name
           AddedRow.Range(3) = Me.comUnit.Text         'Unit
           AddedRow.Range(4) = Me.txtPrice.Text        'CostPrice
           AddedRow.Range(5) = Me.TxtSalePrice.Text
           AddedRow.Range(8) = Me.CbSubCategory.Text  'Departmen
      
      
     End With
  End If
   '==========================================================================
 End With


 MsgBox " Item has added successfully ", vbInformation, "Inventory program "
 
 'End With
 

 
 End If
 End With
 Application.ScreenUpdating = True
End Sub
excel
posted by: Amin25 (rep: 16) on Sat Jan 13, 2024 at 5:50 pm
Answer
Discuss

Discussion

Hi again Amin. 

Thanks for asking a new question but it is incomplete and the Forum's Rules (see blue hyperlink above) ask you to "Include all relevant information" in yiur question text 

Currently, any new reader would not know which userform it is about or (myself included) how a new item is added in practice and so not bother to do anything.

Please help yourself to get an answer from someone - kindly edit your question to include enough detail for a reader to know the scenario you anticipate 
John_Ru (rep: 6142) Jan 14, '24 at 3:22 am
I edit and I added the code already so please can you help me 
Amin25 (rep: 16) Jan 14, '24 at 3:41 am
Add to Discussion

Answers

0
Selected Answer

Amin

Given the Listbox is updated by the UF SubCategory_Change code, you can just use that to revise the listbox.

In the attached revised file, I added the code in bold to the end of your sub (to also clear the detail boxes just added):

Private Sub cmdAdd_Click()

<< existing code>>
   '==========================================================================
 End With

 ' refresh the Listbox
    Call CbSubCategory_Change

 ' clear the other boxes to allow new item but leave Department and Subcategory unchanged
    Me.txtItemName = ""
    Me.txtPrice = ""
    Me.TxtSalePrice = ""
    Me.comUnit = ""

 MsgBox " Item has added successfully ", vbInformation, "Inventory program "

 'End With



 End If
 End With
 Application.ScreenUpdating = True
End Sub
 

(Tip: note the your Add Item code doesn't work if you have no Items in the subcategory e.g. Beverages/ Spirits and you can't add the Item Code since you disabled that TextBox).

Hope this fixes your problem. if so, please r emener to mark this Answer as Selected.

posted by: John_Ru (rep: 6142) on Sun Jan 14, 2024 at 8:51 am
Discuss

Discussion

You're amazing, thank you so much for helping and supporting us
Amin25 (rep: 16) Jan 14, '24 at 8:57 am
Glad that helped. Thanks for selecting my Answer, Amin.

Next time, please be sure to give more clues in your Question so it's easier for us to see the issue and provide a solution. 
John_Ru (rep: 6142) Jan 14, '24 at 9:26 am
Noted
Amin25 (rep: 16) Jan 14, '24 at 9:28 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login