| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent users from saving if cells are blank
Dear all, I'm having a problem with a macro I wrote. I have an excel file
which needs to be filled out. I have used conditional formatting so that when for example C4 gets filled out, Cell D4 gets a blue color. Becasue users keep forgetting to fill out this blue colorded cell I want to prevent them from saving it, until they fill it out. But for some reason my code doesn't work at all. Saving is still possible when leaving the cells blank See code below Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If ActiveWorkbook.Range("D4").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D4") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True ElseIf ActiveWorkbook.Range("D6").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D6") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True End If End Sub Can anyone help me |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Prevent users from saving if cells are blank
Activeworkbook doesn't have a range.
Use If Acticeworkbook.Worksheets("Sheetname).Range("D4"). Value = "" OR _ Acticeworkbook.Worksheets("Sheetname).Range("D6"). Value = "" Then MsgBox ("Please fill out Cells D4 and/or D6 - highlighted in blue.") Cancel = True End If HTH, Bernie MS Excel MVP "JohnBlack" wrote in message ... Dear all, I'm having a problem with a macro I wrote. I have an excel file which needs to be filled out. I have used conditional formatting so that when for example C4 gets filled out, Cell D4 gets a blue color. Becasue users keep forgetting to fill out this blue colorded cell I want to prevent them from saving it, until they fill it out. But for some reason my code doesn't work at all. Saving is still possible when leaving the cells blank See code below Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If ActiveWorkbook.Range("D4").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D4") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True ElseIf ActiveWorkbook.Range("D6").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D6") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True End If End Sub Can anyone help me |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Prevent users from saving if cells are blank
Oops - I forgot a " Worksheets("Sheetname") in both places....
HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Activeworkbook doesn't have a range. Use If Acticeworkbook.Worksheets("Sheetname).Range("D4"). Value = "" OR _ Acticeworkbook.Worksheets("Sheetname).Range("D6"). Value = "" Then MsgBox ("Please fill out Cells D4 and/or D6 - highlighted in blue.") Cancel = True End If HTH, Bernie MS Excel MVP "JohnBlack" wrote in message ... Dear all, I'm having a problem with a macro I wrote. I have an excel file which needs to be filled out. I have used conditional formatting so that when for example C4 gets filled out, Cell D4 gets a blue color. Becasue users keep forgetting to fill out this blue colorded cell I want to prevent them from saving it, until they fill it out. But for some reason my code doesn't work at all. Saving is still possible when leaving the cells blank See code below Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If ActiveWorkbook.Range("D4").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D4") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True ElseIf ActiveWorkbook.Range("D6").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D6") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True End If End Sub Can anyone help me |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Prevent users from saving if cells are blank
Hi Bernie i changed my code and added a range, I left out the rest of your
solution as it disregards the fact that it should only show the message when the cell is highlighted blue and empty. The cell doesn't always have to be filled out. Only when it's highlighted blue. See new code below Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Sheet1").Range("D4").Interior.Color = RGB(0, 255, 255) And Worksheets("Sheet1").Range("D4") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True ElseIf Worksheets("Sheet1").Range("D6").Interior.Color = RGB(0, 255, 255) And Worksheets("Sheet1").Range("D6") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True End If End Sub But I can still save the file. The macro still isn't working "Bernie Deitrick" wrote: Oops - I forgot a " Worksheets("Sheetname") in both places.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Activeworkbook doesn't have a range. Use If Acticeworkbook.Worksheets("Sheetname).Range("D4"). Value = "" OR _ Acticeworkbook.Worksheets("Sheetname).Range("D6"). Value = "" Then MsgBox ("Please fill out Cells D4 and/or D6 - highlighted in blue.") Cancel = True End If HTH, Bernie MS Excel MVP "JohnBlack" wrote in message ... Dear all, I'm having a problem with a macro I wrote. I have an excel file which needs to be filled out. I have used conditional formatting so that when for example C4 gets filled out, Cell D4 gets a blue color. Becasue users keep forgetting to fill out this blue colorded cell I want to prevent them from saving it, until they fill it out. But for some reason my code doesn't work at all. Saving is still possible when leaving the cells blank See code below Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If ActiveWorkbook.Range("D4").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D4") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True ElseIf ActiveWorkbook.Range("D6").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D6") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True End If End Sub Can anyone help me |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Prevent users from saving if cells are blank
I would use ColorIndex instead of Color - Excel doesn't actually use all the bajillions of colors
that can be created with Color - it effectively bins the colors, so try IF Worksheets("Sheet1").Range("D4").Interior.ColorInd ex = 8 And _ Worksheets("Sheet1").Range("D4").Value = "" Then HTH, Bernie MS Excel MVP "JohnBlack" wrote in message ... Hi Bernie i changed my code and added a range, I left out the rest of your solution as it disregards the fact that it should only show the message when the cell is highlighted blue and empty. The cell doesn't always have to be filled out. Only when it's highlighted blue. See new code below Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Sheet1").Range("D4").Interior.Color = RGB(0, 255, 255) And Worksheets("Sheet1").Range("D4") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True ElseIf Worksheets("Sheet1").Range("D6").Interior.Color = RGB(0, 255, 255) And Worksheets("Sheet1").Range("D6") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True End If End Sub But I can still save the file. The macro still isn't working "Bernie Deitrick" wrote: Oops - I forgot a " Worksheets("Sheetname") in both places.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Activeworkbook doesn't have a range. Use If Acticeworkbook.Worksheets("Sheetname).Range("D4"). Value = "" OR _ Acticeworkbook.Worksheets("Sheetname).Range("D6"). Value = "" Then MsgBox ("Please fill out Cells D4 and/or D6 - highlighted in blue.") Cancel = True End If HTH, Bernie MS Excel MVP "JohnBlack" wrote in message ... Dear all, I'm having a problem with a macro I wrote. I have an excel file which needs to be filled out. I have used conditional formatting so that when for example C4 gets filled out, Cell D4 gets a blue color. Becasue users keep forgetting to fill out this blue colorded cell I want to prevent them from saving it, until they fill it out. But for some reason my code doesn't work at all. Saving is still possible when leaving the cells blank See code below Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If ActiveWorkbook.Range("D4").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D4") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True ElseIf ActiveWorkbook.Range("D6").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D6") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True End If End Sub Can anyone help me |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Prevent users from saving if cells are blank
Or use this, which is the basis of your CF:
Worksheets("Sheet1").Range("C4").Value < "" And Worksheets("Sheet1").Range("D4")..Value = "" Then -- HTH, Bernie MS Excel MVP "JohnBlack" wrote in message ... Hi Bernie i changed my code and added a range, I left out the rest of your solution as it disregards the fact that it should only show the message when the cell is highlighted blue and empty. The cell doesn't always have to be filled out. Only when it's highlighted blue. See new code below Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Sheet1").Range("D4").Interior.Color = RGB(0, 255, 255) And Worksheets("Sheet1").Range("D4") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True ElseIf Worksheets("Sheet1").Range("D6").Interior.Color = RGB(0, 255, 255) And Worksheets("Sheet1").Range("D6") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True End If End Sub But I can still save the file. The macro still isn't working "Bernie Deitrick" wrote: Oops - I forgot a " Worksheets("Sheetname") in both places.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Activeworkbook doesn't have a range. Use If Acticeworkbook.Worksheets("Sheetname).Range("D4"). Value = "" OR _ Acticeworkbook.Worksheets("Sheetname).Range("D6"). Value = "" Then MsgBox ("Please fill out Cells D4 and/or D6 - highlighted in blue.") Cancel = True End If HTH, Bernie MS Excel MVP "JohnBlack" wrote in message ... Dear all, I'm having a problem with a macro I wrote. I have an excel file which needs to be filled out. I have used conditional formatting so that when for example C4 gets filled out, Cell D4 gets a blue color. Becasue users keep forgetting to fill out this blue colorded cell I want to prevent them from saving it, until they fill it out. But for some reason my code doesn't work at all. Saving is still possible when leaving the cells blank See code below Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If ActiveWorkbook.Range("D4").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D4") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True ElseIf ActiveWorkbook.Range("D6").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D6") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True End If End Sub Can anyone help me |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Prevent users from saving if cells are blank
Changing Interior.Color to Interior.ColorIndex has no impact. The macro still
won't prevent me from saving. "Bernie Deitrick" wrote: Or use this, which is the basis of your CF: Worksheets("Sheet1").Range("C4").Value < "" And Worksheets("Sheet1").Range("D4")..Value = "" Then -- HTH, Bernie MS Excel MVP "JohnBlack" wrote in message ... Hi Bernie i changed my code and added a range, I left out the rest of your solution as it disregards the fact that it should only show the message when the cell is highlighted blue and empty. The cell doesn't always have to be filled out. Only when it's highlighted blue. See new code below Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("Sheet1").Range("D4").Interior.Color = RGB(0, 255, 255) And Worksheets("Sheet1").Range("D4") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True ElseIf Worksheets("Sheet1").Range("D6").Interior.Color = RGB(0, 255, 255) And Worksheets("Sheet1").Range("D6") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True End If End Sub But I can still save the file. The macro still isn't working "Bernie Deitrick" wrote: Oops - I forgot a " Worksheets("Sheetname") in both places.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Activeworkbook doesn't have a range. Use If Acticeworkbook.Worksheets("Sheetname).Range("D4"). Value = "" OR _ Acticeworkbook.Worksheets("Sheetname).Range("D6"). Value = "" Then MsgBox ("Please fill out Cells D4 and/or D6 - highlighted in blue.") Cancel = True End If HTH, Bernie MS Excel MVP "JohnBlack" wrote in message ... Dear all, I'm having a problem with a macro I wrote. I have an excel file which needs to be filled out. I have used conditional formatting so that when for example C4 gets filled out, Cell D4 gets a blue color. Becasue users keep forgetting to fill out this blue colorded cell I want to prevent them from saving it, until they fill it out. But for some reason my code doesn't work at all. Saving is still possible when leaving the cells blank See code below Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If ActiveWorkbook.Range("D4").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D4") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True ElseIf ActiveWorkbook.Range("D6").Interior.Color = RGB(0, 255, 255) And ActiveWorkbook.Range("D6") = "" Then MsgBox ("Please fill out the Blue highlighted cells.") Cancel = True End If End Sub Can anyone help me |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do you prevent users from changing certain cells? | Excel Worksheet Functions | |||
| How do I prevent saving an excel file if cells are blank? | Excel Worksheet Functions | |||
| Prevent Users from Selecting Cells | Excel Worksheet Functions | |||
| how do I prevent users to go into locked cells | Excel Discussion (Misc queries) | |||
| how do I prevent users to go into locked cells | Excel Discussion (Misc queries) | |||
Hybrid Mode
