automatic erasing of field entry and date entry

**roadkill** · 06-21-2008, 05:36 PM

Dear friends,

I have a problem and i am hoping that someone can help me out.

I have created a small worksheet (tnx to the internet) for keeping our inventory.
(we don't have acces at work)

It is simple : productcode / productname / delivery (IN) / sending (OUT) / Inventory

When the product are at theire min. stock amount the vield changes color so that we know that we have to reorder that product.

Problem that i have is that the cels for IN and OUT are not deleting after input but keep on counting, this could cause some mistakes.

I whas wondering if it is possible that after you enter date that field go's blanc again?

I also have 2 date cels zo that we can see when that product whas last deliverd or went out the inventory, again is there a way to put this date automatic after entering data in the IN or OUT cel?

alleen is het zo dat de velden geleverd en uit stock zich continu optellen, en dit kan voor verwarring gaan zorgen.

I know these are strange questions and i have to admit that i don't have a qlue how to use, write or implement a VBA code, so bare with me and sorry for my poor writing, but English is not my first Language (it is Dutch)

so is the attrached document

Tnx

**WinteE** · 06-22-2008, 04:43 AM

Hi Roadkill,

First of all, my native language is also Dutch. Therefore the parts of this message will be in Dutch and English to make it more clear to you.

EN :
Clearing the contents of in- and outgoing stock won't calculate the right closing stock. This is because you are calculating the closing stock based on a fixed opening stock, so you always have to keep former in- and outgoing stock in your calculation.

NL :
Het opschonen van de in- en uitgaande voorraad geeft een foutieve eindvoorraad omdat je eindvoorraad berekent wordt op basis van een vaste beginvoorraad. Je zult op deze manier altijd voorgaande voorraadbewegingen vanaf de datum 'opname-stock' in de berekening mee moeten nemen.

EN :
Make it clear to yourself how you want to calculate the stock, as it is correct calculated right now.

NL :
Probeer eerst duidelijk te krijgen hoe je de voorraad precies wilt berekenen, het is nu namelijk een correcte berekening.

EN :
I have added some VBA to your file to fill the dates of movements automatically once you change in- or outgoing stock.

NL :
Ik heb een stukje VBA toegevoegd aan je bestand. Dit zorgt ervoor dat zodra je in- of uitgaande voorraad boekt de datum van verwerking automatisch wordt aangepast.

EN :
Once you know how you want to calculate the stock, and you like to have it different the way it calculates now, than let me know.

NL :
Als je er uit bent hoe je de voorraad exact wilt berekenen, en het moet anders dan het nu is, laat het me dan weten.

Regards,

Erik

**roadkill** · 06-22-2008, 09:16 AM

Erik,

tnx for the reply,

you are correct that the iventory is based on a fixed opening stock, the next complete inventory wil be taken begin August, which wil be the starting stock.

because of this the content does not need to be cleaned but it would be grat to see a "last" entry date so to minimize mistakes.

tnx for the VBA code, but is there in error in there?

i get a error saying that i = is not a validate entry

i = Cells(Cells.Rows.Count, "A").End(xlUp).Row

If Not Intersect(Target, Range("E6:E" & i)) Is Nothing Then
Target.Offset(0, -1).Value = Now
End If

If Not Intersect(Target, Range("F6:F" & i)) Is Nothing Then
Target.Offset(0, 1).Value = Now
End If

**WinteE** · 06-22-2008, 10:00 AM

You're right roadkill, sorry. I added this peace of code without testing it.

Change the code to :

Please Login or Register  to view this content.

Added code is shown red.

**roadkill** · 06-24-2008, 03:06 AM

Erik,

tnx for the correction, it is working fine now

OK, so we are starting to test it and even if excel is not the best choice to do this we can use it until are ERP software is upgraded with a inventory module (mid 2009)

just a few extra questions and if you could help (or anybody else) that would be great.

when you want to put data in a field in column E or F then you get a pop-up input-box.

in that box there is a text "Vulg hoeveelheid in ..." (enter quantity ...)

is there a way to give the box in E and F a different text?
like E : Vul GELEVERDE hoeveelheid in ...
F: Vul hoeveelheid UIT STOCK in ...

and now for something completely stupid!

the pop-up input boxes for E and F are both Grey, for minimizing errors it would be neat if these could have a different color (E = Green, F = Red)

so the person who is inputting the date sees in a instant that he is working in the correct field.

Oh, one problem that i encountered (but probably is not solvable) is that is for example you want to put in a amount in a cell in column F (OUT) then you get your pop-up window and can input your amount (perfect), if you press enter or OK then the pop-up disappears and you are still in the cell.
now you can change the value of the cell without the pop-up, screwing the amount up!

is there a way to secure columns E and F that you can only enter data using the pop-up input screen??

Yep a lot of questions here, but still i hope that anybody can help me

personally, i can't understand why it is so hard for me to understand VBA and macro's? but that will be me i guess

Ronny

**WinteE** · 06-24-2008, 06:45 AM

Hi Ronny,

Here's the answer to almost all your questions :

Please Login or Register  to view this content.

Replace the existing 'SelectionChange' code with the code above.

Changing the color of an inputbox isn't possible for as far as I know.

Erik

**roadkill** · 06-24-2008, 06:53 AM

Erik,

you are a genius,

tnx m8 it works perfectly

**roadkill** · 06-24-2008, 10:20 AM

Because i believe in returning a favor i want to Chere this with the community.

after Erik has send me the last code adjustment we had a visit from our IT service company because of a network glitch

I told him about my (our) excel sheet and how it works.

he sat behind my desk and after e few minutes he added some adjustments to it which i thought are really cool.

first there is now a integrated search button at the top and the pop-up input screens match the color of the column (green for IN, red for OUT)

double clicking on a cell (column E and F) is not working anymore

Because of the help i received i am adding the file in attachment so this may help someone else.

tnx again

automatic erasing of field entry and date entry

LinkBack

Thread Tools

Rate This Thread

Display

automatic erasing of field entry and date entry

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions