Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next question
My code is not acting as expected and I would appreciate some help in
understanding why. (I know very little about VBA programming, so answers in the most basic terms would be helpful.) My task is to go through each unlocked cell on a worksheet and set the interior color based on the following criteria: If there is data validation and the"Show error" box is checked, the color should be light orange. All other unlocked cells should be light yellow. One of the problems I'm trying to work around is that if you test a cell for Validation.ShowError and there is no data validation on the cell, an error occurs. After reading other posts in this newsgroup, it seems there is no simple way to test if data validation exists that will generate a true/false answer, and the way around this is through handling the error. My code below attempts this. Every unlocked cell is turned yellow, and then I tried to change only the ones with Validation.ShowError = True to orange. Without the On Error Resume Next instruction, when the code encounters a cell that is unlocked but does not have data validation, I get an error at the second "if" clause. When I include the On Error Resume Next statement, the code turns every cell unlocked cell to orange, except for those where Validation.ShowError=False. (They stay yellow.) This is what I don't understand. Shouldn't the "next statement" after the second If clause errors out be "End If"? It seems as if the line that turns the color to orange is being treated like the next statement, even though I think it's part of the If clause. Any help or explanation would be appreciated. The code starts he ~~~~~~~~~~~~~~ Dim urRows As Integer, urCols As Integer Dim i As Integer, j As Integer Worksheets("Sheet1").Activate ActiveSheet.UsedRange.Select urRows = Selection.Rows.Count urCols = Selection.Columns.Count ActiveSheet.UsedRange.Cells(1, 1).Select For i = 1 To urRows For j = 1 To urCols On Error Resume Next ActiveSheet.UsedRange.Cells(i, j).Select If ActiveSheet.UsedRange.Cells(i, j).Locked = False Then ActiveSheet.UsedRange.Cells(i, j).Interior.Color = RGB(255, 255, 153) ' color = yellow If ActiveSheet.UsedRange.Cells(i, j).Validation.ShowError = True Then _ ActiveSheet.UsedRange.Cells(i, j).Interior.Color = RGB(255, 204, 153) ' color = orange End If Next j Next i ~~~~~~~~~~~ Thanks Alice |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next question
Dim urRows As Integer, urCols As Integer
Dim i As Integer, j As Integer Dim dType As String Worksheets("Sheet1").Activate With ActiveSheet urRows = .UsedRange.Rows.Count urCols = .UsedRange.Columns.Count For i = 1 To urRows For j = 1 To urCols With .UsedRange.Cells(i, j) If .Locked = False Then .Interior.Color = RGB(255, 255, 153) ' color = yellow dType = "" On Error Resume Next dType = .Validation.Type On Error GoTo 0 If dType < "" Then If .Validation.ShowError = True Then _ .Interior.Color = RGB(255, 204, 153) ' color = orange End If End If End With Next j Next i End With -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ups.com... My code is not acting as expected and I would appreciate some help in understanding why. (I know very little about VBA programming, so answers in the most basic terms would be helpful.) My task is to go through each unlocked cell on a worksheet and set the interior color based on the following criteria: If there is data validation and the"Show error" box is checked, the color should be light orange. All other unlocked cells should be light yellow. One of the problems I'm trying to work around is that if you test a cell for Validation.ShowError and there is no data validation on the cell, an error occurs. After reading other posts in this newsgroup, it seems there is no simple way to test if data validation exists that will generate a true/false answer, and the way around this is through handling the error. My code below attempts this. Every unlocked cell is turned yellow, and then I tried to change only the ones with Validation.ShowError = True to orange. Without the On Error Resume Next instruction, when the code encounters a cell that is unlocked but does not have data validation, I get an error at the second "if" clause. When I include the On Error Resume Next statement, the code turns every cell unlocked cell to orange, except for those where Validation.ShowError=False. (They stay yellow.) This is what I don't understand. Shouldn't the "next statement" after the second If clause errors out be "End If"? It seems as if the line that turns the color to orange is being treated like the next statement, even though I think it's part of the If clause. Any help or explanation would be appreciated. The code starts he ~~~~~~~~~~~~~~ Dim urRows As Integer, urCols As Integer Dim i As Integer, j As Integer Worksheets("Sheet1").Activate ActiveSheet.UsedRange.Select urRows = Selection.Rows.Count urCols = Selection.Columns.Count ActiveSheet.UsedRange.Cells(1, 1).Select For i = 1 To urRows For j = 1 To urCols On Error Resume Next ActiveSheet.UsedRange.Cells(i, j).Select If ActiveSheet.UsedRange.Cells(i, j).Locked = False Then ActiveSheet.UsedRange.Cells(i, j).Interior.Color = RGB(255, 255, 153) ' color = yellow If ActiveSheet.UsedRange.Cells(i, j).Validation.ShowError = True Then _ ActiveSheet.UsedRange.Cells(i, j).Interior.Color = RGB(255, 204, 153) ' color = orange End If Next j Next i ~~~~~~~~~~~ Thanks Alice |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next question
Alice
the piece of code shown is missing an End If inside the For Loops. Probably won't help ;-) You should surround the code that can generate the error with On Error Statements On Error Resume Next ' switch on error trapping ' code that can produce the error On Error Goto 0 ' switch off error trapping ' test for error condition As it stands, the first time you go through the loop you switch on error trapping and it stays on Regards Trevor wrote in message ups.com... My code is not acting as expected and I would appreciate some help in understanding why. (I know very little about VBA programming, so answers in the most basic terms would be helpful.) My task is to go through each unlocked cell on a worksheet and set the interior color based on the following criteria: If there is data validation and the"Show error" box is checked, the color should be light orange. All other unlocked cells should be light yellow. One of the problems I'm trying to work around is that if you test a cell for Validation.ShowError and there is no data validation on the cell, an error occurs. After reading other posts in this newsgroup, it seems there is no simple way to test if data validation exists that will generate a true/false answer, and the way around this is through handling the error. My code below attempts this. Every unlocked cell is turned yellow, and then I tried to change only the ones with Validation.ShowError = True to orange. Without the On Error Resume Next instruction, when the code encounters a cell that is unlocked but does not have data validation, I get an error at the second "if" clause. When I include the On Error Resume Next statement, the code turns every cell unlocked cell to orange, except for those where Validation.ShowError=False. (They stay yellow.) This is what I don't understand. Shouldn't the "next statement" after the second If clause errors out be "End If"? It seems as if the line that turns the color to orange is being treated like the next statement, even though I think it's part of the If clause. Any help or explanation would be appreciated. The code starts he ~~~~~~~~~~~~~~ Dim urRows As Integer, urCols As Integer Dim i As Integer, j As Integer Worksheets("Sheet1").Activate ActiveSheet.UsedRange.Select urRows = Selection.Rows.Count urCols = Selection.Columns.Count ActiveSheet.UsedRange.Cells(1, 1).Select For i = 1 To urRows For j = 1 To urCols On Error Resume Next ActiveSheet.UsedRange.Cells(i, j).Select If ActiveSheet.UsedRange.Cells(i, j).Locked = False Then ActiveSheet.UsedRange.Cells(i, j).Interior.Color = RGB(255, 255, 153) ' color = yellow If ActiveSheet.UsedRange.Cells(i, j).Validation.ShowError = True Then _ ActiveSheet.UsedRange.Cells(i, j).Interior.Color = RGB(255, 204, 153) ' color = orange End If Next j Next i ~~~~~~~~~~~ Thanks Alice |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next question
On Sep 21, 4:39 pm, "Bob Phillips" wrote:
Dim urRows As Integer, urCols As Integer Dim i As Integer, j As Integer Dim dType As String Worksheets("Sheet1").Activate With ActiveSheet urRows = .UsedRange.Rows.Count urCols = .UsedRange.Columns.Count For i = 1 To urRows For j = 1 To urCols With .UsedRange.Cells(i, j) If .Locked = False Then .Interior.Color = RGB(255, 255, 153) ' color = yellow dType = "" On Error Resume Next dType = .Validation.Type On Error GoTo 0 If dType < "" Then If .Validation.ShowError = True Then _ .Interior.Color = RGB(255, 204, 153) ' color = orange End If End If End With Next j Next i End With -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) Thank you. That does work! And also, for the very first time, I think I understand how the "With" is supposed to work. Your example is very concise and easy to follow. Alice |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next question
On Sep 21, 4:51 pm, "Trevor Shuttleworth"
wrote: Alice the piece of code shown is missing an End If inside the For Loops. Probably won't help ;-) You should surround the code that can generate the error with On Error Statements On Error Resume Next ' switch on error trapping ' code that can produce the error On Error Goto 0 ' switch off error trapping ' test for error condition As it stands, the first time you go through the loop you switch on error trapping and it stays on Regards Trevor wrote in message ups.com... Thanks, Trevor. I see that Bob Phillips example had the two error traps as you stated, but I didn't know why. I often feel that VBA is like a foreign language where I just don't know enough words. Things almost make sense, and after a lot of labor, I can usually get close to what I want, but the fine details still elude me! Alice |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next question
Alice,
This doesn't show you anything about error capture but it is an alternate approach to your task. _________________________________ Sub test() Dim rngValidation As Range Dim rngTemp As Range Set rngValidation = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllVa lidation) For Each rngTemp In ActiveSheet.UsedRange.Cells If Not rngTemp.Locked Then If Application.Intersect(rngTemp, rngValidation) Is Nothing Then rngTemp.Interior.Color = RGB(255, 255, 153) Else If rngTemp.Validation.ShowError Then rngTemp.Interior.Color = RGB(255, 204, 153) Else rngTemp.Interior.Color = RGB(255, 255, 153) End If End If End If Next rngTemp End Sub _________________________________ Steve wrote in message ups.com... My code is not acting as expected and I would appreciate some help in understanding why. (I know very little about VBA programming, so answers in the most basic terms would be helpful.) My task is to go through each unlocked cell on a worksheet and set the interior color based on the following criteria: If there is data validation and the"Show error" box is checked, the color should be light orange. All other unlocked cells should be light yellow. One of the problems I'm trying to work around is that if you test a cell for Validation.ShowError and there is no data validation on the cell, an error occurs. After reading other posts in this newsgroup, it seems there is no simple way to test if data validation exists that will generate a true/false answer, and the way around this is through handling the error. My code below attempts this. Every unlocked cell is turned yellow, and then I tried to change only the ones with Validation.ShowError = True to orange. Without the On Error Resume Next instruction, when the code encounters a cell that is unlocked but does not have data validation, I get an error at the second "if" clause. When I include the On Error Resume Next statement, the code turns every cell unlocked cell to orange, except for those where Validation.ShowError=False. (They stay yellow.) This is what I don't understand. Shouldn't the "next statement" after the second If clause errors out be "End If"? It seems as if the line that turns the color to orange is being treated like the next statement, even though I think it's part of the If clause. Any help or explanation would be appreciated. The code starts he ~~~~~~~~~~~~~~ Dim urRows As Integer, urCols As Integer Dim i As Integer, j As Integer Worksheets("Sheet1").Activate ActiveSheet.UsedRange.Select urRows = Selection.Rows.Count urCols = Selection.Columns.Count ActiveSheet.UsedRange.Cells(1, 1).Select For i = 1 To urRows For j = 1 To urCols On Error Resume Next ActiveSheet.UsedRange.Cells(i, j).Select If ActiveSheet.UsedRange.Cells(i, j).Locked = False Then ActiveSheet.UsedRange.Cells(i, j).Interior.Color = RGB(255, 255, 153) ' color = yellow If ActiveSheet.UsedRange.Cells(i, j).Validation.ShowError = True Then _ ActiveSheet.UsedRange.Cells(i, j).Interior.Color = RGB(255, 204, 153) ' color = orange End If Next j Next i ~~~~~~~~~~~ Thanks Alice |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next question
Hi Alice
Thanks for the feedback. I guess VBA is one of those things where you just keep learning new tricks all the time ;-) With regard to Bob's code: ' set the variable dType to blank (a known state, not carried forward from previous test(s)) dType = "" ' switch on Error Trapping - so that the code will continue to the next line whatever happens On Error Resume Next ' set dType to the Validation Type for the cell ... which will cause an error if there isn't any dType = .Validation.Type ' switch off Error Trapping ... and, in this case,is quite nicely the *next line* for the Resume Next On Error GoTo 0 ' test the dType variable which will be blank if there was no Validation (having pre-set it above) If dType < "" Then Hope that makes it clearer. You should always keep the error trapping range to a minimum as other errors could be missed and, although it means your code won't fail, you may not get the results you expect. Regards Trevor wrote in message ups.com... On Sep 21, 4:51 pm, "Trevor Shuttleworth" wrote: Alice the piece of code shown is missing an End If inside the For Loops. Probably won't help ;-) You should surround the code that can generate the error with On Error Statements On Error Resume Next ' switch on error trapping ' code that can produce the error On Error Goto 0 ' switch off error trapping ' test for error condition As it stands, the first time you go through the loop you switch on error trapping and it stays on Regards Trevor wrote in message ups.com... Thanks, Trevor. I see that Bob Phillips example had the two error traps as you stated, but I didn't know why. I often feel that VBA is like a foreign language where I just don't know enough words. Things almost make sense, and after a lot of labor, I can usually get close to what I want, but the fine details still elude me! Alice |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next question
.Interior.Color = RGB(255, 255, 153)
Just throwing out an idea if you are using Xl 2007 If Not .Locked Then .Interior.Color = XlRgbColor.rgbYellow If .Validation.ShowError Then _ .Interior.Color = XlRgbColor.rgbOrange On Error Resume Next dType = .Validation.Type Here's an alternative when working with Validation: Sub Demo() Dim RngValidation As Range Dim Cell As Range Const Orange = XlRgbColor.rgbOrange Set RngValidation = Cells.SpecialCells(xlCellTypeAllValidation) For Each Cell In RngValidation.Cells If Cell.Validation.ShowError Then Cell.Interior.Color = Orange Next Cell End Sub -- HTH :) Dana DeLouis Windows XP & Excel 2007 wrote in message oups.com... On Sep 21, 4:39 pm, "Bob Phillips" wrote: Dim urRows As Integer, urCols As Integer Dim i As Integer, j As Integer Dim dType As String Worksheets("Sheet1").Activate With ActiveSheet urRows = .UsedRange.Rows.Count urCols = .UsedRange.Columns.Count For i = 1 To urRows For j = 1 To urCols With .UsedRange.Cells(i, j) If .Locked = False Then .Interior.Color = RGB(255, 255, 153) ' color = yellow dType = "" On Error Resume Next dType = .Validation.Type On Error GoTo 0 If dType < "" Then If .Validation.ShowError = True Then _ .Interior.Color = RGB(255, 204, 153) ' color = orange End If End If End With Next j Next i End With -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) Thank you. That does work! And also, for the very first time, I think I understand how the "With" is supposed to work. Your example is very concise and easy to follow. Alice |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next question
Steve:
This is sweet! I knew there had to be some way to "ask" Excel if a cell had data validation, because I found the instructions about applying data validation that say you need to "modify" a cell if data validation already exists, otherwise you have to "add". It didn't make any sense that there was no way to query a cell about it's data validation status. It looks like this "SpecialCells" method provides a way. And the "Intersection" method is something that looks very handy indeed. I'm sure I will be using that a lot, now that my eyes have been opened. Thanks so much for suggesting this different solution to the problem. Alice (aka night_writer) "Steve Yandl" wrote in message . .. Alice, This doesn't show you anything about error capture but it is an alternate approach to your task. _________________________________ Sub test() Dim rngValidation As Range Dim rngTemp As Range Set rngValidation = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllVa lidation) For Each rngTemp In ActiveSheet.UsedRange.Cells If Not rngTemp.Locked Then If Application.Intersect(rngTemp, rngValidation) Is Nothing Then rngTemp.Interior.Color = RGB(255, 255, 153) Else If rngTemp.Validation.ShowError Then rngTemp.Interior.Color = RGB(255, 204, 153) Else rngTemp.Interior.Color = RGB(255, 255, 153) End If End If End If Next rngTemp End Sub _________________________________ Steve |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next question
Dana:
Thanks for your additional suggestion. I see the handy "SpecialCells" method shows up in your post as well as the one from Steve Yandl. This is going to be very helpful for me on the current problem and going forward. Thanks so much for your input! Alice (aka night_writer) "Dana DeLouis" wrote in message ... .Interior.Color = RGB(255, 255, 153) Just throwing out an idea if you are using Xl 2007 If Not .Locked Then .Interior.Color = XlRgbColor.rgbYellow If .Validation.ShowError Then _ .Interior.Color = XlRgbColor.rgbOrange On Error Resume Next dType = .Validation.Type Here's an alternative when working with Validation: Sub Demo() Dim RngValidation As Range Dim Cell As Range Const Orange = XlRgbColor.rgbOrange Set RngValidation = Cells.SpecialCells(xlCellTypeAllValidation) For Each Cell In RngValidation.Cells If Cell.Validation.ShowError Then Cell.Interior.Color = Orange Next Cell End Sub -- HTH :) Dana DeLouis Windows XP & Excel 2007 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error Resume Next question
Alice,
You're welcome. I have a hard time remembering to look at which types of cells can be isolated using SpecialCells but it can be a very handy tool. The 'Intersection' method was something I ignored until the first time I decided I had to use it and realized how many earlier tasks could have been made easier if I'd researched a bit sooner. There is a similar 'Union' method that allows you to combine ranges into a bigger range. We could have cycled through the cells in the UsedRange and used 'Union' to build a new range consisting of all the unlocked cells and then used the Intersect method to find cells that were both unlocked and set for data validation but I thought the nested If...Then...Else statements might be easier to follow. Steve "my-wings" wrote in message ... Steve: This is sweet! I knew there had to be some way to "ask" Excel if a cell had data validation, because I found the instructions about applying data validation that say you need to "modify" a cell if data validation already exists, otherwise you have to "add". It didn't make any sense that there was no way to query a cell about it's data validation status. It looks like this "SpecialCells" method provides a way. And the "Intersection" method is something that looks very handy indeed. I'm sure I will be using that a lot, now that my eyes have been opened. Thanks so much for suggesting this different solution to the problem. Alice (aka night_writer) "Steve Yandl" wrote in message . .. Alice, This doesn't show you anything about error capture but it is an alternate approach to your task. _________________________________ Sub test() Dim rngValidation As Range Dim rngTemp As Range Set rngValidation = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllVa lidation) For Each rngTemp In ActiveSheet.UsedRange.Cells If Not rngTemp.Locked Then If Application.Intersect(rngTemp, rngValidation) Is Nothing Then rngTemp.Interior.Color = RGB(255, 255, 153) Else If rngTemp.Validation.ShowError Then rngTemp.Interior.Color = RGB(255, 204, 153) Else rngTemp.Interior.Color = RGB(255, 255, 153) End If End If End If Next rngTemp End Sub _________________________________ Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
on error resume next | Excel Programming | |||
Resume on Error? | Excel Programming | |||
On Error {...} Resume Next | Excel Programming | |||
On error resume next? question - problem | Excel Programming | |||
"On Error Resume Next" Question | Excel Programming |