remove an item from a combobox.

[nparsons75]

I need to remove an item from a combo box.

I have been advised to use:
Me.axlenumbox.RemoveItem axlenumbox.ListIndex

Currently it does not work.

My data for the combo box is imported from a spreadsheet.

When I select the data in the combobox and press the submit button on the form I need this selected data to be removed from the combobox, but not the spreadsheet.

[snb]

You probably used 'rowsource' ; never do that again ! ;)
You should populate the combobox using


axlenumbox.List=range("A1:K10").value

[nparsons75]

ha ha. No i didn't :hi:

I populate the combobox with this code:




Private Sub UserForm_Activate()
Dim SourceWB As Workbook
Dim rng As Range, Item As Range
Dim i As Integer
Application.ScreenUpdating = False
With Me.axlenumbox
.Clear ' remove existing entries from the combobox
' open the source workbook as ReadOnly
Set SourceWB = Workbooks.Open("J:\WHEELSET FLOW SYSTEM\LIVE SYSTEM\database_np_201403190805.xlsx", _
False, True)
'set the data range
With SourceWB.Worksheets("database")
Set rng = .Range(.Range("A5"), .Range("A" & .Rows.Count).End(xlUp))
End With
' get the values you want

For Each Item In rng
If Item.Offset(0, 3).Value <> "FAIL" Then
.AddItem Item.Value ' populate the listbox
End If
Next Item
.ListIndex = -1 ' no items selected, set to 0 to select the first item
End With
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
Application.ScreenUpdating = True
End Sub

[nparsons75]

this is why i think i can not get the box to clear by using removeitem and listbox.

New to vba, struggling

[snb]

Private Sub UserForm_Initialize()
with getobject("J:\WHEELSET FLOW SYSTEM\LIVE SYSTEM\database_np_201403190805.xlsx")

With .sheets("database").cells(1).currentregion
.autofilter 3, "<>FAIL"
.offset(1).copy .parent.cells(1,100)
axlenumbox.List=.parent.cells(1,100).currentregion.value
end with

.close
End With
End Sub

[nparsons75]

Sorry, im new to VBA, will this code fix my issue? Where do I add it? Thank you.

[Kenneth Hobs]

The code that snb posted, goes into your userform object. Doubleclick the userform or click the userform and press F7 to enter the code for it. Rename your Activate Sub as snb used the Initialize event to fill your combobox.

As always, test code on a backup copy of your file(s).

[nparsons75]

I'm ok opening up the via and inserting the code. I just can't work out exactly where. Thank you for taking the time to assist.

[nparsons75]

do i replace the whole code? or add to the code i have?

[Kenneth Hobs]

Think about what I said. Activate and Initialize are two separate events. Rather than deleting your Activate code, just rename it or delete it if you like. Then try snb's code. Otherwise, you run the risk of not knowing which is working if both work somewhat.

Event triggered subs or any sub for that matter, it does not matter where you put it providing it is in the right object: Userform, Sheet, ThisWorkbook, Module, and Class.

[nparsons75]

Thank you for your help Kenneth. I Am new to via so please excuse me. I have been struggling with this issue for days. I just can't get it working, so very frustrating. I would love to learn via, need to find a good tutorial I think.

[snb]

You'd better buy a VBA Basic book.

You posted a userform code. Replace that code by mine.

[Kenneth Hobs]

To get the best help, help us help you by posting a short example of your workbook. Too many things can happen that we may not know about otherwise. For example, the method that was used to fill the combobox limits what you can do as snb explained. There are many ways to fill a combobox.

While there are many books you can buy to learn VBA, there are many free sources on-line as well. e.g. http://excel-macro.tutorialhorizon.com/vba-excel-user-forms/

[nparsons75]

Thank you Kenneth, I will try and arrange a simpler version of the spreadsheet but its difficult how I have set it up. (with lots of help)

SNB I replaced the code with yours and get the following error: autofilter method of range class failed?

[snb]

Then there's a task for you to learn VBA very quickly by debugging (that's how I learned it).

[nparsons75]

I want to pull my hair out..... I have been trying for he last 30 minutes... Something to do with the autofilter.

Your code has taken away a lot of the other code, like the range set for example, how does your code know where to look.

Im baffled.

[david000]

I want to pull my hair out.....

You still have hair?

Can you post the code for the "Submit" button that is using the "RemoveItem" method? I'm confused why that's not working, I just made a mock-up with the exact same type of code you used here and it worked fine.

Also, don't worry about snb's code, you don't want to go bald before it's time. Learning should be an enjoyable process if done right. You really have to be a battle scared vertern to appreciate snb 90% of the time.

[nparsons75]

Only just...... This is one of a coulle of issues i have and yes, i agree, i live to learn and have indeed managed to learn a fair bit recently, just not enough. I need to have this working sooner rather than later so desparate for hell. I know its close, so close. I will post the code asap. Will be in about 90 mins when i get to my pc. Really appreciate the help off everyone.

