Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
force text in a cell based on value of cell in a range
Hello all.. new to this board, thx in advance for any help.. Here is what I'm trying to do. I'd like a piece of code that says the following. If any cell in a specific range is < 0 and the cell 7 columns before it, same row (the title of this cell is "description", is blank, i'd like a message box to come up telling the user they need to enter a description in that cell. Here is what I have so far.. I am sorta new to this VBA stuff, so if I'm way off, sorry... Private Sub worksheet_change(ByVal Target As Excel.Range) Dim result As Range For Each result In Range("FY04_reduction_totals") If result < 0 & result.Offset(0, 7) = "" Then MsgBox ("please enter a description") End If End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
force text in a cell based on value of cell in a range
Something like this should do the trick:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rngValidCells As Range Dim Rng As Range On Error Resume Next Set rngValidCells = Intersect(Target, "FY04_reduction_totals") If Not rngValidCells Is Nothing Then For Each Rng In rngValidCells If Rng.Value < 0 And Range.Offset(0, -7).Value = "" Then MsgBox "Please enter a description" End If Next Rng End If End Sub If the range "FY04_reduction_totals" is a column, it would be more robust to give a name to the column containing the descriptions, such as "FY04_descriptions" and replace Range.Offset(0, -7).Value with Intersect(Rng.EntireRow, Range("FY04_descriptions")).Value because the code doesn't rely on there being 6 columns between the two you're interested in (you can insert/delete columns without changing the code) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
force text in a cell based on value of cell in a range
Try:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Range("FY04_reduction_totals"), Target) Is Nothing Then If Target.Value < 0 & IsEmpty(Target.Offset(0, -7).Value) Then MsgBox ("please enter a description") End If End If End Sub -- HTH, Dianne In , jjst34 typed: Hello all.. new to this board, thx in advance for any help.. Here is what I'm trying to do. I'd like a piece of code that says the following. If any cell in a specific range is < 0 and the cell 7 columns before it, same row (the title of this cell is "description", is blank, i'd like a message box to come up telling the user they need to enter a description in that cell. Here is what I have so far.. I am sorta new to this VBA stuff, so if I'm way off, sorry... Private Sub worksheet_change(ByVal Target As Excel.Range) Dim result As Range For Each result In Range("FY04_reduction_totals") If result < 0 & result.Offset(0, 7) = "" Then MsgBox ("please enter a description") End If End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
force text in a cell based on value of cell in a range
& is a concatenation operator, it is not a boolean AND operator.
Also, the OP did say he/she wanted to check every cell in the range. -- Regards, Tom Ogilvy "Dianne" wrote in message ... Try: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Range("FY04_reduction_totals"), Target) Is Nothing Then If Target.Value < 0 & IsEmpty(Target.Offset(0, -7).Value) Then MsgBox ("please enter a description") End If End If End Sub -- HTH, Dianne In , jjst34 typed: Hello all.. new to this board, thx in advance for any help.. Here is what I'm trying to do. I'd like a piece of code that says the following. If any cell in a specific range is < 0 and the cell 7 columns before it, same row (the title of this cell is "description", is blank, i'd like a message box to come up telling the user they need to enter a description in that cell. Here is what I have so far.. I am sorta new to this VBA stuff, so if I'm way off, sorry... Private Sub worksheet_change(ByVal Target As Excel.Range) Dim result As Range For Each result In Range("FY04_reduction_totals") If result < 0 & result.Offset(0, 7) = "" Then MsgBox ("please enter a description") End If End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
force text in a cell based on value of cell in a range
re ampersand: Quite right -- copied and pasted OP's code without
checking everything. Any idea why my suggestion still works despite the ampersand? Does Excel "translate" the ampersand into an AND because it doesn't make sense in that context? Because both "clauses" evaluate to True, but the combined clauses still evaluate as True whereas I would expect a concatenation to be "TrueTrue" or something to that effect. re check every cell: Missed that. Thanks, Dianne In , Tom Ogilvy typed: & is a concatenation operator, it is not a boolean AND operator. Also, the OP did say he/she wanted to check every cell in the range. "Dianne" wrote in message ... Try: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Range("FY04_reduction_totals"), Target) Is Nothing Then If Target.Value < 0 & IsEmpty(Target.Offset(0, -7).Value) Then MsgBox ("please enter a description") End If End If End Sub -- HTH, Dianne In , jjst34 typed: Hello all.. new to this board, thx in advance for any help.. Here is what I'm trying to do. I'd like a piece of code that says the following. If any cell in a specific range is < 0 and the cell 7 columns before it, same row (the title of this cell is "description", is blank, i'd like a message box to come up telling the user they need to enter a description in that cell. Here is what I have so far.. I am sorta new to this VBA stuff, so if I'm way off, sorry... Private Sub worksheet_change(ByVal Target As Excel.Range) Dim result As Range For Each result In Range("FY04_reduction_totals") If result < 0 & result.Offset(0, 7) = "" Then MsgBox ("please enter a description") End If End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
force text in a cell based on value of cell in a range
concatenation is an arithmetic operator and takes precedence over a
comparison operator so If Target.Value < (0 & IsEmpty(Target.Offset(0, -7).Value) if Target.value < "0True" or if Target.Value < "0False" -- Regards, Tom Ogilvy "Dianne" wrote in message ... re ampersand: Quite right -- copied and pasted OP's code without checking everything. Any idea why my suggestion still works despite the ampersand? Does Excel "translate" the ampersand into an AND because it doesn't make sense in that context? Because both "clauses" evaluate to True, but the combined clauses still evaluate as True whereas I would expect a concatenation to be "TrueTrue" or something to that effect. re check every cell: Missed that. Thanks, Dianne In , Tom Ogilvy typed: & is a concatenation operator, it is not a boolean AND operator. Also, the OP did say he/she wanted to check every cell in the range. "Dianne" wrote in message ... Try: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Range("FY04_reduction_totals"), Target) Is Nothing Then If Target.Value < 0 & IsEmpty(Target.Offset(0, -7).Value) Then MsgBox ("please enter a description") End If End If End Sub -- HTH, Dianne In , jjst34 typed: Hello all.. new to this board, thx in advance for any help.. Here is what I'm trying to do. I'd like a piece of code that says the following. If any cell in a specific range is < 0 and the cell 7 columns before it, same row (the title of this cell is "description", is blank, i'd like a message box to come up telling the user they need to enter a description in that cell. Here is what I have so far.. I am sorta new to this VBA stuff, so if I'm way off, sorry... Private Sub worksheet_change(ByVal Target As Excel.Range) Dim result As Range For Each result In Range("FY04_reduction_totals") If result < 0 & result.Offset(0, 7) = "" Then MsgBox ("please enter a description") End If End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
force text in a cell based on value of cell in a range
Thanks for that explanation. I'll keep my eye out for situations where &
is combined with other operators. Dianne In , Tom Ogilvy typed: concatenation is an arithmetic operator and takes precedence over a comparison operator so If Target.Value < (0 & IsEmpty(Target.Offset(0, -7).Value) if Target.value < "0True" or if Target.Value < "0False" "Dianne" wrote in message ... re ampersand: Quite right -- copied and pasted OP's code without checking everything. Any idea why my suggestion still works despite the ampersand? Does Excel "translate" the ampersand into an AND because it doesn't make sense in that context? Because both "clauses" evaluate to True, but the combined clauses still evaluate as True whereas I would expect a concatenation to be "TrueTrue" or something to that effect. re check every cell: Missed that. Thanks, Dianne In , Tom Ogilvy typed: & is a concatenation operator, it is not a boolean AND operator. Also, the OP did say he/she wanted to check every cell in the range. "Dianne" wrote in message ... Try: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Range("FY04_reduction_totals"), Target) Is Nothing Then If Target.Value < 0 & IsEmpty(Target.Offset(0, -7).Value) Then MsgBox ("please enter a description") End If End If End Sub -- HTH, Dianne In , jjst34 typed: Hello all.. new to this board, thx in advance for any help.. Here is what I'm trying to do. I'd like a piece of code that says the following. If any cell in a specific range is < 0 and the cell 7 columns before it, same row (the title of this cell is "description", is blank, i'd like a message box to come up telling the user they need to enter a description in that cell. Here is what I have so far.. I am sorta new to this VBA stuff, so if I'm way off, sorry... Private Sub worksheet_change(ByVal Target As Excel.Range) Dim result As Range For Each result In Range("FY04_reduction_totals") If result < 0 & result.Offset(0, 7) = "" Then MsgBox ("please enter a description") End If End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
force text in a cell based on value of cell in a range
Private Sub worksheet_change(ByVal Target As Excel.Range)
Dim rng as Range Dim result As Range For Each result In Range("FY04_reduction_totals") If result < 0 And result.Offset(0, 7) = "" Then if rng is nothing then set rng = result else set rng = union(result,rng) end if End If Next if not rng is nothing then rng.Select rng.Msgbox "Please enter values in cells: " & rng.Address(0,0) End if End Sub -- Regards, Tom Ogilvy "jjst34" wrote in message ... Hello all.. new to this board, thx in advance for any help.. Here is what I'm trying to do. I'd like a piece of code that says the following. If any cell in a specific range is < 0 and the cell 7 columns before it, same row (the title of this cell is "description", is blank, i'd like a message box to come up telling the user they need to enter a description in that cell. Here is what I have so far.. I am sorta new to this VBA stuff, so if I'm way off, sorry... Private Sub worksheet_change(ByVal Target As Excel.Range) Dim result As Range For Each result In Range("FY04_reduction_totals") If result < 0 & result.Offset(0, 7) = "" Then MsgBox ("please enter a description") End If End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force entry into cell, based on validation selection in adjacent cell | Excel Worksheet Functions | |||
Force line break based on text | Excel Discussion (Misc queries) | |||
Force entry in one cell based on value of another cell | Excel Worksheet Functions | |||
How to force a wrap text function when the cell is big enough to p | Excel Discussion (Misc queries) | |||
How can I force certain text formatting in a cell? | Excel Discussion (Misc queries) |