Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.
Solved
VBA - Evaluate Cells, then continue or end..
Posted on 2014-02-05
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...
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
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
5 Comments
I am assuming that ShiftStart and ShiftEnd are single cells:
How about using an 'If' to check for non-blank value?
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
@ 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
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
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.
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.
@ 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
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Suggested Courses
Course of the Month14 days, 20 hours left to enroll
649 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.