VBA Set Sheet name with prefix text, user input, suffix text and cell value

[vortensis]

Greetings!

I have a question, I've been using some simple code to set the Sheetname as cell value for some time (incidentally I think its from MrExcel too), I want to adjust it, but really haven't the slightest on how...
Current;

Sub SetTabName()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
ws.Name = Range("A2").Value
Next
End Sub

What I would like to achieve is hitting the button, it runs through and does its thing, six unrelated bits and bobs and then sets the name... I'd like a little more detail by grabbing the contents of cell A2, and prompting for the last bit which is not in the spreadsheet...

The sheetname should end up looking like;
"Ticket-##### - Device 123456"

123456 is Cell A2 value
##### being a user prompt for a number in 5 to 6 digits

Can any Excel wizards assist in this?
Thanks,
vortensis

 

[Fluff]

Should the ##### be the same for each sheet?

 

[Gokhan Aycan]

Dim strInput as String

strInput = InputBox("Prompt_here","Title_here")

ws.Name = "Ticket-" & strInput & " - Device " & Range("A2").Value

Might not work for your case exactly, but something like this just to give you an idea.

There is a 32 characters limit for sheet names iirc.

 

[vortensis]

Should the ##### be the same for each sheet?

Thanks for the reply Fluff!

For this particular dataset there is only ever one sheet.
At the moment there isnt a need to have it target multiple sheets...
And I have managed to adapt a number of other macro's to run through multiple sheets, but not sure that will ever be needed...

 

[Fluff]

If there is only one sheet, why are you looping through the worksheets?

 

[Fluff]

As there is just one sheet you can replace your code with

ActiveSheet.Name = "Ticket-" & InputBox("please enter a ticket") & " - Device " & Range("A2").Value

 

[vortensis]

If there is only one sheet, why are you looping through the worksheets?

That's probably just a holdover from the code I borrowed, it worked fine as it was so didn't want to mess with it...

 

[vortensis]

As there is just one sheet you can replace your code with

ActiveSheet.Name = "Ticket-" & InputBox("please enter a ticket") & " - Device " & Range("A2").Value

Fantastic, so simple... I may have been trying to over think the problem

Marked as solution, thanks again :D

 

[Fluff]

Glad we could help & thanks for the feedback.