if no name entered or blank it should send warn message and then end-CodePudding

Private Sub CommandButton1_Click()
    Dim sourceworkbook As Workbook
    Dim currentworkbook As Workbook
    
    Set currentworkbook = ThisWorkbook
    Set sourceworkbook = Workbooks.Open("C:\Users\Robert Sindo\Desktop\JMI Inventory Analyst Master Files\JYKO\JYKO SKU  Masterlist.xlsm")
    sourceworkbook.Sheets("Quickbook").Copy after:=currentworkbook.Sheets("QB Uploader")
    Sheets(Sheets.Count).Name = InputBox("ASSIGN A NEW NAME:")
    
    If sourceworkbook.Value = "" Then
         MsgBox "There is no data in your selection."
         Exit Sub
      Else
         sourceworkbook.Sheets("Quickbook").Select
      End If
    
    sourceworkbook.Close

CodePudding user response：

Existing or Invalid Sheet Name (Error-Handling)

Option Explicit

Private Sub CommandButton1_Click()
    
    Dim currentworkbook As Workbook: Set currentworkbook = ThisWorkbook
    
    Dim sourceworkbook As Workbook
    Set sourceworkbook = Workbooks.Open("C:\Users\Robert Sindo\Desktop\JMI Inventory Analyst Master Files\JYKO\JYKO SKU  Masterlist.xlsm")
    
    sourceworkbook.Sheets("Quickbook").Copy After:=currentworkbook.Sheets("QB Uploader")
    ' If copying after the last sheet, rather use the following:
    'sourceworkbook.Sheets("Quickbook").Copy After:=currentworkbook.Sheets(currentworkbook.Sheets.Count)
    
    sourceworkbook.Close SaveChanges:=False
    
    Dim NewName As String: NewName = InputBox("ASSIGN A NEW NAME:")
    
    Dim ErrNum As Long
    
    On Error Resume Next ' there can be two reasons: existing name and invalid name (emptystring, more than 31 chars...)
        currentworkbook.Sheets(currentworkbook.Sheets.Count).Name = NewName
        ErrNum = Err.Number
    On Error GoTo 0
    
    If ErrNum <> 0 Then
         MsgBox "The name '" & NewName & "' could not be used.", vbCritical
         Exit Sub
    'Else
         'MsgBox "Sheet renamed.", vbInformation
    End If
    
End Sub

CodePudding user response：

UserInput = InputBox("Input some text")

If StrPtr(UserInput) = 0 Then
  MsgBox "You've canceled the input box"
ElseIf UserInput = vbNullString Then
  MsgBox "You does not enter anything"
Else
  Process(UserInput)
End If