Inputbox

Ampleford · Apr 12th, 2002, 03:24 AM

I have an inputbox which asks for some info, drops that on a spread then prints the spread based on that info. All well - except when you press the "Cancel" button - it still runs the Macro... What is the syntax to make it stop ?

My script for the macro so far is :-

Prdno = InputBox("Please enter the Period you wish to print", "Enter Period")
Sheets("Register").Select
Range("A1:C1").Select
ActiveWorkbook.Unprotect
Sheets("Data").Visible = True
Sheets("Data").Select
Range("e1").Select
Selection.FormulaR1C1 = Prdno
Sheets("Register").Select
Range("D1:E1").Select
ActiveCell.FormulaR1C1 = "=Data!RC[2]"~~~~~~~~~ Etc

I know there needs to be a line after the first which acknowledges and actions the "Cancel" button...

Help please ?

Mudface · Apr 12th, 2002, 03:31 AM

If cancel is pressed a zero-length string is returned, so just add the line below after calling your inputbox: -

Prdno = InputBox("Please enter the Period you wish to print", "Enter Period")
If Prdno="" Then Exit Sub
' Rest of macro

Ampleford · Apr 12th, 2002, 03:34 AM

Many Thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 12th, 2002, 03:24 AM	#1
Ampleford Board Regular Join Date: Mar 2002 Location: Scotland Posts: 341	I have an inputbox which asks for some info, drops that on a spread then prints the spread based on that info. All well - except when you press the "Cancel" button - it still runs the Macro... What is the syntax to make it stop ? My script for the macro so far is :- Prdno = InputBox("Please enter the Period you wish to print", "Enter Period") Sheets("Register").Select Range("A1:C1").Select ActiveWorkbook.Unprotect Sheets("Data").Visible = True Sheets("Data").Select Range("e1").Select Selection.FormulaR1C1 = Prdno Sheets("Register").Select Range("D1:E1").Select ActiveCell.FormulaR1C1 = "=Data!RC[2]"~~~~~~~~~ Etc I know there needs to be a line after the first which acknowledges and actions the "Cancel" button... Help please ?

Apr 12th, 2002, 03:31 AM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	If cancel is pressed a zero-length string is returned, so just add the line below after calling your inputbox: - Prdno = InputBox("Please enter the Period you wish to print", "Enter Period") If Prdno="" Then Exit Sub ' Rest of macro

Apr 12th, 2002, 03:34 AM	#3
Ampleford Board Regular Join Date: Mar 2002 Location: Scotland Posts: 341	Many Thanks