VB Code help to change/update existing code

**JDobbsy1987** · 10-17-2013, 05:28 PM

Hi,

Thank you for all the help John (jaslake)

Further from my previous post (http://www.excelforum.com/excel-prog...ate-stock.html) there are a few changes and updates required to fix a design floor in the system (the code worked perfectly though).

Let me try to explain as plain and simple as i can.
The following headers should make sense once you open/see the sheet.

There are 2 stages/buttons...

Stock
When clicking the yellow yes button in the 'stock' section i would like the following to happen:
I have stated specific cells below however these would need to relate to the line with the button that is pressed.

1. Update C4 to minus amount of ordered stock in E4 (this is currently a formula but i think it will need to be a value as we will delete the data in E4 in the below steps)
Basically, we don't want to update B4 just yet as it is still in the building, we just want to show the amount of stock that isn't actually committed to an order effectively making it available to use.
2. Copy A4 to I4
3. Move D4 to J4
4. Move E4 to K4
5. Add current date/time to H4
6. Data sort H to L by Date (most recent at top, oldest at bottom)

Current Orders
When clicking the yellow yes button in the 'current orders' section i would like the following to happen:
I have stated specific cells below however these would need to relate to the line with the button that is pressed.

1. Update B4 to minus K4 (we now want to update B4 as the stock is completed/left the building)
2. Move H4:L4 to O4:S4
3. Data sort O to S by Date (most recent at top, oldest at bottom)
4. Now re-data sort H to L by Date (most recent at top, oldest at bottom)

I have attached the sheet.

Thank you,
Jamie

**jaslake** · 10-19-2013, 11:48 AM

Hi Jamie

Try the Code in the attached...

**jaslake** · 10-19-2013, 11:53 AM

Hi Jamie

Try the Code in the attached...

**JDobbsy1987** · 10-22-2013, 02:53 PM

Hi John,

Sorry, i missed this reply... thank you for helping again, i have had a play with it and there are a few things if you don't mind assisting.

'Stock' Yellow Button:
Once i have processed an order (press the yellow button) under stock, i would like to be able to process another order too as we have multiple orders for the same stock from different customers.
At the moment it doesn't let me unless i click the yellow yes button under 'Current Orders' as i get a message saying this order has been processed.

Also, upon pressing the yellow button i would also like it to clear D and E columns.

'Current Orders' Yellow Button:
When clicking the yellow button, it copies the data from Current Orders to Completed Orders but doesn't remove it from the current orders list.
Is it possible to update this?

Regards,
Jamie

**jaslake** · 10-23-2013, 09:34 AM

Hi Jamie

Try the Code in the attached...

**JDobbsy1987** · 10-23-2013, 12:29 PM

Almost there

Just one minor change... well i hope it's minor change, if not then the sheet will do fine, you have done an awesome job.

When i enter a number in E i can see C updates to reflect the available stock, when i click the yellow button under 'Stock' i don't want it to update B at this point as it currently does
Basically, we don't want to update B just yet as the stock is still in the building, we only want to update B4 when we complete the order (i.e. clicking the yellow button under 'Current Orders')

This way, column B reflects the amount of stock in the building and column C reflects the amount of stock that is available to use.

Is this possible?

Regards,
Jamie

**jaslake** · 10-23-2013, 03:53 PM

Hi Jamie

Well, it wasn't a minor change but I think the attached does as you require. Let me know of issues.

**JDobbsy1987** · 10-23-2013, 04:05 PM

Thank you so much, there does seem to be an issue with ordering more than 1 of the same product.

Example:
Add 2 orders for Product 1...

When you add the 2nd order you will see the available QTY doesn't update... this means when you press the yellow button under current orders you end up with more available than the actual QTY in the building.

I hope that makes sense.

Regards,
Jamie

**jaslake** · 10-23-2013, 04:58 PM

Hi Jamie

Sorry about that.

In the Worksheet Change Event Code change the indicated Line.

Please Login or Register  to view this content.

**JDobbsy1987** · 10-23-2013, 05:04 PM

The sheet works perfectly!
Thank you so much.

Although i can't rep you... here is a token gesture of 1 million rep points

Thank you!

Regards,
Jamie

**jaslake** · 10-23-2013, 05:20 PM

You're welcome...glad I could help. Thanks for the "pseudo" Rep.

**jaslake** · 10-24-2013, 10:22 AM

Hi Jamie

Pursuant to you're PM request, replace the CurrentOrders Code with this

Please Login or Register  to view this content.

The ChangeEvent Code behind Sheets THEORY should be entirely commented out or removed.

**JDobbsy1987** · 10-24-2013, 10:32 AM

Perfect... thank you ever so much!

Cheers,
Jamie

**jaslake** · 10-24-2013, 04:46 PM

You're welcome...glad I could help.

VB Code help to change/update existing code

LinkBack

Thread Tools

Rate This Thread

Display

VB Code help to change/update existing code

Re: VB Code help to change/update existing code

Re: VB Code help to change/update existing code

Re: VB Code help to change/update existing code

Re: VB Code help to change/update existing code

Re: VB Code help to change/update existing code

Re: VB Code help to change/update existing code

Re: VB Code help to change/update existing code

Re: VB Code help to change/update existing code

Re: VB Code help to change/update existing code

Re: VB Code help to change/update existing code

Re: VB Code help to change/update existing code

Re: VB Code help to change/update existing code

Re: VB Code help to change/update existing code

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] have code need user form to automatically update existing information

Change the comments box colour in existing code

How to change existing code that highlights duplicates?

VBA code to change registry value of existing DSN

VB code to update existing Pivot Table

Bookmarks

Bookmarks

Posting Permissions