Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
prevent blank cells in excel
Hai all, i have an excel sheet with different empty cells. i don't want the
user to save the file (or to go to another cell) without filling those particular cells. can anybody tell me how to do this? thanks in advance... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
prevent blank cells in excel
I'd use adjacent columns near the cells that require input and use a formula
like: =if(a1<"","","<--Please put something in this cell") Format it in big, bold, red letters. jojik wrote: Hai all, i have an excel sheet with different empty cells. i don't want the user to save the file (or to go to another cell) without filling those particular cells. can anybody tell me how to do this? thanks in advance... -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
prevent blank cells in excel
hai Dave, it is working but it doesn't look so good in the sheet. do you have
any other idea?? thanks for the response Dave. "Dave Peterson" wrote: I'd use adjacent columns near the cells that require input and use a formula like: =if(a1<"","","<--Please put something in this cell") Format it in big, bold, red letters. jojik wrote: Hai all, i have an excel sheet with different empty cells. i don't want the user to save the file (or to go to another cell) without filling those particular cells. can anybody tell me how to do this? thanks in advance... -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
prevent blank cells in excel
hai Dave, it is working but it doesn't look so good in the sheet. do you have
any other idea?? thanks for the response Dave. "Dave Peterson" wrote: I'd use adjacent columns near the cells that require input and use a formula like: =if(a1<"","","<--Please put something in this cell") Format it in big, bold, red letters. jojik wrote: Hai all, i have an excel sheet with different empty cells. i don't want the user to save the file (or to go to another cell) without filling those particular cells. can anybody tell me how to do this? thanks in advance... -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
prevent blank cells in excel
First, is there a problem if it doesn't look good if the workbook isn't saved?
It seems that if this is irritating to the user, it should make it an even better technique to make sure cells are filled in! But ... You could use an event macro that checks to see if the cells have something in them--if they don't then the save is canceled. But this kind of technique is easily bypassed by disabling macros or just disabling events. But if you want to try, this goes in the ThisWorkbook module (not a general module, not in a worksheet module): Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim myRanges As Variant Dim iCtr As Long myRanges = Array(Me.Worksheets("Sheet1").Range("a1:a3,b7,c9") , _ Me.Worksheets("Sheet2").Range("c1:c2"), _ Me.Worksheets("Sheet3").Range("x1")) For iCtr = LBound(myRanges) To UBound(myRanges) With myRanges(iCtr) If .Cells.Count < Application.CountA(.Cells) Then MsgBox "Please fill in all the cells in: " _ & .Parent.Name & vbLf & .Address(0, 0) Cancel = True Exit For End If End With Next iCtr End Sub You'll have to change the sheet names (probably not Sheet1, sheet2, sheet3) and you'll have to change the cell addresses for each of those sheets. If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) And when you (as a developer, not a user) want to save the workbook with those cells empty, you'll have to disable events, save the workbook, and reenable events. One way is to finish your edits. Open the VBE (use alt-f11) Show the immediate window (use ctrl-g) type this and hit enter: application.enableevents = false back to excel and save the workbook back to the VBE's immediate window and type this and hit enter: application.enableevents = false And this is the same thing anyone can use to save the workbook with empty cells. jojik wrote: hai Dave, it is working but it doesn't look so good in the sheet. do you have any other idea?? thanks for the response Dave. "Dave Peterson" wrote: I'd use adjacent columns near the cells that require input and use a formula like: =if(a1<"","","<--Please put something in this cell") Format it in big, bold, red letters. jojik wrote: Hai all, i have an excel sheet with different empty cells. i don't want the user to save the file (or to go to another cell) without filling those particular cells. can anybody tell me how to do this? thanks in advance... -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
prevent blank cells in excel
sorry dave i was not aware about the macros and VB, and passed your code to a
friend and he did it for me. and now it is working as i wished. thanks a lot... "Dave Peterson" wrote: First, is there a problem if it doesn't look good if the workbook isn't saved? It seems that if this is irritating to the user, it should make it an even better technique to make sure cells are filled in! But ... You could use an event macro that checks to see if the cells have something in them--if they don't then the save is canceled. But this kind of technique is easily bypassed by disabling macros or just disabling events. But if you want to try, this goes in the ThisWorkbook module (not a general module, not in a worksheet module): Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim myRanges As Variant Dim iCtr As Long myRanges = Array(Me.Worksheets("Sheet1").Range("a1:a3,b7,c9") , _ Me.Worksheets("Sheet2").Range("c1:c2"), _ Me.Worksheets("Sheet3").Range("x1")) For iCtr = LBound(myRanges) To UBound(myRanges) With myRanges(iCtr) If .Cells.Count < Application.CountA(.Cells) Then MsgBox "Please fill in all the cells in: " _ & .Parent.Name & vbLf & .Address(0, 0) Cancel = True Exit For End If End With Next iCtr End Sub You'll have to change the sheet names (probably not Sheet1, sheet2, sheet3) and you'll have to change the cell addresses for each of those sheets. If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) And when you (as a developer, not a user) want to save the workbook with those cells empty, you'll have to disable events, save the workbook, and reenable events. One way is to finish your edits. Open the VBE (use alt-f11) Show the immediate window (use ctrl-g) type this and hit enter: application.enableevents = false back to excel and save the workbook back to the VBE's immediate window and type this and hit enter: application.enableevents = false And this is the same thing anyone can use to save the workbook with empty cells. jojik wrote: hai Dave, it is working but it doesn't look so good in the sheet. do you have any other idea?? thanks for the response Dave. "Dave Peterson" wrote: I'd use adjacent columns near the cells that require input and use a formula like: =if(a1<"","","<--Please put something in this cell") Format it in big, bold, red letters. jojik wrote: Hai all, i have an excel sheet with different empty cells. i don't want the user to save the file (or to go to another cell) without filling those particular cells. can anybody tell me how to do this? thanks in advance... -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to prevent text overflow into adjacent blank cells | Excel Discussion (Misc queries) | |||
Prevent blank cells | Excel Discussion (Misc queries) | |||
How do I prevent saving an excel file if cells are blank? | Excel Worksheet Functions | |||
How can I prevent blank cells from charting? | Charts and Charting in Excel | |||
how to prevent blank cell copied when combining cells? | Excel Discussion (Misc queries) |