Forum Discussion
Combining the power of goal seek and scenario analysis in a VBA Userform
I’m building a userform (and a module) for a more flexible Goal Seek. I’ve had recurring uses for something like this at work, where I need Goal Seek’s functionality mixed in with some “what if analysis” sensitivity table power.
I've included a screenshot of the Excel sheet with a simple business case model to illustrate the desired functionality.
Creating the format for the userform was pretty straightforward (screenshot below). However, now that I’m starting to get into the events, things are getting a bit more complicated.
When considering errors that might arise from user input, I think I’ll need to address two things.
- Did the user provide enough information?
- Did they provide the right information?
I’m trying to solve part of the “Did the user provide enough information” problem.
For instance, let’s say a user is trying to set the To value[s] section. This section must be
- RefEdit.value must be not blank
- Range(RefEdit.value) is a single numerical value or range of numerical values
Thus, the goal of this event code is to evaluate the contents of the refEdit object for every entry. If they do not meet the two conditions above, the focus is fixed on that refEdit box until the user enters a valid range reference.
How do I structure the event code so that whenever the value in the refEdit object changes, its contents are evaluated? Whenever I execute the current subroutine, the userform module ends without warning.
- The Exit event does not allow setting focus, best use the Change event.
- Can you provide the actual Excel file with the code? I'm not too happy to invest time in duplicating your setup from a couple of screenshots and I bet neither are others who might be able to help.
JKPieterse Sorry for my delayed response. I took some time to try out a few of your ideas to get rid of the message boxes and refactor the code a bit. I've attached the working file to this message. Since my origional message I've taken a new approach. Each refEdit control will have a series of data validations run upon exiting the control. The hope was that by putting controlName.SetFocus in the sub controlName_Exit(), the user would not be able to hit return or tab to move to the next control until a valid entry is given. However, this does not work, and you can try the mechanics for yourself. I currently have only built out the refEdit control for setCell, but hopefully the direction of the program is clear.
- The Exit event does not allow setting focus, best use the Change event.
- One comment on your code: Don't interrupt the user flow with message boxes appearing on every change. I suggest to place a (red) label on the form that shows the warning.
