Create Function to allow InputBox or Selection
byronwall opened this issue · 11 comments
This is tied to #13 which will create a settings file. It would be good to allow the formatting to be applied to an input or to the Selection.
I guess Application.InputBox takes a parameter Default:= which can be used to start the box with a value. This works nicely for what I want, which is a default option to use Selection. I will create a Function that works on this idea with code like:
Set rng = Application.InputBox("Range:", "Range", Selection.Address, Type:=8)
I will wrap it in a Function so that there is some error checking (e.g. Chart is selected) and consistency across all the places where this can be used. That Function will also pair nicely with a settings file that can override the Input and default to Selection with no prompt.
That is a nice way to solve this. Is the .address required on the selection?
It looks like it. The documentation claims that it can be a Range but I can't seem to get it to work. It looks like it wants to coerce that default to its value and not take the Range object.
I will go ahead and create this Function and replace the various InputBox calls with it.
This has been added as GetInputOrSelection. It still needs a little work to take a parameter for the InputBox prompt. I did not replace any of the other places with this code yet. I only used it twice for issues related to error handling (see #31).
The outstanding work on this is then:
- add a parameter for the input box text and title
- replace all of the current InputBoxes with this function
This has been added, so I should re-clone to be updated?
You probably want to sync your fork with this one to get up to date. Depending on how you're configured, you should probably follow this and possibly this one first if you don't have upstream configured as a remote.
I try to use GitHub for Windows wherever possible (I'm lazy) but am not sure how it handles syncing a fork; I have not done that yet with it.
I just went and did a pull request on my from yours (web interface). All my projects are in my dropbox folder so syncing one machine will sync all machines. I don't have the client at work so I always end up in teamviewer syncing commits from my w7 home box, ha. The fork syncing process seems a little more complicated than the regular process, what with the upstream and everything. I tried setting it up in osx a while back with terminal and I think I ended up breaking something and backed it out.
I thought it might be more useful to add a parameter to the function -
Function GetInputOrSelection(msg As String) As Range
'etc
Set GetInputOrSelection = Application.InputBox(msg, Type:=8, Default:=strDefault)
This will help with subs like CategoricalColoring where the user needs to identify more than one range for different reasons -
Public Sub CategoricalColoring()
'+Get User Input
Dim rngToColor As Range
On Error GoTo errHandler
Set rngToColor = GetInputOrSelection("Select Range to Color")
Dim rngColors As Range
Set rngColors = GetInputOrSelection("Select Range with Colors")
By returning Nothing on cancel, all the existing errHandlers should still work - good stuff.
I think this issue is fairly resolved. There are probably a couple additional things to consider for this Function going forward:
- Provide a parameter for the default range (maybe a boolean to use
Selectionor not). This will help with those second and third inputs where the selection is less meaningful. - Provide another parameter for the title on the InputBox. This can default to nothing or "Select range" or something.
Regarding the error handlers, we can probably take a second look at where some of those are placed now. Since the Function returns Nothing, we can now check for that (If ... Is Nothing Then : Exit Sub : End If) instead of having to catch a downstream error when Nothing.Value or something similar is attempted.
I was just struggling with something similar to the inputbox thing so I came back here looking for it. Apparently if you require Type 8 input (range) and have a range variable being set to the result, the inputbox will throw an error if the user hits cancel (and maybe when the range is blank). This makes it impossible to catch the error with an if this is nothing. I hate error handlers, so I broke the inputbox out into its own function. That way it handles its own errors and doesn't handle any other errors that pop up. Might be useful -
Private Function GetUserInputRange() As Range
'This is segregated because of how excel handles cancelling a range input
Dim userAnswer As Range
On Error GoTo inputerror
Set userAnswer = Application.InputBox("Please select a single column to parse", "Column Parser", Type:=8)
Set GetUserInputRange = userAnswer
Exit Function
inputerror:
Set GetUserInputRange = Nothing
End Function
I did put this on SO http://stackoverflow.com/a/36630124/1161309
InputBoxes can be a pain. There is a function in butl called GetInputOrSelection that does something similar. It defaults the input to the currently selected range if there is one. Can't remember how I handle the errors though. (I may not)
I'll go over the commits tonight or tomorrow night. My work schedule has temporarily changed a bit and I am away from the computer most of the day.
On Apr 14, 2016, at 11:40, Ray Wise notifications@github.com wrote:
I was just struggling with something similar to the inputbox thing so I came back here looking for it. Apparently if you require Type 8 input (range) and have a range variable being set to the result, the inputbox will throw an error if the user hits cancel (and maybe when the range is blank). This makes it impossible to catch the error with an if this is nothing. I hate error handlers, so I broke the inputbox out into its own function. That way it handles its own errors and doesn't handle any other errors that pop up. Might be useful -
Private Function GetUserInputRange() As Range
'This is segregated because of how excel handles cancelling a range input
Dim userAnswer As Range
On Error GoTo inputerror
Set userAnswer = Application.InputBox("Please select a single column to parse", "Column Parser", Type:=8)
Set GetUserInputRange = userAnswer
Exit Function
inputerror:
Set GetUserInputRange = Nothing
End Function
I did put this on SO http://stackoverflow.com/a/36630124/1161309—
You are receiving this because you modified the open/close state.
Reply to this email directly or view it on GitHub