VBA Prohibit the decimal input for a range of cell

[zeromax1]

Hi All,

I am trying to limit the user to input decimal data in a range of a worksheet. like A27:B37.

But the data validation function is already used by another function to ban the formula input.

Is there any VBA code can limit the user to input the number with decimal automatically?

Thank you.

The below code only shows the signle cell whether integer or not.

Function isInteger(c As Variant) As Boolean
    If Not IsNumeric(c) Then Exit Function
    isInteger = (c = CLng(c))
End Function

 

[Joe4]

But the data validation function is already used by another function to ban the formula input.

What exactly is that rule? Can you post it?

I think we may be able to put both in Data Validation.

 

[zeromax1]

What exactly is that rule? Can you post it?

I think we may be able to put both in Data Validation.

I use the below code to create a new function for the formula checking

Public Function check_formula(r1 As Range) As Boolean
    If r1.HasFormula = False Then
        check_formula = False
    Else
        check_formula = True
    End If
End Function

Then I use the above formula & input it into cell IW275.

If the range of data is formula, then the below cell (IW275) will shows "True"

After that, the data validation will ban the user to input formula.

Thank you.

 

[Joe4]

You don't need to create your own function. Excel already has an ISFORMULA function.

Here is a Custom formula you can put in Data Validation to handle both.
This is assuming that you only want to allow whole number entries into the cells (and not any text entries):

=AND(NOT(ISFORMULA(A27),A27-INT(A27)=0)

 

[zeromax1]

You don't need to create your own function. Excel already has an ISFORMULA function.

Here is a Custom formula you can put in Data Validation to handle both.
This is assuming that you only want to allow whole number entries into the cells (and not any text entries):

=AND(NOT(ISFORMULA(A27),A27-INT(A27)=0)

Thank you for your advise. But seems the formula has bugs?

 

[Joe4]

Sorry, when I was editing it, I accidentally dropped a right parenthesis:

=AND(NOT(ISFORMULA(A27)),A27-INT(A27)=0)

 

[zeromax1]

Sorry, when I was editing it, I accidentally dropped a right parenthesis:

=AND(NOT(ISFORMULA(A27)),A27-INT(A27)=0)

Thank you very much. I found the same issue.

But if we paste as value from another cell, it still allows decimal.

 

[Joe4]

OK, this is the first that you mentioned that people may be copying values into this cell.
That is because when you copy/paste a value from another cell, it overwrites any Formatting and Data Validation you have in that cell with what is coming from the source cell.

If that is the case, I would probably go with a Worksheet_Change event procedure on the sheet, i.e.
right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim chk As Boolean
    
'   See if any cells updated in your watched range
    Set rng = Intersect(Range("A27:B37"), Target)
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells in watched range
    For Each cell In rng
        chk = True
'       See if cell has formula or decimal
        If Application.WorksheetFunction.IsFormula(cell) Then
            chk = False
        Else
            If IsNumeric(cell) Then
                If cell - Int(cell) > 0 Then
                    chk = False
                End If
            End If
        End If
'       Clear value if necessary
        If chk = False Then
            Application.EnableEvents = False
            cell.ClearContents
            MsgBox "Invalid entry in cell " & cell.Address(0, 0), vbOKOnly, "ENTRY ERROR!"
            Application.EnableEvents = True
        End If
        
    Next cell
    
End Sub

 

[zeromax1]

OK, this is the first that you mentioned that people may be copying values into this cell.
That is because when you copy/paste a value from another cell, it overwrites any Formatting and Data Validation you have in that cell with what is coming from the source cell.

If that is the case, I would probably go with a Worksheet_Change event procedure on the sheet, i.e.
right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim chk As Boolean
   
'   See if any cells updated in your watched range
    Set rng = Intersect(Range("A27:B37"), Target)
    If rng Is Nothing Then Exit Sub
   
'   Loop through updated cells in watched range
    For Each cell In rng
        chk = True
'       See if cell has formula or decimal
        If Application.WorksheetFunction.IsFormula(cell) Then
            chk = False
        Else
            If IsNumeric(cell) Then
                If cell - Int(cell) > 0 Then
                    chk = False
                End If
            End If
        End If
'       Clear value if necessary
        If chk = False Then
            Application.EnableEvents = False
            cell.ClearContents
            MsgBox "Invalid entry in cell " & cell.Address(0, 0), vbOKOnly, "ENTRY ERROR!"
            Application.EnableEvents = True
        End If
       
    Next cell
   
End Sub

Pretty Awesome. Thank you very much.

 

[Joe4]

You are welcome.
Glad I was able to help!