Inputbox for Worksheet name - problem

S

Stuart

I'm using:
WkSheetName = Application.InputBox("Please type a name for" & _
" this Worksheet ... in CAPITALS")
'update the user
Application.StatusBar = ".....now processing... " & WkSheetName
ActiveSheet.Name = WkSheetName

User's are reporting an occasional error (1004: While renaming you entered
an illegal character..etc) on the line:
ActiveSheet.Name = WkSheetName

When I check, I can see that somehow WkSheetName has the value
: WkSheetName : "EXTERNAL+$1:$1WORKS" : String in the
Locals window, as opposed to the correct value of:
EXTERNAL WORKS.

How might this have occured, and can I prevent it please?

Regards.
 
B

Bob Phillips

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Stuart,

You are using Application.Inputbox, and this allows more flexibility in
inputting data. Unfortunately, this can include any cells selected, which is
your problem.

Use this instead

WkSheetName = InputBox("Please type a name for" & _
" this Worksheet ... in CAPITALS")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

Stuart

Thanks.

Seems hitting the spacebar with the shift key depressed gives an
unexpected bonus (namely the activecell address). Another thing
I didn't know!

Regards.
 
Top