list box listfillrange entity and events

[Shane Barrignton]

Hello to anyone who can help

I am creating a little vb project in excel. It is a simple inventory system.
I have created 3 lists or arrays Listone, listtwo and listthree
I have created 3 listbox controls listonelistbox, listtwolistbox and
listthreelistbox
All three controls have their listfillrange property pointing to the
appopriate list.
I have created add and remove functionality for each of the lists.
My big problem is that I have to use a boolean value every time I remove
something from the lists
The reason for that is that when you remove the list item from the listone
for example the Listonelistbox control updates and Excel or VBA or MS
subsystem goes through each of the overridden controls code.

Could someone let me know if they have gone through something similar? I
would like to know how you got around the problem.

Kind Regards,

Shane

 

[Barb Reinhardt]

You may want to show your code.

 

[Shane Barrignton]

Thank you for the suggestion Barb.
I type in the new department into the DepartmentText control. I click on add
department button
As soon as the code gets to
Range("Departments").ListObject.ListRows.Add Departmentcount


The call stack fails in the InventoryList_click with a uninitialized
..SerialNumber textbox control:
.SerialNumber.Text = .InventoryListbox2.List(Inventorynumber, 2) (fails
here)



From Sheet2 :::
Private Sub AddDepartmentBtn_Click()
Dim Departmenttxt As String
Dim Departmentcount As Integer

If Not (Sheet3.UpdatingList) Then
Departmenttxt = DepartmentText.Text
Sheet3.UpdatingList = True
Sheet2.AddDeparment (Departmenttxt)
Sheet3.UpdatingList = False

DepartmentText.Text = ""
RefreshAvailableProjects
End If
End Sub


from Sheet2 :::
Sub AddDeparment(ByVal NewDepartment As String)
'
' AddRow Macro
' Macro recorded 9/18/2009 by Keane (Canada)
'
Dim Departmentcount As Integer
Dim DepartmentText As String
Dim FoundDepartment As Boolean
Dim AuditString As String
FoundDepartment = False
AuditString = ""


Dim listLimit As Integer
Departmentcount = 0
Departmentcount = Range("Departments").ListObject.ListRows.Count + 1

For Each c In Range("Departments")
If c.Value = NewDepartment Then
FoundDepartment = True
Exit For
End If
Next c


If (Not (FoundDepartment)) Then
Range("Departments").ListObject.ListRows.Add Departmentcount
Range("Departments").Cells(Departmentcount + 1, 1).Value =
NewDepartment
AuditString = "Added Department"
Sheet4.AddtoAdminAudit AuditString, NewDepartment
End If
End Sub

Private Sub InventoryListbox2_Click()
If Not (UpdatingList) Then
With Sheet3
Dim Inventorynumber As Integer
Dim GenericListindex As Integer
Dim StatusString As String
If Not (UpdatingList) Then
Dim departmentstring As String
'Ensure InventoryListbox2 contains list items
If InventoryListbox2.ListCount >= 1 Then
'If no selection, choose last list item.
Inventorynumber = InventoryListbox2.Listindex
If Inventorynumber = -1 Then
.DescriptionTextBox.Text = ""
.SerialNumber.Text = ""
.AssetNumberTextbox.Text = ""
.InvDeparmentCmboBox.Listindex = -1
.StatusCombobox.Listindex = -1
.NotesTextbox.Text = ""
Else
'' Item Asset No Serial Number Department MAC Address
Machine Name Status notes
.DescriptionTextBox.Text =
..InventoryListbox2.List(Inventorynumber, 0)
.SerialNumber.Text = .InventoryListbox2.List(Inventorynumber, 2)
.AssetNumberTextbox.Text =
..InventoryListbox2.List(Inventorynumber, 1)
.machinenametextbox.Text =
..InventoryListbox2.List(Inventorynumber, 5)
.MACTEXTBOX.Text = .InventoryListbox2.List(Inventorynumber, 4)
.NotesTextbox.Text = .InventoryListbox2.List(Inventorynumber, 7)
.SerialNumber.Enabled = False
.AssetNumberTextbox.Enabled = False
.MACTEXTBOX.Enabled = False
.DescriptionTextBox.Enabled = False

'' Populates the correct string in the combox box
GenericListindex = 0
departmentstring = .InventoryListbox2.List(Inventorynumber, 3)
For Each Department In .InvDeparmentCmboBox.List
If Department = departmentstring Then
.InvDeparmentCmboBox.Listindex = GenericListindex
End If
GenericListindex = GenericListindex + 1
Next

'' Populates the correct string in the combox box
GenericListindex = 0
StatusString = .InventoryListbox2.List(Inventorynumber, 6)
For Each invstatus In .StatusCombobox.List
If invstatus = StatusString Then
.StatusCombobox.Listindex = GenericListindex
End If
GenericListindex = GenericListindex + 1
Next
End If
Else

End If
End If
End With
End If

End Sub