• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Use VBA input box to rename current sheet

kevinonearth

kevinonearth

Member
Hi there
could anyone help me with writing some simple code?
All I want to do is run an input box which will take in a string and rename the active worksheet with that string
 
Hi Kevin,

Try using the below:
Code: 
Sub Sheet_Nm()
response = InputBox("Name of the Sheet?", vbOKCancel)
If response = False Or response = "" Then
MsgBox "Invalid Name"
Exit Sub
Else
ActiveSheet.Name = response
End If
response = ""
End Sub
 
I just realised that when I use option explicit and declare 'response' as a string I get an error, when I don't use option explicit it works fine
Do you know why that is?
 
Ok, this works, but what if the initial value is a date? I should have initially said that I wanted to take in a date value. Let me explain:
I'm doing an employee time sheet for every week of 2014, I want to take the first Friday of Jan 2014 as the last day of the first week of 2014.
I'm then going to try and write a macro to create 52 similar sheets, each time adding 7 to the initial date.
But I'm having trouble at the first hurdle, taking in a value and making it a date!
Can anyone assist?
 
Hi, kevinonearth!
Declare response as Variant instead of String. But always use Option Explicit! :)
Regards!
 
SirJB7 has rightly mentioned that you should declare response as "variant".

@SirJB7 : Can you emphasize on the use of option explicit? Even I am not aware of its advantages...:)
 
You must log in or register to reply here.
Back
Top