Preventing Users from inputting certain characters

[Yeoman.jeremy]

Hi there.

In my workbook, I have a macro which saves the file, and names it as the name and address of the client entered - which means if a user entered (for example) "59/2 example street"

it wouldn't save because it's trying to path to the folder 59, or however it works.

how can I create a rule that users cannot enter in characters such as . / \ etc



Thanks

 

[Tom Urtis]

Where, or into what object exactly, are those characters being entered: in a cell? an inputbox? a textbox? userform? forms control or activex control?

 

[Yeoman.jeremy]

sorry, They're being placed into a cell

 

[Tom Urtis]

You did not specify which cell it is, so let's say it is cell A1.

Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Test an entry by including a character like [ or / and see the result.

Private Sub Worksheet_Change(ByVal Target As Range)
 
'Specify the target cell whose entry shall be the workbook name.
If Target.Address <> "$A$1" Then Exit Sub
'OK for target cell to be empty.
If IsEmpty(Target) Then Exit Sub
'Workbook characters to not allow are /, \, [, ], *, ?, or :.

'Verify that none of these characters are present in the cell's entry.
Dim IllegalCharacter(1 To 7) As String, i As Integer
IllegalCharacter(1) = "/"
IllegalCharacter(2) = "\"
IllegalCharacter(3) = "["
IllegalCharacter(4) = "]"
IllegalCharacter(5) = "*"
IllegalCharacter(6) = "?"
IllegalCharacter(7) = ":"

For i = 1 To 7
If InStr(Target.Value, (IllegalCharacter(i))) > 0 Then
MsgBox "You used a character that violates workbook naming rules." & vbCrLf & vbCrLf & _
"Please re-enter a workbook name without the ''" & IllegalCharacter(i) & "'' character.", 48, "Not a possible workbook name !!"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If
Next i
 
End Sub

 

[Yeoman.jeremy]

The Cell the data is being entered into is part of a merged cell.
The cell is B2:E2, when it runs the clear cell part of this code, it won't clear a part of the merged cell.

Do i have to change the target address to a range as opposed to a cell then?

Also, There are 5 different merged cells that this needs to be applied for, I assume i can just change the target address?

 

[Tom Urtis]

I suggest you scrap the merged cell approach, merged cells are the antichrist to VBA and in my opinion to Excel in general. Instead, after unmerging B2:E2, select that range, right click and select Format cells. In the Alignment tab, in the Horizontal drop down list select Center across selection and click OK.

Then, change the codeline from
If Target.Address <> "$A$1" Then Exit Sub

to
If Target.Address <> "$B$2" Then Exit Sub

 

[Yeoman.jeremy]

I have used the merged cells in my coding earlier on in the project,so i fear if i change it to unmerged, then it will mess with the rest of it.
Also, it's for ease of use for the users to just click a merged cell.

There's no way around this?

 

[jkoudsi]

Just because the cell is merged it shouldn't affect the address of the cell.