Edit, Save and Delete from Listbox

S

Siledhrel

New Member
Joined
Jan 27, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hey there everyone,

I've been trying to wrap my head around this for a while now.

I'm trying to build a simple data entry form for drinks recipes, one which I can edit as I want.

I have a Listbox on a userform which I've coded to show the names of the drinks and then populate various textboxes and comboboxes.

What I cannot figure out is how I can then edit those values and save to the worksheet or delete the entire entry from the worksheet.
VBA Code: 
Private Sub ListBoxViewCocktails_Click()



With Me
        .TextViewCockSize1 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 6, False)
        .ComboViewCockMeas1 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 7, False)
        .TextViewCockIngred1 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 5, False)
        .TextViewCockSize2 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 10, False)
        .ComboViewCockMeas2 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 11, False)
        .TextViewCockIngred2 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 9, False)
        .TextViewCockSize3 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 14, False)
        .ComboViewCockMeas3 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 15, False)
        .TextViewCockIngred3 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 13, False)
        .TextViewCockSize4 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 18, False)
        .ComboViewCockMeas4 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 19, False)
        .TextViewCockIngred4 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 17, False)
        .TextViewCockSize5 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 22, False)
        .ComboViewCockMeas5 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 23, False)
        .TextViewCockIngred5 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 21, False)
        .TextViewCockSize6 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 26, False)
        .ComboViewCockMeas6 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 27, False)
        .TextViewCockIngred6 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 25, False)
        .TextViewCockSize7 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 30, False)
        .ComboViewCockMeas7 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 31, False)
        .TextViewCockIngred7 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 29, False)
        .TextViewCockSize8 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 34, False)
        .ComboViewCockMeas8 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 35, False)
        .TextViewCockIngred8 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 33, False)
        .TextViewCockSize9 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 38, False)
        .ComboViewCockMeas9 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 39, False)
        .TextViewCockIngred9 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 37, False)
        .TextViewCockSize10 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 42, False)
        .ComboViewCockMeas10 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 43, False)
        .TextViewCockIngred10 = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 41, False)
        .ComboViewCockGlassware = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 45, False)
        .ComboViewCockMethod = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 46, False)
        .TextViewCockMethod = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 47, False)
        .TextViewCockName = Application.WorksheetFunction.VLookup(Me.ListBoxViewCocktails, Sheet2.Range("DrName"), 1, False)
              
        
        
End With

End Sub

"DrName" is a dynamic range on the worksheet.

The above is the code for viewing the list box. I have two command buttons called "Save" and "Delete" waiting to be used for their respective purposes. How can I go from viewing those values to overwriting them? Or to deleting them?

