Input Box - don't allow spaces

[Shiremaid]

I have a macro to allow users to add a country to a project. But it causes errors if they include spaces in the country name. I have instructed them not to include spaces, but of course someone always ignores that. Is there a way I can make the input box not accept spaces in the entry?

Sub AddCountry

Sheets("SiteTemplate").Visible = True
Sheets("SiteTemplate").Copy Before:=Sheets("SiteTemplate")
Range("A1") = InputBox("Country Name (no spaces)")
ActiveSheet.Name = Range("A1").Value

End Sub

 

[mumps]

Try:

Sub AddCountry()
    Dim response As String
    Sheets("SiteTemplate").Visible = True
    response = InputBox("Country Name (no spaces)")
    If InStr(1, response, " ") > 0 Then
        MsgBox ("Do not include spaces in the country name.  Please re-enter the country name without spaces.")
        Exit Sub
    Else
        Sheets("SiteTemplate").Copy Before:=Sheets("SiteTemplate")
        ActiveSheet.Range("A1") = response
    End If
    ActiveSheet.Name = Range("A1").Value
End Sub

 

[Shiremaid]

Perfect, thank you!

 

[mumps]

My pleasure.