Data validation to a dynamic range

Question

I have a column named "time" in my excel sheet. I want to write a code such that whenever user performs an entry in time column, if it is a whole number, it should accept, but if it is not a pop up should appear saying "only numbers allowed". Also, the validation should be dynamic i.e. should automatically validate next row if users enters a new entry

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Column = Range("Meeting Time").Column Then

    If Not (IsNumeric(Target.Value)) Then

        MsgBox "only numbers allowed"

        Target.Value = ""

        Target.Select
    End If

End If

End Sub

Please post your actual code instead of screenshots of it. Is there a specific reason why this needs to be done via VBA? It seems to me that it can be easily done with the data validation tool in excel. Unless I'm missing something. — Stavros Jon, May 22 '19 at 05:49
Posted actual code. I want the validation to be dynamic. There are two columns, lets say one is User Name and the other one is Time. At present there are 4 entries in user name and time each. If a new entry is made now, I want the validation on time column to be active for the new entry. If it can be achieved through excel itself then please guide me on that — Rithwik Sarma, May 22 '19 at 06:09
Does it absolutely have to be dynamic? It seems to me that if you just apply a data validation rule to the whole column, you would be covered. No? — Stavros Jon, May 22 '19 at 09:13
Again, maybe I'm missing something, but I think you could just select the column you want, go to `Data>Data Validation` on excel's ribbon and just add a data validation rule for the whole column by specifying the `Allow` as `Whole Number`, the `Minimum` as `1` and the `Maximum` as `160`? You can also customize the `Error Alert` message to fit your needs. — Stavros Jon, May 22 '19 at 10:46

score 0 · Answer 1 · edited May 22 '19 at 06:06

0

first you can create a range name for column you want your "time" and you can use the sample codes below.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Column = Range("time").Column Then

      If Not (IsNumeric(Target.Value)) Then

        MsgBox "only numbers allowed"

        Target.Value = ""

        Target.Select

      End If

    End If

End Sub

edited May 22 '19 at 06:06

Pawel Czyz

1,651
4
17
21

answered May 22 '19 at 04:30

jonrizz

94
4

How do i create a range name. I want it to be dynamic i.e. I want it to take the range from row 2 to last row. Please bear with me, I'm new to VBA – Rithwik Sarma May 22 '19 at 04:39
yes it will be dynamic, as you may notice on the code, we are only checking the column. if you want you can specify the column number. you may use this instead If Target.Column = 1 Then, meaning its checking on column A – jonrizz May 22 '19 at 04:41
I'm unable to run this code. I am not getting an option to run this sub. I'm being shown all other subs except this one – Rithwik Sarma May 22 '19 at 04:42
is your excel macro enabled?, did you add the code under workbook? – jonrizz May 22 '19 at 04:43
Yes. I have created 4 subs all of which I'm able to execute. But not this one. I have got 4 sheets on my workbook with time column present in the "input" sheet – Rithwik Sarma May 22 '19 at 04:45
see the sample image i have attached above – jonrizz May 22 '19 at 04:48
I too have attached two images to my original question. Please see them and tell what is wrong – Rithwik Sarma May 22 '19 at 04:52
Can you help me with this question? I tried everything I knew but could not solve. https://stackoverflow.com/q/56207459/8631622 @jonrizz – Pie May 22 '19 at 07:13
@RithwikSarma, did try your code and its look working fine with me? is there any error you encountered? – jonrizz May 22 '19 at 22:34

Error 1004 · Accepted Answer · 2019-05-22T11:18:20.920

You could try:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    '1. Check if the column that affected is B (change to the column you want)
    '2. Check if changed field is one (used to avoid errors if user change more than one cells at the same time)
    If Not Intersect(Target, Columns("B:B")) Is Nothing And Target.Count = 1 Then
        'Check if target is numeric
        If Not IsNumeric(Target.Value) Then
            Call Clear(Target)
        End If
        'Check if target.offset(1,0) is numeric
        If Not IsNumeric(Target.Offset(1, 0).Value) Then
            Call Clear(Target.Offset(1, 0))
        End If
    End If

End Sub

Sub Clear(ByVal rng As Range)

    'Disable events in order to prevent code to re trigger when clear cell
    Application.EnableEvents = False
    rng.Value = ""
    'Enable events
    Application.EnableEvents = True

End Sub

EDITED VERSION:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    '1. Check if the column that affected is B (change to the column you want)
    '2. Check if changed field is one (used to avoid errors if user change more than one cells at the same time)
    If Not Intersect(Target, Columns("B:B")) Is Nothing And Target.Count = 1 Then
        'Check if target is numeric
        If Not IsNumeric(Target.Value) Then
            Call Clear(Target)
        ElseIf Target.Value > 160 Or (Target.Value = Int(Target.Value) = False) Then
             Call Clear(Target)
        End If
        'Check if target.offset(1,0) is numeric
        If Not IsNumeric(Target.Offset(1, 0).Value) Then
            Call Clear(Target.Offset(1, 0))
        ElseIf Target.Offset(1, 0).Value > 160 Or (Target.Offset(1, 0).Value = Int(Target.Offset(1, 0).Value) = False) Then
            Call Clear(Target)
        End If
    End If

End Sub

Sub Clear(ByVal rng As Range)

    'Disable events in order to prevent code to re trigger when clear cell
    Application.EnableEvents = False
    rng.Value = ""
    'Enable events
    Application.EnableEvents = True

End Sub

What do I need to change if I need to prevent decimal numbers as well. Only integers less than 160 are allowed — Rithwik Sarma, May 22 '19 at 10:25
Not working. Even integers less than 160 are getting cleared — Rithwik Sarma, May 23 '19 at 04:34
No, I have copy pasted the same code. On entering values such as 123, 110,25 etc, the entry is being cleared — Rithwik Sarma, May 23 '19 at 10:53
debug the code and tell me at which point the code clear your cell. At `If Not IsNumeric` or `ElseIf Target.Offset` — Error 1004, May 23 '19 at 11:10

Data validation to a dynamic range

2 Answers2