You can try this direct VBA approach which doesn’t require HEX editing. It will work for any files (*.xls, *.xlsm, *.xlam …). Tested and works on: Excel 2007Excel 2010Excel 2013 – 32 bit versionExcel 2016 – 32 bit version Looking for 64 bit version? See this answer How it works I will try my best to explain how it works … Read more
Every For statement with a body must have a matching Next, and every If-Then statement with a body must have a matching End If. Example: You have part of the body of your If statement inside your For i loop, and part of it outside. It has to be either ALL inside or ALL outside. … Read more
You can use this password to unlock the SOLVER.XLAM project; Wildebeest!!
Set statements aren’t allowed outside procedures. Move the Set statement into the Formatting procedure: (I’d move the Dim statement into the procedure as well. I prefer to avoid global variables when possible.)
Well I did not think this was possible until I went and checked. In some previous version of Excel I could not do this. I am currently using Excel 2013. This is what you want to do in a scatter plot: right click on your data point select “Format Data Labels” (note you may have … Read more
“Subscript out of range” indicates that you’ve tried to access an element from a collection that doesn’t exist. Is there a “Sheet1” in your workbook? If not, you’ll need to change that to the name of the worksheet you want to protect.
Try double-clicking on the bottom right hand corner of the cell (ie on the box that you would otherwise drag).
If the user clicks Cancel, a zero-length string is returned. You can’t differentiate this from entering an empty string. You can however make your own custom InputBox class… EDIT to properly differentiate between empty string and cancel, according to this answer. Your example Would tell the user they canceled when they delete the default string, … Read more
Some folk dislike this approach because of an “inappropriate” use of error handling, but I think it’s considered acceptable in VBA… An alternative approach is to loop though all the sheets until you find a match.
You need to take out the $ signs before the row numbers in the formula….and the row number used in the formula should correspond to the first row of data, so if you are applying this to the (“applies to”) range $B$2:$B$5 it must be this formula =$B2>$C2 by using that “relative” version rather than … Read more