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 RangeDim v As VariantIf target.Rows.Count >= Rows.Count Then Exit SubSet targ = Range("CallDuration") 'Watch these cells for time entriesSet 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 = TrueEnd IfEnd Sub
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)
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 RangeDim v As VariantIf target.Rows.Count >= Rows.Count Then Exit SubSet targ = Range("CallDuration") 'Watch these cells for time entriesSet 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 IfEnd Sub
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.
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.
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. :-)
How about using an 'If' to check for non-blank value?
Open in new window