Forum Discussion
Vba code help
Cleaning up the code is often a matter of removing "select" and "activate" and joining whatever was selected/activated with the action. Instead of
Range("A1").Select
Selection.Value = 10
it can be shortened to just
Range("A1").Value = 10
Just my two cents, but since these two procedures have a lot of overlap, you could set up a third procedure to do the work and just pass it the value by which to increase/decrease the cell. And, if you had cells other than F8 that this process worked on, you could pass it the applicable cell(s) on the Parts List worksheet.
Or, you could have the user select the cells they want to increment/decrement and have the macro work on whatever range the user has selected?
Public Sub MinusOnePartClickButtonTest() ChangePartCount -1 End Sub Public Sub AddOnePartClickButtonTest() ChangePartCount 1 End Sub Public Sub ChangePartCount(changeValue As Integer) Dim destWkSht As Worksheet Dim destCell As Range Dim timeStampCell As Range On Error GoTo ErrHandler If changeValue < 0 Then Set destWkSht = Sheets("Parts Removed") ElseIf changeValue > 0 Then Set destWkSht = Sheets("Parts Added") Else Exit Sub End If With destWkSht Set destCell = .Range("A" & .Rows.Count).End(xlUp).Offset(1) Set timeStampCell = Intersect(.Range("O:O").EntireColumn, destCell.EntireRow) End With With ThisWorkbook.Worksheets("Parts List") .Unprotect ("Password") ' Unlock Sheet .Range("F8").Value = .Range("F8").Value + changeValue .Range("A8,F8").Copy destCell .Protect ("Password") ' Lock Sheet End With timeStampCell.Value = Now Application.CutCopyMode = False ExitProc: Set destCell = Nothing Set destWkSht = Nothing Exit Sub ErrHandler: MsgBox "Error " & Err.Number & ": " & Err.Description Resume ExitProc End Sub
Yes everything that you’ve, done is exactly what I was hoping to achieve when I started out, and it works flawlessly on your file.
I’ve been trying from when you first sent the updated version to get it to work on the original which is on my company’s internal network (visible to everyone when they log in) but I can’t get it to work/run. The only Vba code I’ve got on the original file is your Vba and it’s on sheet1 just like your file. I started by firstly deleting everything to do with Vba from the original. After copying and pasting the new header names and layout from your to the original ( so they now look identical), I then copied and pasted the first 2 rows from yours to the original that then gave me the buttons and title, I then opened Visual Basic and made sure that there was no modules or anything that shouldn’t be there then is pasted your code to sheet1 of the Visual Basic. So now when I’ve got your version open on my laptop ant the original open on my work computer the only difference is that the original has a further 16 sheets ( a picture catalogue Of all the parts) but these sheets are not nothing to do with the Vba code so should have no bearing on the outcome of any of the Vba code running ( or so I think).
If I open design mode, right click on either the increment or decrement buttons in the original then click view code it takes me to ( Const procName As String = “ChangeStockButton_Click”). I get the same outcome on your version. If I do the same for the run parts reorder report button I get the same outcome but just with the destination (Const procName As String = “RunPartsReorderReport”) on both your version and the original.
If I click on the run parts reorder report button on your version (magic happens and the results populate to the correct sheet) if I do the same on the original I get an error (object variable or with block variable not set). If I go to the Visual Basic and position the cursor to the left of Public Sub ( for RunPartsReorderReport) click play I get (Run_time error ‘91’: Object variable or with block variable not set)
When I try the increment and decrement buttons on the original I get ( Error: 1004: Method ‘Range’ of object ’_Worksheet’ failed) when I go through the Visual Basic play method I get ( Error: 1004: Method ‘Range’ of object ’_Worksheet’ failed) but when I try both method on your version (magic everything works). So my guess is that again it’s my fault, I’ve definitely done something wrong that your Vba doesn’t like.
How would be the best way to correct these issues?
I should be able to figure out what it doesn't like and then help with some instructions to fix the original file.
- 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? You're welcome. Glad to hear that fixed it.
- 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 - 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. - 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 - 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. Yes. I modified the Reorder Parts and Change Inventory macros to also unlock/relock the destination worksheets. You should be able to copy/paste this revised code from the Parts List module into your workbook.
I'm assuming the worksheets all use the same password, which you may have noticed is defined by the constant at the top of the module (PWord). I think it is currently set to vbnullstring, but change that to whatever you need (enclosed in quotes, Const PWord As String = "MyPassword")
Make a copy/backup of your file, just in case.
- Sorry me again, hopefully this is the last issue, I was going through the workbook with the boss showing him all your hard work and come across a wee hiccup. Because I need the sheets locked to stop to stop them from being changed. I had locked them all and when I click any of the buttons the code runs but will not paste to the destinations unless they are unlocked, I dose tell you that in a message window though. But the stock still increases or decreases without the paste, the reorder report runs (I think) But again no paste unless the reorder sheet is unlocked. I know that makes sense, But is there a way to unlock the destination sheets and relight them again within the same code
- Thank you is plenty. I'm glad to see you got it working.
- Your fantastic, everything is working correctly now 🙂
I can't thankyou enough,
Is there anything I can do to say thankyou? Yes. I believe all you need to do is name the cells in your header row (row 4) in the name manager.
If you look at my file, you'll see that I named all of the cells in Row 4 (click on a header cell in row 4 and you'll see the name in the name box). And if you look in the vba code near the top of the module, you will see a list of Private Const xxxxxxRngName = "xxxxxxxx".
But, I don't believe you will actually need all of them (I named the cells up front before writing any code, but it turns out I didn't actually use all of them). You will only actually need to name columns M through R.
I attached a screenshot of the six you need. To add the names on the excel worksheet side (walk you through the first one for the Min Stock column)
1) click on the Parts List tab
2) click on Formulas\Name Manager
3) select New
4) Name (see screenshot - the name I used will be on the right of the = sign): Stock_Min
Scope: Parts List
Refers to: ='Parts List'!$M$4 (or click on the little arrow button to right of the box and select cell M4).
5) click OKThen, repeat for the other five.
here is a copy of the workbook, good luck, I think I was just being over cautious last time, as my company is really strict about the use of their computers, with the risk of viruses etc. so is there ant way for me to be able to update the original workbook. with the repaired workbook from yourself without the possibility of contaminating the original/ works company network?
