We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
5 days, 23 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Suggested Courses
Course of the Month5 days, 23 hours left to enroll
707 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.