Input Box Error

[sweater_vests_rock]

Everyone.

I want to use an input box to pull a range detailing on what sheets the user wishes a program to be run. Here is the code:

Do
    Set rng_Input_Box = Application.InputBox("Select the worksheets from the list in Column A on which you wish to run this program.  If you wish to use all worksheets, select 'All Sheets'.", "Worksheet Selection", Type:=8)
    If IsEmpty(rng_Input_Box) = True Then
        Msg = MsgBox("You have not selected any worksheets.", vbRetryCancel + vbInformation, "Error: No Worksheets Selected")
    ElseIf rng_Input_Box = (Cells(1, 1)) Then
        Set rng_ws = Range(Cells(2, 1), Cells(ws_count, 1))
    Else
        Set rng_ws = rng_Input_Box
    End If
Loop While IsEmpty(rng_Input_Box) = True And Msg = vbRetry

What is bothering me is that

1) if I click "Cancel" when the Input Box occurs on the worksheet in run mode, I receive a 424 error and the code breaks. How can I avoid this code break?

2) if I click "OK" without highlighting any cells, I receive a function/formula error, similar to if something is typed incorrectly in a cell formula. How can I use the loop to avoid this I have written, rather than the loop VB is forcing?



Thanks. Ben.

 

[macleanb]

I think your problem here is that your input returns different types depending on whether a selection was made or escape was pushed - so I guess you will need to use a variant as the target. You can then use VarType to distinguish what you got back (not very well as it doesnt understand ranges) but you can tell if its boolean (11), and then if not go on to treat it as a range. Hope this helps. For a limey - whats a sweater vest?

 

[sweater_vests_rock]

Ben.

Thanks for the advice; your solution does indeed avoid a 424 break.

Regarding sweater vests:
For an excellent example, see http://www.achillknitwear.com/images/sweater-vest-sm.jpg

 

[macleanb]

wish I hadnt asked!! try one of these with it:

http://www.finepipes.com/Calabash.htm

 

[sweater_vests_rock]

Ben.

Whoa! One thing at a time.

Also:

As is often the case, the defeat of one problem has caused another to arise.

In order to use VarType(), I had to order my input box to return a string. This means that when a user clicks on a cell, the input box returns the value contained in that cell. Unfortunately, when a user selects multiple cells, the input box returns only the value contained in the FIRST cell. Because of how I am using the user selection, I need to be able to capture all selected cells -- originally I was doing this with a range, although I could accomplish the same end goal using the cell values and an array.

Is there away to make my two goals inclusive?
1) Avoid 424 Break
2) Capture entire user selection

Thanks. Ben.

 

[macleanb]

make it a variant not a string and change you inputbox back the way it was...

 

[sweater_vests_rock]

Ben.

I was just coming here to report that I had figured it out!

Thanks again for all the help.
Ben.