HELP! Userform VLOOKUP , RESET and Add/Remove

[SikPup]

Hi All,

I joined a few nights ago and have been researching on the forums trying to solve my problem myself, only to confuse myself even further. I would love one of you pro's to give me 10 mins and a bit of guidance !!!

I am building a very simple 1 sheet workbook called 'data' that will act as a stock in/out inventory control. Very basic, Column A is Part Number, Column B is Quantity and Column C is Location.

I have created 2 userforms that toggle between each other with the main sheet non-visible. All good so far...

The main userform 'frmInventory' has a a combobox that I think I have got right. It uses part numbers form Column A as a reference when typing input. I then need the quantity and location to be population when the search key is pressed (or user clicks out of combobox it seems)?

Once the partnumber has a match, the quantity can be shown and/or adjusted either by ADDING or SUBTRACTING from the existing stock row in sheet1 using the 'add to stock' or 'remove from stock' buttons some sort of message confirming the action.

If the partnumber is not found, the user must use frm.newitem to add a new partnumber to the sheet1("data") Column A..

Then there's the reset buttons that I had a .clearcontents on but that didnt work either...

I have included the file and removed most of my bad code to simplify things..

PLEASE HELP!! THANK YOU ALL!

Regards,
Vaughan

[Jack7774]

#1 problem is that when you open that file you uploaded it closes excels visibility. Maybe someone else can fix that without closing your file but I can't. Please remove the code you have upon open of the file and I can take a look at it.

[jonathann3891]

See if this will get you pointed in the right direction.

I couldn't get the add/delete function to work properly.

I centered all your dialog boxes on top of excel. I personally don't like when they open off screen.

Source Data.xlsm

[Jack7774]

Okay you said the following but isn't making any sense to me, "I then need the quantity and location to be population when the search key is pressed (or user clicks out of combobox it seems)?" What do you mean you need the quantity and location to be population?

So I'm trying to understand the big picture here, you want the user to see inventory form when they open this file, then they choose from the drop down list not type the part number in to see the current status of the stock or inventory then they can add or remove from stock or if they can't find it then they would click new item to add the new part. If they want to add to stock they select the part number and the current stock will pop up with the location it is at and quantity it currently has. They would then type in a number that either was purchased or bought and click the button add or remove depending on which. It would then add or remove the number they typed from the number previously shown? Also when you say clearcontents I know your referred to code but what exactly does the user need to clear contents of the boxes for? Can't the user just choose another part number once they desire to update another part?

[Jack7774]

You may want to consider adding an update button rather than just an add and remove button. Also something else to consider does the same part number exist at different locations. If so then you will need to search by part and location number not just the part number when finding the part and location number you want to add or remove or update.

[SikPup]

Here is the file again with the hide feature deactivated.

Yes the part number is the searchable item, with the Quantity being calculated from all locations...


"I then need the quantity and location to be population when the search key is pressed (or user clicks out of combobox it seems)?" What do you mean you need the quantity and location to be POPULATED ie> vlookup from matching part number

[jonathann3891]

I attached a workbook that will populate the quantity and location once the part number is selected.

[Jack7774]

I reactivated your hide feature in the workbook I uploaded and fixed it so that you could click hide and it would reshow the workbook. But could you answer these questions.

It would then add or remove the number they typed from the number previously shown?

Also when you say clearcontents I know your referred to code but what exactly does the user need to clear contents of the boxes for?

Can't the user just choose another part number once they desire to update another part?

[SikPup]

Hi Jonahann3891

WOW Thanks, that really helped a lot.

I'm so close to completion its not funny...

I just need to find a way stop the error messages if someone enters a partnumber that does not exist or they mistype. I assume some form of IFERROR code is needed?

Also I added 2 new forms, add & remove...

I am having trouble entering the location data from all the userforms to sheet1

I have reuploaded the file as vers 3. http://www.filedropper.com/sourcedata3 (couldn't upload to this site for some reason)

Many thanks again..

[SikPup]

Replied my many thanks!! PLease help just a little more

[SikPup]

Hi again,

Firstly thanks to all for helping. It's really appreciated.

I have just one line of code to fix then I'm done!!!


Private Sub CommandButton1_Click()
With Sheet1
.Cells(Me.cboPartNumber.ListIndex + 1, 2).Value = .Cells(Me.cboPartNumber.ListIndex + 1, 2).Value + Val(Me.txtAddQty.Value)

.Cells(Me.cboPartNumber.ListIndex + 1, 3).Value = .Cells(Me.cboPartNumber.ListIndex + 1, 3).Me.txtLocationAdd.Value

End With
Me.TxtQty.Value = Empty
Me.txtAddQty.Value = Empty
frmAdd.Hide
End Sub

This line is trying to pass a text/number combo to Column 3... I am getting a mismatch error. Also, If there is text in the cell already how can this overwrite that?

Thanks again !!!

[bakerman2]

Please Login or Register  to view this content.

[Jack7774]

Sweet everyone. Adding rep to all because its when everyone pitches in we ultimately meet the goal of this website to help people and that's what its all about.

[SikPup]

Sweet everyone. Adding rep to all because its when everyone pitches in we ultimately meet the goal of this website to help people and that's what its all about.

Just wanted to say a big thanks again to all who helped. Could not have done this myself so great team effort. Thanks Vaughan.