how to prevent user from clicking a button on error

Hi all,

I have the following code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim FACTSHEET
    Dim pname
    Dim str
    
    Static OldTargetRow As Long
    FACTSHEET = 22
    pname = 3
    
    Application.EnableEvents = False
    
    Call MarkRequired
    Call ValidateSheet
    
    prevrow = OldTargetRow
    OldTargetRow = Target.row
    
     Application.EnableEvents = False
        If prevrow > 10 Then
        
            str_len = Len(Cells(prevrow, pname).Value)
            str = Trim(Cells(prevrow, pname).Value)
   
            If (str <> "") Then
            If (Asc(Mid(str, 1, 1)) >= 48 And Asc(Mid(str, 1, 1)) <= 57) Then
                MsgBox ("Product Name cannot begin with a Number")
                Cells(prevrow, pname).Select
                OldTargetRow = prevrow
            End If
            End If
            
            If (str <> "") Then
            For i = 1 To str_len
                If (Asc(Mid(str, i, 1)) <= 47) Or (Asc(Mid(str, i, 1)) >= 58 And Asc(Mid(str, i, 1)) <= 64) Or (Asc(Mid(str, i, 1)) >= 91 And Asc(Mid(str, i, 1)) <= 94) Or (Asc(Mid(str, i, 1)) = 96) Or (Asc(Mid(str, i, 1)) >= 123) Then
                        MsgBox ("Product Name cannot contain special characters")
                        Cells(prevrow, pname).Select
                        ActiveCell.Cells(prevrow, pname) = ""
                        OldTargetRow = prevrow
                        Exit For
                End If
            Next i
            End If

In this code i am checking if the user types any special characters in name.
If so then on selection change i ll show a msgbox and select that cell again for the user to correct.

But the problem is the user can click on a button in my excel and go to the next tab,

How can i prevent him from clicking the button in this case?

Please help me with this...

Thank you,
Glen

Glen

Would making it a change event instead of a selectionchange event beat the problem? Just a thought and not tested at all....


rylo

Thank you Rylo .

I tried to do the check on click of the Button . so when user clicked the button , i call a function and did the check on all the rows if any names have special characters .

This works now

Thank you.