Vba code help

[StevieGis01]

Hi, I’ve been tasked with creating a parts spreadsheet in work, so for the past six weeks I have itemised over 2000 parts. After completing the data input I’ve set about making it more user friendly, but have been beaten by my limits on vba. I have manage to 3 vba codes to preform different tasks across 4 mains worksheets (parts list, re-order report, parts movement, catalogue). I’ve made over 4000 function buttons to preform 2 different tasks. 2000 increase parts list cells (Q1:Q2000) by +1 if. clicked. The other 2000 buttons decrease the same cells by -1 if clicked. My third code is also a button which runs a re order report by searching for the word YES in cells (R1:R2000) then copying the rows and populating them into the reorder sheet. But while running my spreadsheet within my maintenance team I encountered a problem, I have no record of parts movement. For the past week I’ve tried and tried and searched the internet but have came up short, in my search a and code building. I need a code to Automatically (no buttons) read cells(Q1:Q2000) for any change (these cells are increased and decreased by 1 with each click of the 2000 add buttons and 2000 remove buttons. The code then needs to copy each row in which the Q cell has increased or decreased (cells A to L) and paste them into parts movement sheet starting in row 5 and pasting into cells ( A to L) . The code also needs to allow the pasted rows to stay and to just create another row if the same part has been increased or decreased. I also need the pasted rows in parts movement sheet to be Timestamped this would allow a history for both used and restocked parts.

[#2]

Hi JMB17

It been a few weeks since we last spoke, our workbook is working well, but I’ve noticed a slight problem. Within each worksheet I have auto filters that work fine when the worksheet is locked but when any of the function buttons are clicked (Vba operates as it should) the worksheets unlock then lock again when code it finished running. My issue is after any vba runs and locks the sheet the auto filters no longer work, I have to unlock the sheet reselect auto sort and the lock the sheet again.

[#3]

Hi JMB17

It’s been a wee while from our last chat, the spreadsheet is working great, but there’s a small issue that I’ve noticed, I’ve auto sort function enabled in the protected/ locked sheets. The auto sort works fine on all the locked sheets until any of our VBA buttons are clicked, after the code runs the auto sort stops working until you manually unlock re-enable and then lock again, but again of any buttons is VBA button is clicked then it stops again. I assume this is fixable, but I can’t work out how to rectify it. Any help would be great, thanks in advance

[#4]

Sorry for the late response. I saw the notification, but when I clicked on it, there were no new comments. I only saw this when I went to your profile.

If you locate the lines in the code that are applying protection to the various worksheets, you can modify it to allow filtering by adding a comma and AllowFiltering:=True

For example:
Me.Protect Password:=Pword, AllowFiltering:=True

I believe there are 5 places where it is re-protecting worksheets.

[#5]

Hi JMB17 hope alls well with you and your family.

As always such an easy fix for yourself, that cured it. I feel like a right spanner, I really can’t thank you enough just wish there was a way to repay you for all your time and expertise.

Thanks again
Stevie

[#6]

@StevieGis01 

You're welcome. Glad to hear that fixed it.

[#7]

Hello again JMB17

I hope all is well with you and your family?

It’s been some time from we spoke last, just letting to know that our code is still working like a dream,

It’s working so we’ll my boss wants me to let it run all our parts but he would like to have barcodes for each product, and to be able to scan a barcode to add or minus a product

Is this doable? Was thinking if I can work out how to create a barcode each +1 and each -1 they could be linked to the VBA buttons would this be true?