Using a UserForm to rename a tab

[gheyman]

I have code that pulls in tabs from another workbook. When I do this, I want a Userform to pop-up and force the User to rename the tab.

(I'm doing this because I use a lot of Indirect formulas that use the tab name in it: =IFERROR((INDIRECT($A4&"!$L$3")),"") where $A4 is a tab name in a cell) Therefore I have to stop the tab names from having spaces, dashes.... in it.

I can place the "Name" from the userform text box into a sheet and then use

Sub Name_Tab()
'
On Error GoTo ErrTrap
    ActiveSheet.Name = Sheet1.Range("$C$6").Value
On Error GoTo 0
    Exit Sub
ErrTrap:
    ActiveSheet.Name = Sheet1.Range("$C$6").Value & " Error"
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
On Error GoTo 0
'
End Sub

But I was wondering if there was a better way to use the (UserForm) userformNameTab (TextBox) textbox1 value directly.

 

[gheyman]

Got it

Private Sub CommandButton1_Click()
If TextBoxName.Value = "" Then
    MsgBox ("You must rename the tab")
   TextBoxName.SetFocus

Else
        ActiveSheet.Name = TextBoxName.Value
       UserFormNameTab.Hide
End If

End Sub