[nparsons75]

Hi David, here is the code for the submit button. Below the submit code is the code for how the combobox is populated. Below that is the code as a whole for the user form. Appreciate any help.


Private Sub SUBMITBUT_Click()Dim ws As Worksheet
Dim sFileName As String
Dim sFolderName As String
Dim LastRow As Long
Dim wbDest As Workbook
Dim pad As Long
Dim msg As String
Dim Title As String


If Not IsDate(Me.datebox.Value) Then
MsgBox "The Date box must contain a date.", vbExclamation, "Date Entry"
Me.datebox.SetFocus
Exit Sub
End If


If Me.axlenumbox.Value = "" Then
MsgBox "Please enter an Axle Number.", vbExclamation, "Axle Number?"
Me.axlenumbox.SetFocus
Exit Sub
End If


'If Me.wsettypecom.Value = "" Then
'MsgBox "Please select a Wheelset Type.", vbExclamation, "Wheelset Type?"
'Me.wsettypecom.SetFocus
'Exit Sub
'End If


If Me.bookedincom.Value = "" Then
MsgBox "Please select an operator to book in.", vbExclamation, "Booked in by?"
Me.bookedincom.SetFocus
Exit Sub
End If


If Me.statuscom.Value = "" Then
MsgBox "Please select a Status.", vbExclamation, "Pass or Fail?"
Me.statuscom.SetFocus
Exit Sub
End If




sFileName = "database_np_201403190805.xlsx"
sFolderName = "J:\WHEELSET FLOW SYSTEM\LIVE SYSTEM\"




Application.ScreenUpdating = False
If Not Dir(sFolderName & sFileName, vbDirectory) = vbNullString Then
Set wbDest = Workbooks.Open(sFolderName & sFileName, ReadOnly:=False)
Else
pad = Len(sFolderName & sFileName) / 2
msg = MsgBox(sFolderName & sFileName & Chr(10) & Chr(10) & _
Space(pad) & "File Not Found", vbInformation, Title)
GoTo progend
End If




Set ws = wbDest.Sheets("Database")




Dim Foundcell As Range
With ws
Set Foundcell = .Columns(1).Find(Me.axlenumbox.Text, LookIn:=xlValues, lookat:=xlWhole)


If Not Foundcell Is Nothing Then
'update data from userform to worksheet ranges
.Cells(Foundcell.Row, 11).Value = Me.axlenumbox.Text
.Cells(Foundcell.Row, 12).Value = Me.wsettypecom.Text
.Cells(Foundcell.Row, 13).Value = Me.bookedincom.Text
.Cells(Foundcell.Row, 14).Value = Me.statuscom.Text
.Cells(Foundcell.Row, 15).Value = Me.datebox.Text
'
' etc etc
'
Else
MsgBox Me.axlenumbox.Text & Chr(10) & "Record Not Found", 48, "Not Found"
End If

End With
wbDest.Close True
Me.axlenumbox.RemoveItem (Me.axlenumbox.ListIndex)
progend:
Application.ScreenUpdating = False
Unload Me
PAINTSTAGE3.Show
Application.ScreenUpdating = True
End Sub


Populate combobox code:


Private Sub UserForm_Activate() Dim SourceWB As Workbook
Dim rng As Range, Item As Range
Dim i As Integer
Application.ScreenUpdating = False
With Me.axlenumbox
.Clear ' remove existing entries from the combobox
' open the source workbook as ReadOnly
Set SourceWB = Workbooks.Open("J:\WHEELSET FLOW SYSTEM\LIVE SYSTEM\database_np_201403190805.xlsx", _
False, True)
'set the data range
With SourceWB.Worksheets("database")
Set rng = .Range(.Range("f5"), .Range("f" & .Rows.Count).End(xlUp))
End With

' get the values you want

For Each Item In rng
If Item.Offset(0, 3).Value <> "FAIL" Then
.AddItem Item.Value ' populate the listbox
End If
Next Item
.ListIndex = -1 ' no items selected, set to 0 to select the first item
End With
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
Application.ScreenUpdating = True
End Sub

Total code:




'---------------------------------------------------------------------------------------
' Module : UserForm1
' DateTime : 02/11/2005 13:49
' Author : royUK
' Website : www.excel-it.com
' Purpose : load a combobox from a closed workbook
'---------------------------------------------------------------------------------------
Option Explicit



Private Sub todaysdate_Click()
datebox.Value = Format(DateTime.Now, "DD MMM YYYY hh:mm:ss")
End Sub


Private Sub UserForm_Activate()
Dim SourceWB As Workbook
Dim rng As Range, Item As Range
Dim i As Integer
Application.ScreenUpdating = False
With Me.axlenumbox
.Clear ' remove existing entries from the combobox
' open the source workbook as ReadOnly
Set SourceWB = Workbooks.Open("J:\WHEELSET FLOW SYSTEM\LIVE SYSTEM\database_np_201403190805.xlsx", _
False, True)
'set the data range
With SourceWB.Worksheets("database")
Set rng = .Range(.Range("f5"), .Range("f" & .Rows.Count).End(xlUp))
End With

