CODEWITHSUNDEEP
vbaexcel
ayyappan km
ayyappan km

Reputation: 83

Rename worksheet Based on INPUT BOX

Please help me to to change the below code. Need to select the renaming cell through InputBox

Thanks

Sub RenWSs()

Dim WS As Worksheet
Dim shtName
Dim newName As String
Dim i As Integer

For Each WS In Worksheets
    With WS
        If Trim(.Range("c14")) <> "" Then
            shtName = Split(Trim(.Range("c14")), " ")
            newName = shtName(0)
            On Error GoTo ws_name_error
            .Name = newName
            GoTo done
repeat:
            .Name = newName & i
            GoTo done
ws_name_error:
            i = i + 1
            Resume repeat
        End If
    End With
    On Error GoTo 0
done:
Next

End Sub

Upvotes: 0

Views: 850

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

There are a few ways you can use the InputBox to access Cell C4.

One is by selecting a String in the InputBox , see code below:

Dim RngStr  As String

RngStr = Application.InputBox(prompt:="Select the Cell for the new Sheet's name", Type:=2)
If Trim(.Range(RngStr)) <> "" Then

Another, is by selecting a Range in the InputBox, see code below:

Dim rng As Range

Set rng = Application.InputBox(prompt:="Select the Cell for the new Sheet's name", Type:=8)
If Trim(rng) <> "" Then

Full Code

Option Explicit

Sub RenWSs()

Dim WS As Worksheet
Dim shtName
Dim newName As String
Dim i As Integer
Dim RngStr  As String

RngStr = Application.InputBox(prompt:="Select the Range for the new Sheet's name", Type:=2)
For Each WS In Worksheets
    With WS        
        If Trim(.Range(RngStr)) <> "" Then
            shtName = Split(Trim(.Range(RngStr)), " ")
            newName = shtName(0)
            On Error GoTo ws_name_error
            .Name = newName
            GoTo done
repeat:
            .Name = newName & i
            GoTo done
ws_name_error:
            i = i + 1
            Resume repeat
        End If
    End With
    On Error GoTo 0
done:

Next

End Sub

To read more about InputBox functionality: https://msdn.microsoft.com/en-us/library/office/ff839468.aspx

Upvotes: 1

Related Questions