Inventory help!

[davemackenzie]

Hi Guys,

First post

My story is this:

I'm an IT asset manager in a company that has no official means of stock control, I've only been in the job for all of 3 weeks and have already found faults all over the show that I now want to get back on the straight path.
I'm in need of some help as I have an idea for a basic stock sheet but with the following options if possible?

My idea is this, we currently use a basic spreadsheet across 6 branches (which each branch just updates its stock on the relevant tab) - this is then accessed via the cloud app so that all parties can update as and when, the trouble is, the other branches don't tend to update things as this is the last thing on their mind so I want to get some processes in place for that (at a later date)
What I want to know is, is it possible to setup a spreadsheet of which all branches update their stock but if that stock level goes below the buffer (minimum requirement for work orders) can I setup a macro that will email myself of a pending stock deficit so that I can get an order in place to bump up the said branch's stock levels?

Basically at the moment, the branches email myself at the last minute to say they no longer hold 10 iMacs and they need some within 2 days, whilst if I had a stock sheet setup that had pre-warned me of pending levels then I could have counter-acted such!

Any help is appreciated, I use Excel often but just don't tend to dwelve into the advanced side of things!

 

[mrmmickle1]

davemackenzie,

It is possible to set up what is called an event. What this means is when a certain event occurs like a cell value going below X amount then a procedure is activated. Otherwise, it lies "dormant."
There are many different types of events in MS Excel. Please find an example below.

Here is an example of a Worksheet Change Event:

[COLOR=#0000ff]Private Sub [/COLOR]Worksheet_Change(ByVal Target As Range)
[COLOR=#0000ff]    If[/COLOR] Range("A1") < 10 [COLOR=#0000ff]Then[/COLOR]
        Range("A1") = "TRIGGERED EMAIL"  [COLOR=#ff0000]'<----The Email Code would go here[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

Try this out.

1. Access the VBE (Visual Basic Editor) by pressing Alt + F11
2. Click on the ICON that says Sheet1.
3. Paste the above code in the white screen.
4. Try changing the value of the cell A1 in Sheet1 and see what happens.

Like I said this is possible, but there are several other things that need to be taken into account like your end user's email settings, software versions, etc....(Just to name a few)


Welcome to the board!!!

 

[Ron Coderre]

It seems like MS Access would be a more likely candidate to track assets then Excel.
Try see this website:
MS Access Templates

I did...and thought you might be able to use something like this:
Free Goods Tracking Template | Free Business Database Templates | MS Access Templates

Does that help?

 

[davemackenzie]

Thanks guys, I would love to use Access as I know more about it over the last 20 years - But, the company does not use it (why, I do not know) I can grant the licensing through active directory but I can't justify the purchase of several licences for the sake of it without director's approval (brick wall) I say this because I'm also in charge of software licencing for users across the company!
MrMickle, I was thinking of the same idea but ofcourse we come into issues with SMTP settings etc, although we use a VPN, asking the user to confirm their settings and then email it to me it would just seem long winded. I think the best thing I can do is setup the work book just to email my designated inbox with a list of what's updated as I will then have easier access to the outbox settings for the email side of things! I'm over complicating it to the point that I need to take into account the users on the other side that may not have a technical background in IT infrastructure

 

[mrmmickle1]

davemackenzie,

Look at this thread I answered yesterday it contains an email macro. If you include something like this into the event you should be fine: CLICK HERE

It's not that difficult to set up an email to a predefined address. You would modify this portion of code in the thread link above to something like this:

Change This:

 .To = ""  [COLOR=#008000]'Add To as necessary[/COLOR]

To This:

.To = "davemackenzie@email.com"

You would use an event similar to this:

[COLOR=#0000ff]Private Sub[/COLOR] Worksheet_Change([COLOR=#0000ff]ByVal [/COLOR]Target [COLOR=#0000ff]As[/COLOR] Range)
    [COLOR=#0000ff]If[/COLOR] Range("A1") < 10 T[COLOR=#0000ff]hen[/COLOR]
  [COLOR=#0000ff]       Call [/COLOR]Mail_workbook_Outlook_1[COLOR=#008000] '<----Copy your modification of this Procedure from the other thread into a standard module so you can "Access" It in this Procedure[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

The settings I was referring to were more or less in MS Outlook. If you don't turn off the user's warning settings they will be prompted every time an email is sent.....

 

[davemackenzie]

Thanks MrMickle, The reason I'm guessing I should keep the email side local is because I'm not sure whether the other users are able to do such on their end if I send a request out. The main thing is, I run the stock for the UK branches and need to ascertain whether the other branches need stock, soo far they haven't bothered keeping me up to date so if I can set this as a process then it will hopefully speed things up and make sure we're on top. The only other option is to just give them all an Outlook reminder but that just doesn't seem professional enough considering we have Excel here

 

[mrmmickle1]

Happy Hunting!! Report back to the board if you have issues. I'm sure someone will be able to assist you. I agree Excel is a powerful tool

 

[davemackenzie]

With regard to the macro, would the end user have to run this or can it be set to run automatically as long as the user opens the spreadsheet? I seriously haven't used it in depth in years, from last count there used to be a dialog that asked if the user wanted to run the automated scripts which would then run it through? Automation is key here, I want to make it as painless as possible

 

[mrmmickle1]

You have several options. If I was you I would look into these three options depending on what you want.

1. First I would set conditional formatting in the particular cells that you want to "Manage". So maybe set the formatting to Pink or Red if it was below your defined limit. This would make the low stock easily identifiable to your staff and subsequently to you when you opened the spreadsheet to review why you received an automatic email....

2. I would use one of these types of events depending on your preferences:

Private Sub Workbook_BeforeClose(Cancel [COLOR=#0000ff]As Boolean[/COLOR])
[COLOR=#008000]'Your Code Here[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Private Sub [/COLOR]Workbook_Open()
[COLOR=#008000]'Your Code Here[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Private Sub W[/COLOR]orksheet_Change([COLOR=#0000ff]ByVal[/COLOR] Target [COLOR=#0000ff]As[/COLOR] Range)
[COLOR=#008000]'Your Code Here[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

Here is some general information on Events provided by Chip Pearson. CLICK HERE FOR EVENT INFO


The email message I was referring to looks like this: CLICK HERE FOR PICTURE