Solved

VBA - Evaluate Cells, then continue or end..

Posted on 2014-02-05
5
514 Views
Last Modified: 2014-02-17
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
0
Comment
Question by:Geekamo
  • 3
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39837736
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

0
 
LVL 1

Accepted Solution

by:
Geekamo earned 0 total points
ID: 39837766
@ 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
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39837768
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.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39837772
@ 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
0
 
LVL 1

Author Closing Comment

by:Geekamo
ID: 39864347
Closing out question - solution located on other post.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now