Inputbox for Worksheet name - problem

  • Thread starter Thread starter Stuart
  • Start date Start date
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.
 
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
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)
 
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.
 
Back
Top