Look closely at what you’re trying to do in this code: You’re assigning a “long” value to each of l and m and then calling them in the average function as if they were references to a cell. I suggest that you add a range variable, assign the location of the data you want to … Read more
Google is full of information on this. As Hans Passant said, Form controls are built in to Excel whereas ActiveX controls are loaded separately. Generally you’ll use Forms controls, they’re simpler. ActiveX controls allow for more flexible design and should be used when the job just can’t be done with a basic Forms control. Many user’s computers by default won’t trust ActiveX, and it will be disabled; … Read more
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
Excel (and many other MS related apps) default to 96 pixels per inch. So your 103 pixel wide Excel column should be: 103 / 96 = 1.073 inches (Rounded to thousands.) Of course many printing options will change this, such as “Fit on one page.”
Yes. Or, if your Excel version is old enough,
“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
This is an Array Formula. After entering the formula, press CTRL + Shift + Enter to have Excel evaluate it as an Array Formula. This returns the first nonblank value of the given range of cells. For your example, the formula is entered in the column with the header “a”