This is my first time posting, so I apologise for any breaches of etiquette. Unfortunately I cannot install XL2BB on this computer so. Any help will be greatly appreciated.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
I adjusted your code to be a bit more practical, replace your code with this:
VBA Code: 
Private Sub ListBoxViewCocktails_Click()
  Dim f As Range, rng As Range
  
  Set rng = Sheet2.Range("DrName")
  Set f = rng.Find(ListBoxViewCocktails, , xlValues, xlWhole, , , False)
  
  If Not f Is Nothing Then
    TextViewCockSize1 = rng.Cells(f.Row, 6)
    ComboViewCockMeas1 = rng.Cells(f.Row, 7)
    TextViewCockIngred1 = rng.Cells(f.Row, 5)
    TextViewCockSize2 = rng.Cells(f.Row, 10)
    ComboViewCockMeas2 = rng.Cells(f.Row, 11)
    TextViewCockIngred2 = rng.Cells(f.Row, 9)
    TextViewCockSize3 = rng.Cells(f.Row, 14)
    ComboViewCockMeas3 = rng.Cells(f.Row, 15)
    TextViewCockIngred3 = rng.Cells(f.Row, 13)
    TextViewCockSize4 = rng.Cells(f.Row, 18)
    ComboViewCockMeas4 = rng.Cells(f.Row, 19)
    TextViewCockIngred4 = rng.Cells(f.Row, 17)
    TextViewCockSize5 = rng.Cells(f.Row, 22)
    ComboViewCockMeas5 = rng.Cells(f.Row, 23)
    TextViewCockIngred5 = rng.Cells(f.Row, 21)
    TextViewCockSize6 = rng.Cells(f.Row, 26)
    ComboViewCockMeas6 = rng.Cells(f.Row, 27)
    TextViewCockIngred6 = rng.Cells(f.Row, 25)
    TextViewCockSize7 = rng.Cells(f.Row, 30)
    ComboViewCockMeas7 = rng.Cells(f.Row, 31)
    TextViewCockIngred7 = rng.Cells(f.Row, 29)
    TextViewCockSize8 = rng.Cells(f.Row, 34)
    ComboViewCockMeas8 = rng.Cells(f.Row, 35)
    TextViewCockIngred8 = rng.Cells(f.Row, 33)
    TextViewCockSize9 = rng.Cells(f.Row, 38)
    ComboViewCockMeas9 = rng.Cells(f.Row, 39)
    TextViewCockIngred9 = rng.Cells(f.Row, 37)
    TextViewCockSize10 = rng.Cells(f.Row, 42)
    ComboViewCockMeas10 = rng.Cells(f.Row, 43)
    TextViewCockIngred10 = rng.Cells(f.Row, 41)
    ComboViewCockGlassware = rng.Cells(f.Row, 45)
    ComboViewCockMethod = rng.Cells(f.Row, 46)
    TextViewCockMethod = rng.Cells(f.Row, 47)
    TextViewCockName = rng.Cells(f.Row, 1)
  End If
End Sub

To save:
VBA Code: 
Private Sub CommandButton1_Click()  'Save
  Dim f As Range, rng As Range
  
  If ListBoxViewCocktails.ListIndex = -1 Then
    MsgBox "Select an item"
    Exit Sub
  End If
  
  Set rng = Sheet2.Range("DrName")
  Set f = rng.Find(ListBoxViewCocktails, , xlValues, xlWhole, , , False)
  
  If Not f Is Nothing Then
    rng.Cells(f.Row, 6) = TextViewCockSize1
    rng.Cells(f.Row, 7) = ComboViewCockMeas1
    rng.Cells(f.Row, 5) = TextViewCockIngred1
    rng.Cells(f.Row, 10) = TextViewCockSize2
    rng.Cells(f.Row, 11) = ComboViewCockMeas2
    rng.Cells(f.Row, 9) = TextViewCockIngred2
    rng.Cells(f.Row, 14) = TextViewCockSize3
    rng.Cells(f.Row, 15) = ComboViewCockMeas3
    rng.Cells(f.Row, 13) = TextViewCockIngred3
    rng.Cells(f.Row, 18) = TextViewCockSize4
    rng.Cells(f.Row, 19) = ComboViewCockMeas4
    rng.Cells(f.Row, 17) = TextViewCockIngred4
    rng.Cells(f.Row, 22) = TextViewCockSize5
    rng.Cells(f.Row, 23) = ComboViewCockMeas5
    rng.Cells(f.Row, 21) = TextViewCockIngred5
    rng.Cells(f.Row, 26) = TextViewCockSize6
    rng.Cells(f.Row, 27) = ComboViewCockMeas6
    rng.Cells(f.Row, 25) = TextViewCockIngred6
    rng.Cells(f.Row, 30) = TextViewCockSize7
    rng.Cells(f.Row, 31) = ComboViewCockMeas7
    rng.Cells(f.Row, 29) = TextViewCockIngred7
    rng.Cells(f.Row, 34) = TextViewCockSize8
    rng.Cells(f.Row, 35) = ComboViewCockMeas8
    rng.Cells(f.Row, 33) = TextViewCockIngred8
    rng.Cells(f.Row, 38) = TextViewCockSize9
    rng.Cells(f.Row, 39) = ComboViewCockMeas9
    rng.Cells(f.Row, 37) = TextViewCockIngred9
    rng.Cells(f.Row, 42) = TextViewCockSize10
    rng.Cells(f.Row, 43) = ComboViewCockMeas10
    rng.Cells(f.Row, 41) = TextViewCockIngred10
    rng.Cells(f.Row, 45) = ComboViewCockGlassware
    rng.Cells(f.Row, 46) = ComboViewCockMethod
    rng.Cells(f.Row, 47) = TextViewCockMethod
    rng.Cells(f.Row, 1) = TextViewCockName
  End If
