Cancel button for input box

[willyboy58]

The seemingly simple stuff drives me crazy!

The user clicks a button “Process Data”. An input box opens and gives the user 3 choices: 1)Enter month and year to process data 2) click OK to view the raw data without processing or 3) click CANCEL and close the activeworkbook without viewing or processing data. I have the following code:

Public ReportMonth As String

Sub MonthAndYear()
Application.ScreenUpdating = False
ReportMonth = InputBox("Enter the FULL name of the month" _
& "to be processed followed by the FOUR DIGIT year. If " _
& "you want to view the raw data, select OK. If you " _
& "want to close the workbook and exit now, select " _ & "CANCEL", Type:=4)

If ReportMonth = "" Then
End 'closes input box but does not close _
raw data workbook, which is OK

If ReportMonth = False Then
ActiveWorkbook.Close
'This is where the user selects CANCEL and the _ activeworkbook shoulc close. Instead I get the _ message "Named arguement not found"

End If
End If
Application.ScreenUpdating = True
End Sub

TIA

Bill

 

[MisterC]

ReportMonth will never equal False. If a user selects cancel on an inputbox then a zero length string is returned. You will have to test for "" to catch that.

 

[willyboy58]

MisterC,

ReportMonth = "" is what closes the input box, but not the workbook.

I need someway to detect when the CANCEL button is clicked which causes both the input box and the workbook to close.

TIA

Bill

 

[TonyJollans]

Hi Bill,

Sorry, you can't do it that way. Ridiculous as it seems, there is no way to tell the difference between the buttons if the inputbox is empty. You might (if you have well behaved Users) be able to set some default input (like a single space) which would not be passed back if Cancel were pressed but would be passed (and could be ignored) if OK were pressed, but it's not reliable because if a User deleted it and then pressed OK you would be assuming they had pressed Cancel. Otherwise I think you'll have to create your own UserForm to do the job.

Enjoy,
Tony

 

[SQLBI]

Hi,

With the buttons on the input box, OK returns the value of the input field and CANCEL returns a value of 'False'.

If you want to test for the user pressing Cancel, you could use;

If MyInputBoxReturn = False Then

     Code to handle Cancel Click.

Else

     Code to handle input value.

End If

Hope this helps.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[TonyJollans]

Hi Leigh,

What version of Excel do you get that result with? I'm a bit handicapped at the moment as my main PC is broken and I only have '97 to play with but in (my) 97 it works as advertised and returns a zero-length string when cancel is pressed.

Enjoy,
Tony

 

[SQLBI]

Tony,

Using Excel 2000 Pro, got the info from the "InputBox Method" help files.

Remarks

Use InputBox to display a simple dialog box so that you can enter information to be used in a macro. The dialog box has an OK button and a Cancel button. If you choose the OK button, InputBox returns the value entered in the dialog box. If you click the Cancel button, InputBox returns False.




Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[willyboy58]

Hey guys,

Thanks for the help. I adjusted my code a bit and it works except I’m still having a problem with the Cancel button. I have Excel 2000 (9.0.2720) Premium. I changed from InputBox to Application.InputBox plus a few other changes. Here’s my code:

Public ReportMonth As String
Sub MonthAndYear2()
Application.ScreenUpdating = False

ReportMonth = Application.InputBox("Enter the FULL name of the month to " _
& "be processed followed by the FOUR DIGIT year. If you only " _
& "want to view the raw data, select OK. If you want to close " _
& "the Propxfer workbook and exit now, select CANCEL&quot

If ReportMonth <> &quot;&quot; Then 'user has entered month and _ selected OK and info is processed as it should be
Else

If ReportMonth = &quot;&quot; Then 'user has selected OK with no entry
End 'closes input box but does not close Propxfer _
workbook, which is OK
Else
'following is code to handle Cancel click. _
Should close input box AND Propxfer, but the following _
does not close Propxfer. Instead it process the info.

'I tried the following:
ReportMonth = CBool(ReportMonth)
If ReportMonth = FALSE Then
Workbooks(&quot;Propxfer&quot.Close savechanges:=False
End If
‘The above processes the data and gives it the file name &quot;FALSE&quot;. ReportMonth is Public and is saved as the _ file name in a different proc.

‘ I also tried:
If CBool(ReportMonth) = False Then
Workbooks(&quot;Propxfer&quot.Close savechanges:=False
End If
End If
End If
Application.ScreenUpdating = True
End Sub

As always, thanks for the help.

Bill