RefEdit Entries
Recently I've been working on creating a ribbon tab for Excel 2007 that interfaces with and controls several of the add-ins that I use for work. The Goal Seek Tool is one of those add-ins. I created this tool to allow the entry of a single-cell range or value when performing the goal-seek function.
In November 2008 I wrote a short post about my Goal Seek tool. In my first version of that tool, I used a RefEdit control to enter a range and a textbox to enter a value. In a comment to that post, Jon Peltier noted that the textbox could be eliminated and the RefEdit control could be programmed to accept either a range or text entry.
After a lot of time searching the internet, I finally found an example of how this functionality could work on the TM Consulting site. It looks like this:
When wanting to enter a range with RefEdit and assuming the RefEdit control is named "RefEdit1", use
Range(RefEdit1)
When wanting to enter a value with RefEdit, use
Range(RefEdit1).value
When wanting to validate the entries made into RefEdit, you can use examples like:
To validate that a range contains a formula, use
HasFormula(Range(RefEdit1))
To validate that a range contains a number, use
IsNumeric(RefEdit1)
