error handling input boxes for excel 5

[nja5150]

Hi guys and gals! I hope this might be something someone can shed some light on for me!

I've got an input box that opens up to rename an excel sheet. The trouble is...I get a run-time error if the user clicks cancel before entering in a name. The second message box (which verifies the users input) works just as I intended.

Also...worksheet names have limitations on what characters can be used.

I'd like to pop up a message if the user clicks cancel...or alternatively set a default name to be used.

I need some direction to get me on the right track. Thanks for any...input...you can give (pardon the pun)

Here is what I have so far:
Sheets("MB Percentages").Select

MBperReN:
Workbooks(MyPRbk).Activate
Sheets(1).Name = InputBox("Enter the name of the new sheet")

' If the user clicks cancel or enters unacceptable characters here we have a problem....

Msg = "Is the new sheet named correctly?"
Style = vbYesNo + vbCritical + vbDefaultButton1
Title = "Attention:"
Help = "PPER.HLP"
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
MyString = "Yes"
Else
MyString = "No"
GoTo MBperReN
End If

 

[PHV]

You may simply play with the On Error instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

First you have to test the response to the inputbox function for cancel or any invalid character in the string.

Skip,
[sub]
[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More![/sub]

 

[nja5150]

Sounds like I can use both recommendations to get what I want.

Skip...can you be a little more specific? Maybe I need to play with something else besides Sheets(1).Name for the input box -- then set valid input to Sheets(1).Name. Would I do something like...

fourscore:
myvar = InputBox("Enter the name of the new sheet")
On Error
'set my conditions for on error'
Set myvar = Sheet(1).Name
'set if statments to test for unwanted characters
Msg = "Is the new sheet named correctly?"
Style = vbYesNo + vbCritical + vbDefaultButton1
Title = "Attention:"
Help = "PPER.HLP"
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
MyString = "Yes"
Else
MyString = "No"
GoTo fourscore
End If

I just need a little more help to put me on the right track. I feel like I'm close. Thanks guys!

 

[mrmovie]

i believe Skip is suggesting that you need to read up on what is a valid sheetname string,,,i.e lets say that they are not allowed to contain the # character

strSheetName = InputBox("enter sheet name")
If InStr(strSheetName, "#") Then
'do something....
'strSheetName = Replace(strSheetName, "#", " ")
End If

'or that the sheetname cannot already exist
If SheetExists(strSheetName) Then
strSheetName = ....
'InputBox("Please try again")
End If

Function SheetExists(ByVal sPassed As Srting) As Boolean
.....

End Function

PHV is suggesting something like

On Error Resume Next
myvar = InputBox("Enter the name of the new sheet")
Set myvar = Sheet(1).Name
If Err.Number <> 0 Then
myVar = InputBox("Please try again error occured")
...etc etc
End If
On Error Goto 0

 

[SkipVought]

Sheet Names cannot contain certain characters. So If your going to control what the user enters, YOU have to check the value that the user enters to trap any unwanted, illegal characters. That's one thing that programmers do. It can seem very tedious. You have to PROGRAM (error trap) for every error that the user can make and gracefully recover.

Skip,
[sub]
[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More![/sub]

 

[nja5150]

Everyone thanks for your input. I think I can work with other posts on char codes and the like to get what I want. I appreciate all the help! Stars all around!

 

[HarleyQuinn]

Doubt this will be needed now but I'll post it just in case.

In addition to all the helpful advice given so far, if you want to include a default value in the InputBox (as per an interpretation of part of the question in your OP) you can specify one when you call the InputBox. e.g.

myvar = InputBox("Enter the name of the new sheet",,[red]"Default Sheet Name Here"[/red])

The blank parameter before the default value is where you would specify the InputBox's Title.

Hopw this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[GVF]

How about..

Sheets(1).Select
OldName = ActiveSheet.Name
RePlay:
Response = Application.Dialogs(xlDialogWorkbookName).Show
if Response = False then
MsgBox "You must rename the sheet"
Goto Replay
End If
MySheet = Sheets(1).Name

Greg

 

[nja5150]

Thanks for the additional info GVF and Harley! I've learned so much from all the help -- this site is incredible!