Validating cell input

[Michael]

Hi all,

We have these excel sheets for registering worked hours.
Is there a way so that on input is checked what values are entered so that
they only can enter values ending with .0 or .25 or .5 or .75


Txs in advance

Michael

 

[Harald Staff]

Hi Michael

I hate bothering my users with messageboxes, so here's a macro that rounds
entries in column B by itself. Rightclick the sheet tab, choose "View code",
paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Column = 2 Then 'B column !
If .HasFormula = False Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = CLng(.Value * 4) / 4
Application.EnableEvents = True
End If
End If
End If
End With
End Sub

 

[Michael]

Txs for the quick respons Harald,

Works like a charm on column B. But the input area i have is from B7 to K22.

Can i adjust it? and if so, could you tell me how?


Txs in advance
Michael

 

[Harald Staff]

Hi Miccael

Replace
If .Column = 2 Then
with
If Not Intersect(Target(1), Range("B7:K22")) Is Nothing Then

 

[Michael]

Txs a million.

Michael

Hi Miccael

Replace
If .Column = 2 Then
with
If Not Intersect(Target(1), Range("B7:K22")) Is Nothing Then