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
Thanks again you great
I see that you have increment/decrement buttons in row 5, columns O and P. Are these buttons dedicated to row 5 (i.e. - in your master version, do you have buttons for each row that increment/decrement the current stock level only for the item in that specific row)? I guessing that is the case based on your original post regarding having 4k buttons for 2k parts.
If so, I would agree with Riny that seems pretty overwhelming. Particularly when you consider making updates (adding parts or removing discontinued parts), as each button is hardcoded to a specific row (so, if you insert/delete rows, you have to update the vba code for all of the cells that got moved up/down and delete/add buttons and their respective vba code).
If you are open to suggestions, I would just have 2 buttons (increment/decrement) above the column that change the stock quantity in the same row as the active cell. So, the user selects the row they want to change, then clicks the button, and the stock quantity changes in column Q of that same row. Then you are free to update your table w/o having to update any of your increment/decrement code.
- 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?
- Is it possible that you can upload a copy of the workbook that is misbehaving (you can remove the worksheets that comprise the picture catalog, anything that is sensitive, or even most/all of the parts list data if you need to)?
I should be able to figure out what it doesn't like and then help with some instructions to fix the original file. - Hi
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? Take a look at the file and see if that is what you have in mind. I added a worksheet with comments to provide some additional explanation of what I did. Hopefully, it makes sense.
Excel has quite a few ways to refer to worksheet ranges, instead of using their cell address, that can help make the vba procedures less susceptible to breaking because a user (or you) moved things around (like cut/paste the table fields because you want them to appear in a different order, or inserting rows above your header row, or deleting some rows/columns - depending on what's deleted).
See if the attached workbook does what you are looking to do.
I named the cells in the last row of the table header. Instead of referring to a range by it's address, Range("Q4"), you can refer to it also by it's name (if it is named), such as Range("Current_Stock") if cell Q4 is named "Current_Stock". The advantage is that if you modify your table (insert rows/columns or cut/paste columns so they are in a different order), your code can still find the named range whereas Range("Q4") will always refer to Q4 (it doesn't realize Q4 was moved because you inserted/deleted a row/column above or to the left of it).
I also set up some constants in the code module that refer to the names of the header cells. So, if you change the names of the cells, you only have to update the constants that refer to them.
I added two forms buttons (not activex buttons), which are named "DecrementButton" and "IncrementButton" at the top of the current stock column. Both of these buttons are assigned to the macro "ChangeStockButton_Click." The macro will check the name of the button that called it and increment/decrement the current stock in the same row as the active cell (you don't have to actually be in column Q, just on the same row).
Then, "ChangeStockButton_Click" will call "CopyInvChange," which will copy the row of the item that was changed (excluding the min, initial, removed, and added fields) to the "Parts Added" or "Parts Removed" worksheets.
Note that worksheets actually have two names - tab name and code name. If you look at the worksheets listed in vba's project explorer window (view\project explorer), the code name is the first name listed and the tab name is in parentheses. Instead of Worksheets("Parts List").Range("A1"), you could use Sheet1.Range("A1"). That way, when users change the worksheet tab name, your code is not affected.
Excel has quite a few tools for referring or navigating worksheet ranges. As much as possible, I try to avoid referring to ranges by their actual cell address so your macros are more flexible to changes made to the worksheet. These are a few that I used:
CurrentRegion - refers to a region, such as a table. In this file, refers to A1:S209
UsedRange - refers to a worksheets "used range." Instead of looping through all of the cells in the entire column, you can limit your search to the used range (no point in checking all of the empty cells in the unused range).
Intersect - returns the range/cells in common between two other ranges. Intersect(cell1.entirerow, cell2.entirecolumn) is one way to find the cell on the same row, but in another column.
Union - merges two or more ranges together (not on the worksheet itself, but your vba range variable).
- Yes that’s correct, I’ve 2 buttons in every row that control the stock level cell then copy the row to the appropriate sheet ie. -1 button go to parts removed sheet, and +1 button go to parts added. It was a nightmare creating all the buttons, but that’s all I knew at the time.
Your suggestion sounds perfect, but I wouldn’t even know where to start, would it be able when the cell is selected and the correct button clicked will the stock level change up or down depending on which button is pressed. Then after the level changes the row is copied the to appropriate sheet and time stamp the new row in the new sheet also? If all that is possible great, will this code still allow the run parts reorder report to work,
This sound like a lot of work for yourself on my behalf.
Any help any greatly appreciated, it’s nice to be able to speak with Vba guru, my google searches only throw back the same things but described differently.
Thanks again for all you help
