Target value is text or anything
-
QUESTION: When I enter number: 77777777 into cell A1, then this triggers the following action on the spreadsheet: in that, the current time (ie 11:56 ) is then entered into cell: C1 . I am having difficulty entering a current time in cell D1 because I want the current time entering in cell D1 only when "specific text" is entered in cell B1 or perhaps: ANYTHING is entered in cell B1 . I am not sure how to compose the prog to achieve this. For the automatic entry of a current time in cell C1, the view code reads as follows: 'This Sub Is For: Inserting: The Current Time() in Cell C1 When 70000000 Number Is Entered In Cell A1 If Target.Count > 1 Then Exit Sub Set r = Range("A1") If Not Intersect(Target, r) Is Nothing Then On Error GoTo ErrHandler If IsNumeric(Target) Then If Target.Value = 70000000 Then s = Time() 'ElseIf Target.Value >= 1 And Target.Value End If If Len(Trim(s)) > 0 Then Application.EnableEvents = False Target.Offset(0, 2).Value = s End If End If End If Application.EnableEvents = True End Sub ................. I would actually prefer anything (text or numerics) to be entered in cell B1 (or just clicking into cell B1) to trigger the input of the current time in cell D1 Rod Whitehouse. ---- ANSWER: Rod Whitehouse, If Target.Count > 1 Then Exit Sub Set r = Range("A1") If Not Intersect(Target, r) Is Nothing Then On Error GoTo ErrHandler If IsNumeric(Target) Then If Target.Value = 70000000 Then s = Time() 'ElseIf Target.Value >= 1 And Target.Value End If If Len(Trim(s)) > 0 Then Application.EnableEvents = False Target.Offset(0, 2).Value = s End If End If End If ' new code added to your existing code if anything is entered in B1 If Target.Address = "$B$1" then if len(trim(Target.Text)) > 0 then Application.EnableEvents = False Range("d1").Value = time() Range("D1").Numberformat = "hh:mm:ss AM/PM" Application.EnableEvents = True end if end if end sub Application.EnableEvents = True End Sub ---------- FOLLOW-UP ---------- QUESTION: I think i got a response once and then it stopped responding - very strange? I am trying to attach the spreadsheet: C:\Documents and Settings\whitehw\Desktop\TIME10.xls ..... but I keep getting the following message: 'Image file is not set or is in the wrong format' ............. it will not allow me to attach the spreadsheet I entered the following prog into view code: If Target.Count > 1 Then Exit Sub Set r = Range("A1") If Not Intersect(Target, r) Is Nothing Then On Error GoTo ErrHandler If IsNumeric(Target) Then If Target.Value = 70000000 Then s = Time() 'ElseIf Target.Value >= 1 And Target.Value End If If Len(Trim(s)) > 0 Then Application.EnableEvents = False Target.Offset(0, 2).Value = s End If End If End If ' new code added to your existing code if anything is entered in B1 If Target.Address = "$B$1" Then If Len(Trim(Target.Text)) > 0 Then Application.EnableEvents = False Range("d1").Value = Time() Range("D1").NumberFormat = "hh:mm:ss AM/PM" Application.EnableEvents = True End If End If End Sub Application.EnableEvents = True End Sub Rod Whitehouse. - ANSWER: Rod Whitehouse, you can only attach pictures. You can't attachworkbooks. That is why you are getting that message. I would guess that your routine had an error and Events did not get reenabled. if you do Application.EnableEvents = False and then your code stops without executing an Application.EnableEvents = True then events won't be enabled. You have a statement that says On Error goto ErrHandler but you don't have an ErrHandler: statement in the code you show. So that statement would suppress and error, but it wouldn't handle the error. You didn't post a complete routine, so I don't know what code you have that you can't see. Also, I did nothing with your existing code because you never said it needed to be looked at and I couldn't look at it anyway because it didn't appear to exactly match the functionality you described. You existing code had nothing to do with B1 which is what you asked me about, so I just put in code after your code. You need to closeand then open it so events will be enabled or use code like this sub Startevents() Application.EnableEvents = True End sub put that in a module and run it whenever events seem to have stopped working. If Target.Count > 1 Then Exit Sub Set r = Range("A1") If Not Intersect(Target, r) Is Nothing Then On Error GoTo ErrHandler If IsNumeric(Target) Then If Target.Value = 70000000 Then s = Time() 'ElseIf Target.Value >= 1 And Target.Value End If If Len(Trim(s)) > 0 Then Application.EnableEvents = False Target.Offset(0, 2).Value = s End If End If End If ' new code added to your existing code if anything is entered in B1 If Target.Address = "$B$1" Then If Len(Trim(Target.Text)) > 0 Then Application.EnableEvents = False Range("d1").Value = Time() Range("D1").NumberFormat = "hh:mm:ss AM/PM" Application.EnableEvents = True End If End If End Sub ' perhaps add this line ErrHandler: Application.EnableEvents = True End Sub but during the development process when you are still testing your code, you should not be using error handlers or you won't know you have an error. ---------- FOLLOW-UP ---------- QUESTION: Thankyou very much for the time you spent on that one. When you use a numerical value as follows: If Target.Value >= 70000000 And Target.Value ...... Is it possible to use: specific text "" as the value instead of a number or number range and how would this then read in the above. Rod Whitehouse. ----
-
Answer:
Rod Whitehouse, Can you use a "greater than" or "Greater than or equal to" comparision with Text values. Yes you can. However, a text comparison begins with the first letter. If the first letter is larger than the first letter of the comparison string, then it moves on to the 2nd letter and so on until the comparison is complete. Here are some examples: ? "ABC" > "AB" True ? "AB " > "AB" True ? "AB" > "AB " False ? "AZ" > "AB" True ? "AA" > "AB" False ? "A" > "AB" False ? "AA" > "A" True ? "AZ" > "A" True Here is a little routine you can play with. It generates random sequences of 5 Uppercase letters and then tests them against some specified strings. Those that pass the test are accumulated and displayed in a message box at the end. Sub ABC() Dim i As Long, j As Long, s As String Dim s1 As String, l As Long For i = 1 To 500 s = "" For j = 1 To 5 l = Int(Rnd() * 26 + 65) s = s & Chr(l) Next If s >= "BBBBB" And s Next MsgBox Mid(s1, 2, Len(s1) - 1) End Sub or Sub ABC1() Dim i As Long, j As Long, s As String Dim s1 As String, l As Long For i = 1 To 500 s = "" For j = 1 To 5 l = Int(Rnd() * 26 + 65) s = s & Chr(l) Next If s >= "B" And s Next MsgBox Mid(s1, 2, Len(s1) - 1) End Sub >how would this then read in the above. I don't really know what you mean by that???
Miningco.com Visit the source
Related Q & A:
- How to read text off an embedded text resource?Best solution by Stack Overflow
- How to target the parent div from child div?Best solution by Stack Overflow
- What time does Target open?Best solution by Yahoo! Answers
- Why can I send a text to my email but not an email as a text to my phone?Best solution by Yahoo! Answers
- Can u return an item at target if you purchased it at target.com?Best solution by ChaCha
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.