PDA

View Full Version : How to solve my combobox bug in Excel-Vba?



imso
08-07-2011, 06:44 AM
Hi Guys,

Rather new in Excel-Vba so please so easy on me ok.

Brief description: I'm somehow creating a combobox to keep track of the quantity of the item in a lab kept in the drawers. Something like a 'Inventory Tracker'.

Problem 1: It works initially to transfer items from drawer to drawer, but it will hit an error when the item's quantity goes to zero which i don't want it to? Error message --> (Run-time error '381' Could not get the list property. Invalid property array index.) Which ideally that particular item should be deleted and the space filled up with the rows underneath since its considered to be 'nothing' in the drawers.

Problem 2: I also wanted to include the MHSADxx on the right to be included in the selection process but was left out..
I know it got to do with this line here but rather i do not really know how to modify it to include the MHSAD Set tmp = Range("B9:B196").Find("*")

Sorry if my explanation was kinda bad. Attachments below, many thanks for your time and help :)

Bob Phillips
08-07-2011, 07:30 AM
When I try this, it clears the value from where it moved it to, not where it moved it from. Does this happen for you?

imso
08-07-2011, 07:11 PM
Hi xld,

Thanks for answering. I don't quite get what you mean? Its deduct the value of the quantity from where the item comes from, and move the desired quantity value to the designated drawers. But that function works fine, it will only encounter problem when the value is deducted to 0 from where it came from.. Any ideas how can i solve this?

But by the way is there a way i can encompass the MHSAD01, MHSAD02, MHSAD03, MHSAD04, MHSAD05 and MHSAD08 on the right hand side of the sheet into the dropdown menu of the userform? What are the codes i should type to solve Problem 2?

Bob Phillips
08-08-2011, 12:43 AM
It doesn't work in my test. I moved the first item from drawer 1 to drawer 3, and it deducted the amount from drawer 3 (not 1).

imso
08-08-2011, 02:12 AM
I know there's bug in my code, but i do not know really how to solve it.. As its suppose to deduct the original quantity depending on user specification and transfer the value to the destination drawer. Can you help?

imso
08-08-2011, 08:22 AM
The quantity functionality don't seems to work by deducting the source the increment the destination location(Stays at 0 all the time)... Any ideas how can i solve this problem?

imso
08-08-2011, 11:06 AM
Hi xld,

The deduction issue has been solved, but do you have any idea on how i can incorporate a update feature of the date to current date when it spotted a transference of items between a pair of drawers?

And the incorporation of the MHSADxx drawers into the dropdown menu (From and To) on the right hand side of the sheet with my current set of codes? Thanks for your time?

Regards,
imso

Rayman
08-08-2011, 02:39 PM
For problem one, try this (modification of your code)
Private Sub CommandButton1_Click()
Dim quantity As Integer
Dim searchArea, tmp, rng As Range
Dim endRow, diff As Integer

If ComboBox1.value = ComboBox2.value Then Exit Sub

Set tmp = Range("B9:B196").Find(ComboBox2.value)
endRow = tmp.Row + tmp.MergeArea.Count
Set searchArea = Range("C" & tmp.Row, "C" & endRow - 1)
Set rng = searchArea.Find(ComboBox3.value)
If rng Is Nothing Then
Set rng = searchArea.Find(what:=vbNullString, lookat:=xlWhole)
If rng Is Nothing Then
Range("A" & tmp.Row, "B" & tmp.Row).UnMerge
Range("A" & endRow, "D" & endRow).Insert (xlDown)
Set rng = Range("C" & endRow)
Range("B" & tmp.Row, "B" & endRow).Merge
Range("A" & tmp.Row, "A" & endRow).Merge
End If
With rng
.value = ComboBox3.value
.Offset(0, 1).value = ComboBox4.value
End With
Else


On Error GoTo GestErr

rng.Offset(0, 1).value = CInt(rng.Offset(0, 1).value) + CInt(ComboBox4.value)
End If

Set tmp = Range("B9:B196").Find(ComboBox1.value)
Set rng = Range("C" & tmp.Row, "C" & tmp.Row + tmp.MergeArea.Count - 1).Find(ComboBox3.value)

GestErr:
If Err.Number = 13 Then
MsgBox " Quantity = 0!", vbCritical
Exit Sub
End If


diff = CInt(rng.Offset(0, 1).value) - CInt(ComboBox4.value)
If diff = 0 Then
rng.ClearContents
rng.Offset(0, 1).ClearContents
ComboBox1_Change
Else
rng.Offset(0, 1).value = CInt(rng.Offset(0, 1).value) - CInt(ComboBox4.value)
ComboBox3_Change
End If
End Sub

and this:
Private Sub ComboBox3_Change()
Dim tmp, rng As Range
Dim quantity, i As Integer

If ComboBox3.ListCount = 0 Then Exit Sub

Set tmp = Range("B9:B196").Find(ComboBox1.value)
Set rng = Range("C" & tmp.Row, "C" & tmp.Row + tmp.MergeArea.Count - 1)
Set tmp = rng.Find(ComboBox3.value)
quantity = CInt(tmp.Offset(0, 1).value)
ComboBox4.Clear
For i = 1 To quantity
ComboBox4.AddItem i
Next

If quantity > 0 Then

ComboBox4.value = ComboBox4.List(0)
End If
End Sub