Status: Solved
Priority: Medium
Security: Public
Views: 676
Last Modified: 2014-02-17

VBA - Evaluate Cells, then continue or end..

Hello Experts,

I would like the VBA code I already have, to evaluate some cells - and based on the answer either the VBA code continues, or ends...

Here is the existing code I am using...

Private Sub Worksheet_Change(ByVal target As Range)

Dim cel As Range, targ As Range
Dim v As Variant
If target.Rows.Count >= Rows.Count Then Exit Sub

Set targ = Range("CallDuration")     'Watch these cells for time entries
Set targ = Intersect(targ, target)

If Not targ Is Nothing Then
    Application.EnableEvents = False
    For Each cel In targ.Cells
        If IsNumeric(cel.Value) Then
            If cel.Value > 0 Then
                If Len(cel.Value) < 7 Then
                    On Error Resume Next
                    v = 0
                    v = TimeValue(Format(cel.Value, "00:00:0#"))
                    On Error GoTo 0
                    If v = 0 Then
                        cel.Select
                        MsgBox Format(cel.Value, "00:00:0#") & " is not a permissible time value!"
                        cel.ClearContents
                    Else
                        cel.NumberFormat = "hh:mm:ss"
                        cel.Value = v
                    End If
                Else
                    cel.Select
                    MsgBox "Too many digits in " & cel.Value
                    cel.ClearContents
                End If
            Else
                If cel.Value < 0 Then
                    cel.Select
                    MsgBox cel.Value & " is not a permissible time value"
                    cel.ClearContents
                End If
            End If
        Else
            cel.Select
            MsgBox cel.Value & " is not a permissible time value"
            cel.ClearContents
        End If
    Next
    Application.EnableEvents = True
    
End If

End Sub

Open in new window

Here is what I would like...

1.) The user enters a value into a cell - which then triggers the above VBA code.
2.) The code should FIRST check named range ShiftStart & ShiftEnd.
3.) If ShiftStart AND ShiftEnd have a value (IE 12:00 PM) - then continue with the existing VBA code like normal.
4.) If ShiftStart OR ShiftEnd have NO value - then the rest of the VBA code should NOT run, and the value that was typed in (which triggered this VBA in the first place) should NOT be accepted into the cell.

I hope that was enough information to help.

I think the revised piece of code I am looking for, should be easy to accomplish - just difficult for my skill level. If you revise the above code, please leave the existing code - unchanged if possible, and put clear notes (comments in VBA) to clearly note the new additions. (It's just easier for me to read through)

Thank you in advance for your help!

~ Geekamo

Geekamo

Asked: 2014-02-05

Advertise Here

1 Solution

LVL 18

Steven HarrisPresidentCommented: 2014-02-05

I am assuming that ShiftStart and ShiftEnd are single cells:

How about using an 'If' to check for non-blank value?

Private Sub Worksheet_Change(ByVal target As Range)
Dim cel As Range, targ As Range
Dim v As Variant
If target.Rows.Count >= Rows.Count Then Exit Sub

Set targ = Range("CallDuration")     'Watch these cells for time entries
Set targ = Intersect(targ, target)

    If Range("ShiftStart") And Range("ShiftEnd") <> "" Then  'Only proceed if not blank
        If Not targ Is Nothing Then
            Application.EnableEvents = False
            For Each cel In targ.Cells
                If IsNumeric(cel.Value) Then
                    If cel.Value > 0 Then
                        If Len(cel.Value) < 7 Then
                            On Error Resume Next
                            v = 0
                            v = TimeValue(Format(cel.Value, "00:00:0#"))
                            On Error GoTo 0
                            If v = 0 Then
                                cel.Select
                                MsgBox Format(cel.Value, "00:00:0#") & " is not a permissible time value!"
                                cel.ClearContents
                            Else
                                cel.NumberFormat = "hh:mm:ss"
                                cel.Value = v
                            End If
                        Else
                            cel.Select
                            MsgBox "Too many digits in " & cel.Value
                            cel.ClearContents
                        End If
                    Else
                        If cel.Value < 0 Then
                            cel.Select
                            MsgBox cel.Value & " is not a permissible time value"
                            cel.ClearContents
                        End If
                    End If
                Else
                    cel.Select
                    MsgBox cel.Value & " is not a permissible time value"
                    cel.ClearContents
                End If
            Next
            Application.EnableEvents = True
            
        End If
    Else
        a = MsgBox("Please evrify ShiftStart and ShiftEnd times", vbOKOnly, "ERROR") 'errorbox if ranges are blank
    End If
End Sub

Open in new window

LVL 1

GeekamoAuthor Commented: 2014-02-05

@ ThinkSpaceSolutions,

Thank you very much for taking the time to revise the VBA code. The solution didn't work for me, and kept producing the msgbox error message on any cell input - so something was out of whack.

It turns out this question was just solved over at: Q_28355662

I originally tried to accomplish this via the Data Validation feature, but after several tries - I realized my above VBA was conflicting with it. And I knew that the data validation I wanted to be applied, technically needed to live within the VBA code itself.

That being said, I assumed I needed to ask a new question on EE - which explains why I created this post/question. But it turns out, another EE expert who is very familiar with my workbook already - applied the revisions to the VBA code (code he created himself) - and it appears the revised code is working flawlessly.

I'm sure if I posted my workbook, along with my question here - that your revision to the code might have worked. Either way, I will still be awarding you the points.

Thank you very much!

~ Geekamo

LVL 18

Steven HarrisPresidentCommented: 2014-02-05

You are in good hands with byundt!

And looking back, I see where my error is in the solution I suggested. But yes, if a workbook was provided I would have caught that a bit sooner. Sorry about that, but I am glad byundt got you taken care of.

LVL 1

GeekamoAuthor Commented: 2014-02-05

@ ThinkSpaceSolutions,

I'm convinced byundt isn't human. He's too damn good! Out of the 109 questions I've posted here, I'd be willing to bet he's been involved with 95% of them - and I have yet to post a question that stumps him. I swear, he's not human! :-)

And yes, I'm sure you would have picked up on the error. You were coding blind. :-)

Still, thank you for the effort!

~ Geekamo

LVL 1

GeekamoAuthor Commented: 2014-02-17

Closing out question - solution located on other post.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Expand your skills with this month's free Premium course.

VBA - Evaluate Cells, then continue or end..

Join & Write a Comment Already a member? Login.

Featured Post