' get the values you want

For Each Item In rng
If Item.Offset(0, 3).Value <> "FAIL" Then
.AddItem Item.Value ' populate the listbox
End If
Next Item
.ListIndex = -1 ' no items selected, set to 0 to select the first item
End With
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
Application.ScreenUpdating = True
End Sub







Private Sub clearbut_Click()
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub


Private Sub SUBMITBUT_Click()
Dim ws As Worksheet
Dim sFileName As String
Dim sFolderName As String
Dim LastRow As Long
Dim wbDest As Workbook
Dim pad As Long
Dim msg As String
Dim Title As String


If Not IsDate(Me.datebox.Value) Then
MsgBox "The Date box must contain a date.", vbExclamation, "Date Entry"
Me.datebox.SetFocus
Exit Sub
End If


If Me.axlenumbox.Value = "" Then
MsgBox "Please enter an Axle Number.", vbExclamation, "Axle Number?"
Me.axlenumbox.SetFocus
Exit Sub
End If


'If Me.wsettypecom.Value = "" Then
'MsgBox "Please select a Wheelset Type.", vbExclamation, "Wheelset Type?"
'Me.wsettypecom.SetFocus
'Exit Sub
'End If


If Me.bookedincom.Value = "" Then
MsgBox "Please select an operator to book in.", vbExclamation, "Booked in by?"
Me.bookedincom.SetFocus
Exit Sub
End If


If Me.statuscom.Value = "" Then
MsgBox "Please select a Status.", vbExclamation, "Pass or Fail?"
Me.statuscom.SetFocus
Exit Sub
End If




sFileName = "database_np_201403190805.xlsx"
sFolderName = "J:\WHEELSET FLOW SYSTEM\LIVE SYSTEM\"




Application.ScreenUpdating = False
If Not Dir(sFolderName & sFileName, vbDirectory) = vbNullString Then
Set wbDest = Workbooks.Open(sFolderName & sFileName, ReadOnly:=False)
Else
pad = Len(sFolderName & sFileName) / 2
msg = MsgBox(sFolderName & sFileName & Chr(10) & Chr(10) & _
Space(pad) & "File Not Found", vbInformation, Title)
GoTo progend
End If




Set ws = wbDest.Sheets("Database")




Dim Foundcell As Range
With ws
Set Foundcell = .Columns(1).Find(Me.axlenumbox.Text, LookIn:=xlValues, lookat:=xlWhole)


If Not Foundcell Is Nothing Then
'update data from userform to worksheet ranges
.Cells(Foundcell.Row, 11).Value = Me.axlenumbox.Text
.Cells(Foundcell.Row, 12).Value = Me.wsettypecom.Text
.Cells(Foundcell.Row, 13).Value = Me.bookedincom.Text
.Cells(Foundcell.Row, 14).Value = Me.statuscom.Text
.Cells(Foundcell.Row, 15).Value = Me.datebox.Text
'
' etc etc
'
Else
MsgBox Me.axlenumbox.Text & Chr(10) & "Record Not Found", 48, "Not Found"
End If

End With
wbDest.Close True
Me.axlenumbox.RemoveItem (Me.axlenumbox.ListIndex)
progend:
Application.ScreenUpdating = False
Unload Me
PAINTSTAGE3.Show
Application.ScreenUpdating = True
End Sub

[david000]

this is why i think i can not get the box to clear by using removeitem and listbox.

New to vba, struggling

Unless you're asking what to do if the item is NOT found try this;


Me.axlenumbox.RemoveItem (Me.axlenumbox.ListIndex)
Me.axlenumbox.ListIndex = -1 'Clears the Combobox


Move the lines here between the if statement to remove the item ONLY if its not found.


MsgBox Me.axlenumbox.Text & Chr(10) & "Record Not Found", 48, "Not Found"
Me.axlenumbox.RemoveItem (Me.axlenumbox.ListIndex)
Me.axlenumbox.ListIndex = -1 'Clears the Combobox

[nparsons75]

Hi David, Sorry, I can't figure out where to put the lines in my code. Can they go anywhere? I am really hoping this works, it has been days. Still have hair, but not much....

[nparsons75]

Im not entirely sure what this line does? MsgBox Me.axlenumbox.Text & Chr(10) & "Record Not Found", 48, "Not Found"

This has been a collection of many bits of code put together. (not the best way i guess).

If the axle serial number has a "PASS" then in should be visible in the next stage combobox. If a "FAIL" then it should not show. Also I dont want the combobox to show blanks.

I tried it the 2nd way as I can see where you wanted me to put that, there were no errors but the combobox still contained all the values.