|
popup alert in data validation
hello,
i need to set up an "indirect" error alert using DATA VALIDATION. please refer to the following link http://www.freefilehosting.net/download/3h8gj details are provided in the uploaded file. thank you, karl |
popup alert in data validation
Hi Karl
I don't understand why you are not prepared to have data Validation on cell D4. Apply DV and set it to allow Whole Number between Minimum 0 and Maximum 50 Set the message on the error alert tab to something like "The value in this cell must be less than 50" There will be no dropdown on the cell, but if anyone tries to enter a value greater than 50, they will be prevented and your message will pop up. -- Regards Roger Govier "karl" wrote in message ... hello, i need to set up an "indirect" error alert using DATA VALIDATION. please refer to the following link http://www.freefilehosting.net/download/3h8gj details are provided in the uploaded file. thank you, karl |
popup alert in data validation
the reason I don't want to validate D4 is that it is a sum of two numbers and
I won't be able to select that cell directly. I hope I answered your question. "Roger Govier" wrote: Hi Karl I don't understand why you are not prepared to have data Validation on cell D4. Apply DV and set it to allow Whole Number between Minimum 0 and Maximum 50 Set the message on the error alert tab to something like "The value in this cell must be less than 50" There will be no dropdown on the cell, but if anyone tries to enter a value greater than 50, they will be prevented and your message will pop up. -- Regards Roger Govier "karl" wrote in message ... hello, i need to set up an "indirect" error alert using DATA VALIDATION. please refer to the following link http://www.freefilehosting.net/download/3h8gj details are provided in the uploaded file. thank you, karl |
popup alert in data validation
Hi Karl
I now understand. You cannot use DV error box, unless it is applied to that cell. You could incorporate some VBA code to check it, and bring up an error message, but you need an event to trigger it. The following code would be triggered if you double click anywhere on the sheet. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub Copy the code above Right click on Sheet tabView CodePaste into the white pane. Alt+F11 to return to Excel -- Regards Roger Govier "karl" wrote in message ... the reason I don't want to validate D4 is that it is a sum of two numbers and I won't be able to select that cell directly. I hope I answered your question. "Roger Govier" wrote: Hi Karl I don't understand why you are not prepared to have data Validation on cell D4. Apply DV and set it to allow Whole Number between Minimum 0 and Maximum 50 Set the message on the error alert tab to something like "The value in this cell must be less than 50" There will be no dropdown on the cell, but if anyone tries to enter a value greater than 50, they will be prevented and your message will pop up. -- Regards Roger Govier "karl" wrote in message ... hello, i need to set up an "indirect" error alert using DATA VALIDATION. please refer to the following link http://www.freefilehosting.net/download/3h8gj details are provided in the uploaded file. thank you, karl |
popup alert in data validation
Hi Roger,
I must be doing something wrong. I hit alt f11 and pasted the code. the first sentence (Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)) is all red when i paste it. thanks. "Roger Govier" wrote: Hi Karl I now understand. You cannot use DV error box, unless it is applied to that cell. You could incorporate some VBA code to check it, and bring up an error message, but you need an event to trigger it. The following code would be triggered if you double click anywhere on the sheet. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub Copy the code above Right click on Sheet tabView CodePaste into the white pane. Alt+F11 to return to Excel -- Regards Roger Govier "karl" wrote in message ... the reason I don't want to validate D4 is that it is a sum of two numbers and I won't be able to select that cell directly. I hope I answered your question. "Roger Govier" wrote: Hi Karl I don't understand why you are not prepared to have data Validation on cell D4. Apply DV and set it to allow Whole Number between Minimum 0 and Maximum 50 Set the message on the error alert tab to something like "The value in this cell must be less than 50" There will be no dropdown on the cell, but if anyone tries to enter a value greater than 50, they will be prevented and your message will pop up. -- Regards Roger Govier "karl" wrote in message ... hello, i need to set up an "indirect" error alert using DATA VALIDATION. please refer to the following link http://www.freefilehosting.net/download/3h8gj details are provided in the uploaded file. thank you, karl |
popup alert in data validation
Hi Karl
The NG reader has wrapped it onto 2 lines. In the code window, hit the delete button at the end of line 1, and that will bring it all back to one line and it will change from Red to Black. or use the code posted below with a proper line break in it Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub -- Regards Roger Govier "karl" wrote in message ... Hi Roger, I must be doing something wrong. I hit alt f11 and pasted the code. the first sentence (Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)) is all red when i paste it. thanks. "Roger Govier" wrote: Hi Karl I now understand. You cannot use DV error box, unless it is applied to that cell. You could incorporate some VBA code to check it, and bring up an error message, but you need an event to trigger it. The following code would be triggered if you double click anywhere on the sheet. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub Copy the code above Right click on Sheet tabView CodePaste into the white pane. Alt+F11 to return to Excel -- Regards Roger Govier "karl" wrote in message ... the reason I don't want to validate D4 is that it is a sum of two numbers and I won't be able to select that cell directly. I hope I answered your question. "Roger Govier" wrote: Hi Karl I don't understand why you are not prepared to have data Validation on cell D4. Apply DV and set it to allow Whole Number between Minimum 0 and Maximum 50 Set the message on the error alert tab to something like "The value in this cell must be less than 50" There will be no dropdown on the cell, but if anyone tries to enter a value greater than 50, they will be prevented and your message will pop up. -- Regards Roger Govier "karl" wrote in message ... hello, i need to set up an "indirect" error alert using DATA VALIDATION. please refer to the following link http://www.freefilehosting.net/download/3h8gj details are provided in the uploaded file. thank you, karl |
popup alert in data validation
Roger, Thank you. I later realized that I had to double click on the cell in
order to get the popup window. please help me to have a popup window as soon as the wrong data is entered, without having to return to the cell and double clicking on it- just as the "ERROR" shows up in d6, in my original example. thank you in advance. "Roger Govier" wrote: Hi Karl The NG reader has wrapped it onto 2 lines. In the code window, hit the delete button at the end of line 1, and that will bring it all back to one line and it will change from Red to Black. or use the code posted below with a proper line break in it Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub -- Regards Roger Govier "karl" wrote in message ... Hi Roger, I must be doing something wrong. I hit alt f11 and pasted the code. the first sentence (Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)) is all red when i paste it. thanks. "Roger Govier" wrote: Hi Karl I now understand. You cannot use DV error box, unless it is applied to that cell. You could incorporate some VBA code to check it, and bring up an error message, but you need an event to trigger it. The following code would be triggered if you double click anywhere on the sheet. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub Copy the code above Right click on Sheet tabView CodePaste into the white pane. Alt+F11 to return to Excel -- Regards Roger Govier "karl" wrote in message ... the reason I don't want to validate D4 is that it is a sum of two numbers and I won't be able to select that cell directly. I hope I answered your question. "Roger Govier" wrote: Hi Karl I don't understand why you are not prepared to have data Validation on cell D4. Apply DV and set it to allow Whole Number between Minimum 0 and Maximum 50 Set the message on the error alert tab to something like "The value in this cell must be less than 50" There will be no dropdown on the cell, but if anyone tries to enter a value greater than 50, they will be prevented and your message will pop up. -- Regards Roger Govier "karl" wrote in message ... hello, i need to set up an "indirect" error alert using DATA VALIDATION. please refer to the following link http://www.freefilehosting.net/download/3h8gj details are provided in the uploaded file. thank you, karl |
popup alert in data validation
Hi Karl
We need an event to trigger the macro. From what you had said previously, the value in D4 is a formula, based upon entries in other cells Let me know what the formula is in D4, so I can see which cells it is dependent upon. -- Regards Roger Govier "karl" wrote in message ... Roger, Thank you. I later realized that I had to double click on the cell in order to get the popup window. please help me to have a popup window as soon as the wrong data is entered, without having to return to the cell and double clicking on it- just as the "ERROR" shows up in d6, in my original example. thank you in advance. "Roger Govier" wrote: Hi Karl The NG reader has wrapped it onto 2 lines. In the code window, hit the delete button at the end of line 1, and that will bring it all back to one line and it will change from Red to Black. or use the code posted below with a proper line break in it Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub -- Regards Roger Govier "karl" wrote in message ... Hi Roger, I must be doing something wrong. I hit alt f11 and pasted the code. the first sentence (Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)) is all red when i paste it. thanks. "Roger Govier" wrote: Hi Karl I now understand. You cannot use DV error box, unless it is applied to that cell. You could incorporate some VBA code to check it, and bring up an error message, but you need an event to trigger it. The following code would be triggered if you double click anywhere on the sheet. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub Copy the code above Right click on Sheet tabView CodePaste into the white pane. Alt+F11 to return to Excel -- Regards Roger Govier "karl" wrote in message ... the reason I don't want to validate D4 is that it is a sum of two numbers and I won't be able to select that cell directly. I hope I answered your question. "Roger Govier" wrote: Hi Karl I don't understand why you are not prepared to have data Validation on cell D4. Apply DV and set it to allow Whole Number between Minimum 0 and Maximum 50 Set the message on the error alert tab to something like "The value in this cell must be less than 50" There will be no dropdown on the cell, but if anyone tries to enter a value greater than 50, they will be prevented and your message will pop up. -- Regards Roger Govier "karl" wrote in message ... hello, i need to set up an "indirect" error alert using DATA VALIDATION. please refer to the following link http://www.freefilehosting.net/download/3h8gj details are provided in the uploaded file. thank you, karl |
popup alert in data validation
here we go Roger: http://www.freefilehosting.net/download/3hbeh
thanks. "Roger Govier" wrote: Hi Karl We need an event to trigger the macro. From what you had said previously, the value in D4 is a formula, based upon entries in other cells Let me know what the formula is in D4, so I can see which cells it is dependent upon. -- Regards Roger Govier "karl" wrote in message ... Roger, Thank you. I later realized that I had to double click on the cell in order to get the popup window. please help me to have a popup window as soon as the wrong data is entered, without having to return to the cell and double clicking on it- just as the "ERROR" shows up in d6, in my original example. thank you in advance. "Roger Govier" wrote: Hi Karl The NG reader has wrapped it onto 2 lines. In the code window, hit the delete button at the end of line 1, and that will bring it all back to one line and it will change from Red to Black. or use the code posted below with a proper line break in it Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub -- Regards Roger Govier "karl" wrote in message ... Hi Roger, I must be doing something wrong. I hit alt f11 and pasted the code. the first sentence (Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)) is all red when i paste it. thanks. "Roger Govier" wrote: Hi Karl I now understand. You cannot use DV error box, unless it is applied to that cell. You could incorporate some VBA code to check it, and bring up an error message, but you need an event to trigger it. The following code would be triggered if you double click anywhere on the sheet. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub Copy the code above Right click on Sheet tabView CodePaste into the white pane. Alt+F11 to return to Excel -- Regards Roger Govier "karl" wrote in message ... the reason I don't want to validate D4 is that it is a sum of two numbers and I won't be able to select that cell directly. I hope I answered your question. "Roger Govier" wrote: Hi Karl I don't understand why you are not prepared to have data Validation on cell D4. Apply DV and set it to allow Whole Number between Minimum 0 and Maximum 50 Set the message on the error alert tab to something like "The value in this cell must be less than 50" There will be no dropdown on the cell, but if anyone tries to enter a value greater than 50, they will be prevented and your message will pop up. -- Regards Roger Govier "karl" wrote in message ... hello, i need to set up an "indirect" error alert using DATA VALIDATION. please refer to the following link http://www.freefilehosting.net/download/3h8gj details are provided in the uploaded file. thank you, karl |
popup alert in data validation
Hi Karl
I was not thinking correctly. As there is a formula in D4, which will get recalculated when there is a change on the sheet, then you could use Private Sub Worksheet_Change(ByVal Target As Range) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub If for any reason the cell doesn't get calculated each time, then you could force volatility by changing the formula to =C1/C2+NOW()-NOW() -- Regards Roger Govier "karl" wrote in message ... here we go Roger: http://www.freefilehosting.net/download/3hbeh thanks. "Roger Govier" wrote: Hi Karl We need an event to trigger the macro. From what you had said previously, the value in D4 is a formula, based upon entries in other cells Let me know what the formula is in D4, so I can see which cells it is dependent upon. -- Regards Roger Govier "karl" wrote in message ... Roger, Thank you. I later realized that I had to double click on the cell in order to get the popup window. please help me to have a popup window as soon as the wrong data is entered, without having to return to the cell and double clicking on it- just as the "ERROR" shows up in d6, in my original example. thank you in advance. "Roger Govier" wrote: Hi Karl The NG reader has wrapped it onto 2 lines. In the code window, hit the delete button at the end of line 1, and that will bring it all back to one line and it will change from Red to Black. or use the code posted below with a proper line break in it Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub -- Regards Roger Govier "karl" wrote in message ... Hi Roger, I must be doing something wrong. I hit alt f11 and pasted the code. the first sentence (Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)) is all red when i paste it. thanks. "Roger Govier" wrote: Hi Karl I now understand. You cannot use DV error box, unless it is applied to that cell. You could incorporate some VBA code to check it, and bring up an error message, but you need an event to trigger it. The following code would be triggered if you double click anywhere on the sheet. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub Copy the code above Right click on Sheet tabView CodePaste into the white pane. Alt+F11 to return to Excel -- Regards Roger Govier "karl" wrote in message ... the reason I don't want to validate D4 is that it is a sum of two numbers and I won't be able to select that cell directly. I hope I answered your question. "Roger Govier" wrote: Hi Karl I don't understand why you are not prepared to have data Validation on cell D4. Apply DV and set it to allow Whole Number between Minimum 0 and Maximum 50 Set the message on the error alert tab to something like "The value in this cell must be less than 50" There will be no dropdown on the cell, but if anyone tries to enter a value greater than 50, they will be prevented and your message will pop up. -- Regards Roger Govier "karl" wrote in message ... hello, i need to set up an "indirect" error alert using DATA VALIDATION. please refer to the following link http://www.freefilehosting.net/download/3h8gj details are provided in the uploaded file. thank you, karl |
popup alert in data validation
hi roger,
thank you very much!! it works great. "Roger Govier" wrote: Hi Karl I was not thinking correctly. As there is a formula in D4, which will get recalculated when there is a change on the sheet, then you could use Private Sub Worksheet_Change(ByVal Target As Range) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub If for any reason the cell doesn't get calculated each time, then you could force volatility by changing the formula to =C1/C2+NOW()-NOW() -- Regards Roger Govier "karl" wrote in message ... here we go Roger: http://www.freefilehosting.net/download/3hbeh thanks. "Roger Govier" wrote: Hi Karl We need an event to trigger the macro. From what you had said previously, the value in D4 is a formula, based upon entries in other cells Let me know what the formula is in D4, so I can see which cells it is dependent upon. -- Regards Roger Govier "karl" wrote in message ... Roger, Thank you. I later realized that I had to double click on the cell in order to get the popup window. please help me to have a popup window as soon as the wrong data is entered, without having to return to the cell and double clicking on it- just as the "ERROR" shows up in d6, in my original example. thank you in advance. "Roger Govier" wrote: Hi Karl The NG reader has wrapped it onto 2 lines. In the code window, hit the delete button at the end of line 1, and that will bring it all back to one line and it will change from Red to Black. or use the code posted below with a proper line break in it Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub -- Regards Roger Govier "karl" wrote in message ... Hi Roger, I must be doing something wrong. I hit alt f11 and pasted the code. the first sentence (Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)) is all red when i paste it. thanks. "Roger Govier" wrote: Hi Karl I now understand. You cannot use DV error box, unless it is applied to that cell. You could incorporate some VBA code to check it, and bring up an error message, but you need an event to trigger it. The following code would be triggered if you double click anywhere on the sheet. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub Copy the code above Right click on Sheet tabView CodePaste into the white pane. Alt+F11 to return to Excel -- Regards Roger Govier "karl" wrote in message ... the reason I don't want to validate D4 is that it is a sum of two numbers and I won't be able to select that cell directly. I hope I answered your question. "Roger Govier" wrote: Hi Karl I don't understand why you are not prepared to have data Validation on cell D4. Apply DV and set it to allow Whole Number between Minimum 0 and Maximum 50 Set the message on the error alert tab to something like "The value in this cell must be less than 50" There will be no dropdown on the cell, but if anyone tries to enter a value greater than 50, they will be prevented and your message will pop up. -- Regards Roger Govier "karl" wrote in message ... hello, i need to set up an "indirect" error alert using DATA VALIDATION. please refer to the following link http://www.freefilehosting.net/download/3h8gj details are provided in the uploaded file. thank you, karl |
popup alert in data validation
Hi Roger,
Suppose I want to use that code for ten cells in one worksheet. I have tried to do so but couldn't accomplish much. I have attached an example as well. http://www.freefilehosting.net/download/3ibkh thanks "karl" wrote: hi roger, thank you very much!! it works great. "Roger Govier" wrote: Hi Karl I was not thinking correctly. As there is a formula in D4, which will get recalculated when there is a change on the sheet, then you could use Private Sub Worksheet_Change(ByVal Target As Range) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub If for any reason the cell doesn't get calculated each time, then you could force volatility by changing the formula to =C1/C2+NOW()-NOW() -- Regards Roger Govier "karl" wrote in message ... here we go Roger: http://www.freefilehosting.net/download/3hbeh thanks. "Roger Govier" wrote: Hi Karl We need an event to trigger the macro. From what you had said previously, the value in D4 is a formula, based upon entries in other cells Let me know what the formula is in D4, so I can see which cells it is dependent upon. -- Regards Roger Govier "karl" wrote in message ... Roger, Thank you. I later realized that I had to double click on the cell in order to get the popup window. please help me to have a popup window as soon as the wrong data is entered, without having to return to the cell and double clicking on it- just as the "ERROR" shows up in d6, in my original example. thank you in advance. "Roger Govier" wrote: Hi Karl The NG reader has wrapped it onto 2 lines. In the code window, hit the delete button at the end of line 1, and that will bring it all back to one line and it will change from Red to Black. or use the code posted below with a proper line break in it Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub -- Regards Roger Govier "karl" wrote in message ... Hi Roger, I must be doing something wrong. I hit alt f11 and pasted the code. the first sentence (Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)) is all red when i paste it. thanks. "Roger Govier" wrote: Hi Karl I now understand. You cannot use DV error box, unless it is applied to that cell. You could incorporate some VBA code to check it, and bring up an error message, but you need an event to trigger it. The following code would be triggered if you double click anywhere on the sheet. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub Copy the code above Right click on Sheet tabView CodePaste into the white pane. Alt+F11 to return to Excel -- Regards Roger Govier "karl" wrote in message ... the reason I don't want to validate D4 is that it is a sum of two numbers and I won't be able to select that cell directly. I hope I answered your question. "Roger Govier" wrote: Hi Karl I don't understand why you are not prepared to have data Validation on cell D4. Apply DV and set it to allow Whole Number between Minimum 0 and Maximum 50 Set the message on the error alert tab to something like "The value in this cell must be less than 50" There will be no dropdown on the cell, but if anyone tries to enter a value greater than 50, they will be prevented and your message will pop up. -- Regards Roger Govier "karl" wrote in message ... hello, i need to set up an "indirect" error alert using DATA VALIDATION. please refer to the following link http://www.freefilehosting.net/download/3h8gj details are provided in the uploaded file. thank you, karl |
popup alert in data validation
Hi Karl
try the following code to use with a Double Click event Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim myrange As Range, cell As Range Set myrange = Range("D4, I4, N4") '<=== Extend as necessary For Each cell In myrange If cell.Value 50 Then MsgBox "Error in cell " & cell.Address & vbCr _ & "Value too high" End If Next End Sub -- Regards Roger Govier "karl" wrote in message ... Hi Roger, Suppose I want to use that code for ten cells in one worksheet. I have tried to do so but couldn't accomplish much. I have attached an example as well. http://www.freefilehosting.net/download/3ibkh thanks "karl" wrote: hi roger, thank you very much!! it works great. "Roger Govier" wrote: Hi Karl I was not thinking correctly. As there is a formula in D4, which will get recalculated when there is a change on the sheet, then you could use Private Sub Worksheet_Change(ByVal Target As Range) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub If for any reason the cell doesn't get calculated each time, then you could force volatility by changing the formula to =C1/C2+NOW()-NOW() -- Regards Roger Govier "karl" wrote in message ... here we go Roger: http://www.freefilehosting.net/download/3hbeh thanks. "Roger Govier" wrote: Hi Karl We need an event to trigger the macro. From what you had said previously, the value in D4 is a formula, based upon entries in other cells Let me know what the formula is in D4, so I can see which cells it is dependent upon. -- Regards Roger Govier "karl" wrote in message ... Roger, Thank you. I later realized that I had to double click on the cell in order to get the popup window. please help me to have a popup window as soon as the wrong data is entered, without having to return to the cell and double clicking on it- just as the "ERROR" shows up in d6, in my original example. thank you in advance. "Roger Govier" wrote: Hi Karl The NG reader has wrapped it onto 2 lines. In the code window, hit the delete button at the end of line 1, and that will bring it all back to one line and it will change from Red to Black. or use the code posted below with a proper line break in it Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub -- Regards Roger Govier "karl" wrote in message ... Hi Roger, I must be doing something wrong. I hit alt f11 and pasted the code. the first sentence (Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)) is all red when i paste it. thanks. "Roger Govier" wrote: Hi Karl I now understand. You cannot use DV error box, unless it is applied to that cell. You could incorporate some VBA code to check it, and bring up an error message, but you need an event to trigger it. The following code would be triggered if you double click anywhere on the sheet. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range("D4").Value 50 Then MsgBox "Error in cell D4. Value too high" End If End Sub Copy the code above Right click on Sheet tabView CodePaste into the white pane. Alt+F11 to return to Excel -- Regards Roger Govier "karl" wrote in message ... the reason I don't want to validate D4 is that it is a sum of two numbers and I won't be able to select that cell directly. I hope I answered your question. "Roger Govier" wrote: Hi Karl I don't understand why you are not prepared to have data Validation on cell D4. Apply DV and set it to allow Whole Number between Minimum 0 and Maximum 50 Set the message on the error alert tab to something like "The value in this cell must be less than 50" There will be no dropdown on the cell, but if anyone tries to enter a value greater than 50, they will be prevented and your message will pop up. -- Regards Roger Govier "karl" wrote in message ... hello, i need to set up an "indirect" error alert using DATA VALIDATION. please refer to the following link http://www.freefilehosting.net/download/3h8gj details are provided in the uploaded file. thank you, karl |
| All times are GMT +1. The time now is 07:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com