End Sub

To delete:
VBA Code: 
Private Sub CommandButton2_Click()  'Delete
  Dim f As Range, rng As Range
  
  With ListBoxViewCocktails
    If .ListIndex = -1 Then
      MsgBox "Select an item"
      Exit Sub
    End If
    
    Set rng = Sheet2.Range("DrName")
    If MsgBox("Are you sure", vbYesNo + vbQuestion) = vbYes Then
      Set f = rng.Find(.Value, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        f.EntireRow.Delete
      End If
    End If
    .List = rng.Value
  End With
End Sub
 
Upvote 0
DanteAmor said:
I adjusted your code to be a bit more practical, replace your code with this:
VBA Code: 
Private Sub ListBoxViewCocktails_Click()
  Dim f As Range, rng As Range
 
  Set rng = Sheet2.Range("DrName")
  Set f = rng.Find(ListBoxViewCocktails, , xlValues, xlWhole, , , False)
 
  If Not f Is Nothing Then
    TextViewCockSize1 = rng.Cells(f.Row, 6)
    ComboViewCockMeas1 = rng.Cells(f.Row, 7)
    TextViewCockIngred1 = rng.Cells(f.Row, 5)
    TextViewCockSize2 = rng.Cells(f.Row, 10)
    ComboViewCockMeas2 = rng.Cells(f.Row, 11)
    TextViewCockIngred2 = rng.Cells(f.Row, 9)
    TextViewCockSize3 = rng.Cells(f.Row, 14)
    ComboViewCockMeas3 = rng.Cells(f.Row, 15)
    TextViewCockIngred3 = rng.Cells(f.Row, 13)
    TextViewCockSize4 = rng.Cells(f.Row, 18)
    ComboViewCockMeas4 = rng.Cells(f.Row, 19)
    TextViewCockIngred4 = rng.Cells(f.Row, 17)
    TextViewCockSize5 = rng.Cells(f.Row, 22)
    ComboViewCockMeas5 = rng.Cells(f.Row, 23)
    TextViewCockIngred5 = rng.Cells(f.Row, 21)
    TextViewCockSize6 = rng.Cells(f.Row, 26)
    ComboViewCockMeas6 = rng.Cells(f.Row, 27)
    TextViewCockIngred6 = rng.Cells(f.Row, 25)
    TextViewCockSize7 = rng.Cells(f.Row, 30)
    ComboViewCockMeas7 = rng.Cells(f.Row, 31)
    TextViewCockIngred7 = rng.Cells(f.Row, 29)
    TextViewCockSize8 = rng.Cells(f.Row, 34)
    ComboViewCockMeas8 = rng.Cells(f.Row, 35)
    TextViewCockIngred8 = rng.Cells(f.Row, 33)
    TextViewCockSize9 = rng.Cells(f.Row, 38)
    ComboViewCockMeas9 = rng.Cells(f.Row, 39)
    TextViewCockIngred9 = rng.Cells(f.Row, 37)
    TextViewCockSize10 = rng.Cells(f.Row, 42)
    ComboViewCockMeas10 = rng.Cells(f.Row, 43)
    TextViewCockIngred10 = rng.Cells(f.Row, 41)
    ComboViewCockGlassware = rng.Cells(f.Row, 45)
    ComboViewCockMethod = rng.Cells(f.Row, 46)
    TextViewCockMethod = rng.Cells(f.Row, 47)
    TextViewCockName = rng.Cells(f.Row, 1)
  End If
End Sub

To save:
VBA Code: 
Private Sub CommandButton1_Click()  'Save
  Dim f As Range, rng As Range
 
  If ListBoxViewCocktails.ListIndex = -1 Then
    MsgBox "Select an item"
    Exit Sub
  End If
 
  Set rng = Sheet2.Range("DrName")
  Set f = rng.Find(ListBoxViewCocktails, , xlValues, xlWhole, , , False)
 
  If Not f Is Nothing Then
    rng.Cells(f.Row, 6) = TextViewCockSize1
    rng.Cells(f.Row, 7) = ComboViewCockMeas1
    rng.Cells(f.Row, 5) = TextViewCockIngred1
    rng.Cells(f.Row, 10) = TextViewCockSize2
    rng.Cells(f.Row, 11) = ComboViewCockMeas2
    rng.Cells(f.Row, 9) = TextViewCockIngred2
    rng.Cells(f.Row, 14) = TextViewCockSize3
    rng.Cells(f.Row, 15) = ComboViewCockMeas3
    rng.Cells(f.Row, 13) = TextViewCockIngred3
    rng.Cells(f.Row, 18) = TextViewCockSize4
    rng.Cells(f.Row, 19) = ComboViewCockMeas4
    rng.Cells(f.Row, 17) = TextViewCockIngred4
    rng.Cells(f.Row, 22) = TextViewCockSize5
    rng.Cells(f.Row, 23) = ComboViewCockMeas5
    rng.Cells(f.Row, 21) = TextViewCockIngred5
    rng.Cells(f.Row, 26) = TextViewCockSize6
    rng.Cells(f.Row, 27) = ComboViewCockMeas6
    rng.Cells(f.Row, 25) = TextViewCockIngred6
    rng.Cells(f.Row, 30) = TextViewCockSize7
    rng.Cells(f.Row, 31) = ComboViewCockMeas7
    rng.Cells(f.Row, 29) = TextViewCockIngred7
    rng.Cells(f.Row, 34) = TextViewCockSize8
    rng.Cells(f.Row, 35) = ComboViewCockMeas8
    rng.Cells(f.Row, 33) = TextViewCockIngred8
    rng.Cells(f.Row, 38) = TextViewCockSize9
    rng.Cells(f.Row, 39) = ComboViewCockMeas9
    rng.Cells(f.Row, 37) = TextViewCockIngred9
    rng.Cells(f.Row, 42) = TextViewCockSize10
    rng.Cells(f.Row, 43) = ComboViewCockMeas10
    rng.Cells(f.Row, 41) = TextViewCockIngred10
    rng.Cells(f.Row, 45) = ComboViewCockGlassware
    rng.Cells(f.Row, 46) = ComboViewCockMethod
    rng.Cells(f.Row, 47) = TextViewCockMethod
    rng.Cells(f.Row, 1) = TextViewCockName
  End If
End Sub

To delete:
VBA Code: 
Private Sub CommandButton2_Click()  'Delete
  Dim f As Range, rng As Range
 
  With ListBoxViewCocktails
    If .ListIndex = -1 Then
      MsgBox "Select an item"
      Exit Sub
    End If
   
    Set rng = Sheet2.Range("DrName")
    If MsgBox("Are you sure", vbYesNo + vbQuestion) = vbYes Then
      Set f = rng.Find(.Value, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        f.EntireRow.Delete
      End If
    End If
    .List = rng.Value
  End With
End Sub
Click to expand...


Hey, sorry for taking so long to reply.

Thank you so much for the code.

Two things. The clean up you did doesn't populate my form with the values. I can still click the drink name in the ListBox, but my comboboxes and textboxes are empty. Do you know why that is?

So I tried leaving my original code and using your save and delete codes. They worked, especially the delete, although it throws up a 1004 error immediately afterwards because it can no longer find the entry on the list, so is there any way to update the list at the same time?

The Save function didn't update the currently chosen entry, it just added a new entry outside of my dynamic range, which meant it didn't appear in my ListBox.

Let me know if I can do anything else to help. I really appreciate this.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,743
Messages
6,126,609
Members
449,321
Latest member
syzer

Share this page

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
Back
Top