Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Making cells mandatory to fill in
I am creating an application form where I want users to fill certain cells in
before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#2
|
|||
|
|||
Hi
following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#3
|
|||
|
|||
I was given this code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaツ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreツ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#4
|
|||
|
|||
Hi Rob
to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolea*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addre*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#5
|
|||
|
|||
It doesn't say "This Workbook" anywhere... the VBE screen is completely
blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaツ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreツ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#6
|
|||
|
|||
Hi Rob
from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolea*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addre*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#7
|
|||
|
|||
It totally worked, thank you!!!!!!!!!!!!!!!!
"JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaツ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreツ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#8
|
|||
|
|||
you're welcome ... now make sure you've set security to medium (and choose
enable macros when you open the workbook) or it won't work if you close the book (tools / macro / security tab) Cheers JulieD "abfabrob" wrote in message ... It totally worked, thank you!!!!!!!!!!!!!!!! "JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolea*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addre*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
if i want when A1 is entered, B1 must be entered also.
then when A2 has to be entered, B2 has to be entered too. then... if the forumla is apply to B3,B4&b5 but i only need uers to fill in A3 first, A4&A5 will be filled next time. then it will keep promp users to enter B4&B5 which I dont want. any helps? thanks! "JulieD" wrote: you're welcome ... now make sure you've set security to medium (and choose enable macros when you open the workbook) or it won't work if you close the book (tools / macro / security tab) Cheers JulieD "abfabrob" wrote in message ... It totally worked, thank you!!!!!!!!!!!!!!!! "JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaツ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreツ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
any tips?
"kyoshirou" wrote: if i want when A1 is entered, B1 must be entered also. then when A2 has to be entered, B2 has to be entered too. then... if the forumla is apply to B3,B4&b5 but i only need uers to fill in A3 first, A4&A5 will be filled next time. then it will keep promp users to enter B4&B5 which I dont want. any helps? thanks! "JulieD" wrote: you're welcome ... now make sure you've set security to medium (and choose enable macros when you open the workbook) or it won't work if you close the book (tools / macro / security tab) Cheers JulieD "abfabrob" wrote in message ... It totally worked, thank you!!!!!!!!!!!!!!!! "JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaツ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreツ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
Hi Julie,
I have added your code to my spreadsheet and it does force the user to populate the required cells. However, I need the cells to be blank when other users open the spreadsheet and for them to be forced to populate the cells before they save a copy. Do you know how I can save the spreadsheet having added the code, without populating the fields myself? Thanks "JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaツ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreツ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
Click on the Design Mode button so all code is disabled long enough for you to save the sheet. That drove me nuts for weeks once... mdtuckett;173637 Wrote: I have added your code to my spreadsheet and it does force the user to populate the required cells. However, I need the cells to be blank when other users open the spreadsheet and for them to be forced to populate the cells before they save a copy. Do you know how I can save the spreadsheet having added the code, without populating the fields myself? Thanks -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48067 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
You will have to save the workbook as a Template with the beforesave code
and the required cells empty. To do this, add the code to Thisworkbook.........I think you already did that. Leave the required cells empty then save the workbook with events disabled. Application.EnableEvents = False entered into the Immediate Window will do that for you. Save the workbook as a FileType of Template(*.xlt) Now close the workbook. You will not be asked to fill in the cells Go back to the Immediate window and re-enable events by making them = True. From now on when users open a new workbook based upon the Template, they will have to fill in the cells before saving. Gord Dibben MS Excel MVP On Thu, 8 Jan 2009 07:04:47 -0800, mdtuckett wrote: Hi Julie, I have added your code to my spreadsheet and it does force the user to populate the required cells. However, I need the cells to be blank when other users open the spreadsheet and for them to be forced to populate the cells before they save a copy. Do you know how I can save the spreadsheet having added the code, without populating the fields myself? Thanks "JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolea*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addre*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
Julie, I tried the code you provided and it worked perfectly. However my
problem is slightly different. I would like users to enter an X in one of cells E6, E7, E8, E9 or E10. Any one only. How can I change the code below to accept an X in one cell only? Thanks. Ben Joseph "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaツ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreツ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
I need help, I am working on an excel expense report templete and want them
to fill in certain cells before they exit. When I copied the thread from below I keep getting error messages. I dont know if I am putting valoues of the cells i want filled out in the wrong place or not. This is what it looks like: it says for each cell in range line is not right... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim cell As Range For Each cell In Range(d5, g5, j5, e7, m7, g10) If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell End Sub -- wkmooreh "abfabrob" wrote: I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
You'll want to provide the worksheet name to check, too.
Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim cell As Range For Each cell In Me.Worksheets("Sheet1").Range("d5,g5,j5,e7,m7,g10" ) If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell End Sub wkmooreh wrote: I need help, I am working on an excel expense report templete and want them to fill in certain cells before they exit. When I copied the thread from below I keep getting error messages. I dont know if I am putting valoues of the cells i want filled out in the wrong place or not. This is what it looks like: it says for each cell in range line is not right... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim cell As Range For Each cell In Range(d5, g5, j5, e7, m7, g10) If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell End Sub -- wkmooreh "abfabrob" wrote: I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! -- Dave Peterson |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
Dave Thanks.. this is what i put. i saved it and closed it out , when back in
and it isnt forcing doing anything... I am sorry for the confusion, I did this years ago. But none of it has come back to me at all Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim cell As Range For Each cell In Expense_Report__BLANK.xls("MASTER").Range("d5,g5,j 5,e7,m7,g10") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell End Sub -- w "wkmooreh" wrote: I need help, I am working on an excel expense report templete and want them to fill in certain cells before they exit. When I copied the thread from below I keep getting error messages. I dont know if I am putting valoues of the cells i want filled out in the wrong place or not. This is what it looks like: it says for each cell in range line is not right... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim cell As Range For Each cell In Range(d5, g5, j5, e7, m7, g10) If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell End Sub -- wkmooreh "abfabrob" wrote: I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
Try this:
For Each cell In me.worksheets("MASTER").Range("d5,g5,j5,e7,m7,g10" ) Me refers to the object that owns the code. In this case, since the code is in the ThisWorkbook module, the Me keyword refers to the workbook that's closing. And in this case, it doesn't matter, but I like this syntax: For Each cell In me.worksheets("MASTER").Range("d5,g5,j5,e7,m7,g10" ).cells I think it makes it more self-documenting. wkmooreh wrote: Dave Thanks.. this is what i put. i saved it and closed it out , when back in and it isnt forcing doing anything... I am sorry for the confusion, I did this years ago. But none of it has come back to me at all Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim cell As Range For Each cell In Expense_Report__BLANK.xls("MASTER").Range("d5,g5,j 5,e7,m7,g10") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell End Sub -- w "wkmooreh" wrote: I need help, I am working on an excel expense report templete and want them to fill in certain cells before they exit. When I copied the thread from below I keep getting error messages. I dont know if I am putting valoues of the cells i want filled out in the wrong place or not. This is what it looks like: it says for each cell in range line is not right... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim cell As Range For Each cell In Range(d5, g5, j5, e7, m7, g10) If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell End Sub -- wkmooreh "abfabrob" wrote: I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! -- Dave Peterson |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
Ok Dave - Now it works to perfect, I cant save and close it without it
wanting me to fill in the required cells.. What can I do Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim cell As Range For Each cell In Me.Worksheets("MASTER").Range("d5,g5,j5,e7,m7,g10" ) If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell End Sub -- w "wkmooreh" wrote: I need help, I am working on an excel expense report templete and want them to fill in certain cells before they exit. When I copied the thread from below I keep getting error messages. I dont know if I am putting valoues of the cells i want filled out in the wrong place or not. This is what it looks like: it says for each cell in range line is not right... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim cell As Range For Each cell In Range(d5, g5, j5, e7, m7, g10) If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell End Sub -- wkmooreh "abfabrob" wrote: I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
So you want to give you (as a developer) special dispensation and allow yourself
to save and close the workbook with those cells empty. You have a few choices. I'd do this: Open the VBE Hit ctrl-g to see the immediate window type this and hit enter: application.enableevents = false Then back to excel and save/close the workbook. Then back to the immediate window in the VBE: application.enableevents = true The workbook_BeforeSave procedure is one of those events that excel keeps looking for. By telling it to stop looking at all events, you can do what you want. Notice that this isn't difficult to do. Any one who knows a little excel can do exactly the same thing. So your _BeforeSave event isn't really secure. ========= If you are only using this workbook as a developer and never want to have this code stop you from saving, you can add a couple of lines to check to see who's using the workbook: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim cell As Range If Application.UserName = "Your Name Here" Then Exit Sub End If For Each cell In Me.Worksheets("Sheet1").Range("d5,g5,j5,e7,m7,g10" ).Cells If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell End Sub To make sure you spell your username correctly, type this into the immediate window: ?application.username And use that name in your code. wkmooreh wrote: Ok Dave - Now it works to perfect, I cant save and close it without it wanting me to fill in the required cells.. What can I do Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim cell As Range For Each cell In Me.Worksheets("MASTER").Range("d5,g5,j5,e7,m7,g10" ) If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell End Sub -- w "wkmooreh" wrote: I need help, I am working on an excel expense report templete and want them to fill in certain cells before they exit. When I copied the thread from below I keep getting error messages. I dont know if I am putting valoues of the cells i want filled out in the wrong place or not. This is what it looks like: it says for each cell in range line is not right... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim cell As Range For Each cell In Range(d5, g5, j5, e7, m7, g10) If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell End Sub -- wkmooreh "abfabrob" wrote: I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! -- Dave Peterson |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
Gord--I followed these instructions to save the workbook template but still
get the message that the cells need to be filled out. I cannot even click Save-As--the message pops up immediately. Any thoughts? "Gord Dibben" wrote: You will have to save the workbook as a Template with the beforesave code and the required cells empty. To do this, add the code to Thisworkbook.........I think you already did that. Leave the required cells empty then save the workbook with events disabled. Application.EnableEvents = False entered into the Immediate Window will do that for you. Save the workbook as a FileType of Template(*.xlt) Now close the workbook. You will not be asked to fill in the cells Go back to the Immediate window and re-enable events by making them = True. From now on when users open a new workbook based upon the Template, they will have to fill in the cells before saving. Gord Dibben MS Excel MVP On Thu, 8 Jan 2009 07:04:47 -0800, mdtuckett wrote: Hi Julie, I have added your code to my spreadsheet and it does force the user to populate the required cells. However, I need the cells to be blank when other users open the spreadsheet and for them to be forced to populate the cells before they save a copy. Do you know how I can save the spreadsheet having added the code, without populating the fields myself? Thanks "JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaツ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreツ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
Try the instructions about disabling events once more.
Pam M wrote: Gord--I followed these instructions to save the workbook template but still get the message that the cells need to be filled out. I cannot even click Save-As--the message pops up immediately. Any thoughts? "Gord Dibben" wrote: You will have to save the workbook as a Template with the beforesave code and the required cells empty. To do this, add the code to Thisworkbook.........I think you already did that. Leave the required cells empty then save the workbook with events disabled. Application.EnableEvents = False entered into the Immediate Window will do that for you. Save the workbook as a FileType of Template(*.xlt) Now close the workbook. You will not be asked to fill in the cells Go back to the Immediate window and re-enable events by making them = True. From now on when users open a new workbook based upon the Template, they will have to fill in the cells before saving. Gord Dibben MS Excel MVP On Thu, 8 Jan 2009 07:04:47 -0800, mdtuckett wrote: Hi Julie, I have added your code to my spreadsheet and it does force the user to populate the required cells. However, I need the cells to be blank when other users open the spreadsheet and for them to be forced to populate the cells before they save a copy. Do you know how I can save the spreadsheet having added the code, without populating the fields myself? Thanks "JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaツ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreツ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! -- Dave Peterson |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
I did--still no luck. Here is what I am doing.
Clicking View - Immediate Window Entering Application.EnableEvents = False into the immediate window Back to Excel, clicking File-Save As, but can't because I get the pop up to force entry into the cell. "Dave Peterson" wrote: Try the instructions about disabling events once more. Pam M wrote: Gord--I followed these instructions to save the workbook template but still get the message that the cells need to be filled out. I cannot even click Save-As--the message pops up immediately. Any thoughts? "Gord Dibben" wrote: You will have to save the workbook as a Template with the beforesave code and the required cells empty. To do this, add the code to Thisworkbook.........I think you already did that. Leave the required cells empty then save the workbook with events disabled. Application.EnableEvents = False entered into the Immediate Window will do that for you. Save the workbook as a FileType of Template(*.xlt) Now close the workbook. You will not be asked to fill in the cells Go back to the Immediate window and re-enable events by making them = True. From now on when users open a new workbook based upon the Template, they will have to fill in the cells before saving. Gord Dibben MS Excel MVP On Thu, 8 Jan 2009 07:04:47 -0800, mdtuckett wrote: Hi Julie, I have added your code to my spreadsheet and it does force the user to populate the required cells. However, I need the cells to be blank when other users open the spreadsheet and for them to be forced to populate the cells before they save a copy. Do you know how I can save the spreadsheet having added the code, without populating the fields myself? Thanks "JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaテつ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreテつ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! -- Dave Peterson |
#24
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
After you type that "application.enableevents = false" into the immediate
window, are you hitting enter? This command tells excel to stop looking for events completely. So it should work for you. (I've never seen it fail.) If the next test fails, enter this command right after the first: ?application.enableevents That should return True or False. If you see true, then events are enabled and your SaveAs will fire that event. If you see false (I hope!), then events are disabled and you should be able to do the SaveAs. Pam M wrote: I did--still no luck. Here is what I am doing. Clicking View - Immediate Window Entering Application.EnableEvents = False into the immediate window Back to Excel, clicking File-Save As, but can't because I get the pop up to force entry into the cell. "Dave Peterson" wrote: Try the instructions about disabling events once more. Pam M wrote: Gord--I followed these instructions to save the workbook template but still get the message that the cells need to be filled out. I cannot even click Save-As--the message pops up immediately. Any thoughts? "Gord Dibben" wrote: You will have to save the workbook as a Template with the beforesave code and the required cells empty. To do this, add the code to Thisworkbook.........I think you already did that. Leave the required cells empty then save the workbook with events disabled. Application.EnableEvents = False entered into the Immediate Window will do that for you. Save the workbook as a FileType of Template(*.xlt) Now close the workbook. You will not be asked to fill in the cells Go back to the Immediate window and re-enable events by making them = True. From now on when users open a new workbook based upon the Template, they will have to fill in the cells before saving. Gord Dibben MS Excel MVP On Thu, 8 Jan 2009 07:04:47 -0800, mdtuckett wrote: Hi Julie, I have added your code to my spreadsheet and it does force the user to populate the required cells. However, I need the cells to be blank when other users open the spreadsheet and for them to be forced to populate the cells before they save a copy. Do you know how I can save the spreadsheet having added the code, without populating the fields myself? Thanks "JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaテつ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreテつ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! -- Dave Peterson -- Dave Peterson |
#25
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
Dave; Never underestimate the power of hitting the enter key. lol--duh!
Thanks so much. : ) Pam "Dave Peterson" wrote: After you type that "application.enableevents = false" into the immediate window, are you hitting enter? This command tells excel to stop looking for events completely. So it should work for you. (I've never seen it fail.) If the next test fails, enter this command right after the first: ?application.enableevents That should return True or False. If you see true, then events are enabled and your SaveAs will fire that event. If you see false (I hope!), then events are disabled and you should be able to do the SaveAs. Pam M wrote: I did--still no luck. Here is what I am doing. Clicking View - Immediate Window Entering Application.EnableEvents = False into the immediate window Back to Excel, clicking File-Save As, but can't because I get the pop up to force entry into the cell. "Dave Peterson" wrote: Try the instructions about disabling events once more. Pam M wrote: Gord--I followed these instructions to save the workbook template but still get the message that the cells need to be filled out. I cannot even click Save-As--the message pops up immediately. Any thoughts? "Gord Dibben" wrote: You will have to save the workbook as a Template with the beforesave code and the required cells empty. To do this, add the code to Thisworkbook.........I think you already did that. Leave the required cells empty then save the workbook with events disabled. Application.EnableEvents = False entered into the Immediate Window will do that for you. Save the workbook as a FileType of Template(*.xlt) Now close the workbook. You will not be asked to fill in the cells Go back to the Immediate window and re-enable events by making them = True. From now on when users open a new workbook based upon the Template, they will have to fill in the cells before saving. Gord Dibben MS Excel MVP On Thu, 8 Jan 2009 07:04:47 -0800, mdtuckett wrote: Hi Julie, I have added your code to my spreadsheet and it does force the user to populate the required cells. However, I need the cells to be blank when other users open the spreadsheet and for them to be forced to populate the cells before they save a copy. Do you know how I can save the spreadsheet having added the code, without populating the fields myself? Thanks "JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaテム堙つ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreテム堙つ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! -- Dave Peterson -- Dave Peterson |
#26
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
One more quick question Dave--can I set the application.enable events = false
or true within a macro so that it disables events in order to save the template, but then re-enables them? "Dave Peterson" wrote: After you type that "application.enableevents = false" into the immediate window, are you hitting enter? This command tells excel to stop looking for events completely. So it should work for you. (I've never seen it fail.) If the next test fails, enter this command right after the first: ?application.enableevents That should return True or False. If you see true, then events are enabled and your SaveAs will fire that event. If you see false (I hope!), then events are disabled and you should be able to do the SaveAs. Pam M wrote: I did--still no luck. Here is what I am doing. Clicking View - Immediate Window Entering Application.EnableEvents = False into the immediate window Back to Excel, clicking File-Save As, but can't because I get the pop up to force entry into the cell. "Dave Peterson" wrote: Try the instructions about disabling events once more. Pam M wrote: Gord--I followed these instructions to save the workbook template but still get the message that the cells need to be filled out. I cannot even click Save-As--the message pops up immediately. Any thoughts? "Gord Dibben" wrote: You will have to save the workbook as a Template with the beforesave code and the required cells empty. To do this, add the code to Thisworkbook.........I think you already did that. Leave the required cells empty then save the workbook with events disabled. Application.EnableEvents = False entered into the Immediate Window will do that for you. Save the workbook as a FileType of Template(*.xlt) Now close the workbook. You will not be asked to fill in the cells Go back to the Immediate window and re-enable events by making them = True. From now on when users open a new workbook based upon the Template, they will have to fill in the cells before saving. Gord Dibben MS Excel MVP On Thu, 8 Jan 2009 07:04:47 -0800, mdtuckett wrote: Hi Julie, I have added your code to my spreadsheet and it does force the user to populate the required cells. However, I need the cells to be blank when other users open the spreadsheet and for them to be forced to populate the cells before they save a copy. Do you know how I can save the spreadsheet having added the code, without populating the fields myself? Thanks "JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaテム堙つ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreテム堙つ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! -- Dave Peterson -- Dave Peterson |
#27
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
Sure.
Make a macro that only you know the name of and make it invisible to the alt-f8 dialog. In a separate module: Option Explicit Option Private Module Sub PamsSaveAs() application.enableevents = false thisworkbook.save 'save the current file?? application.enableevents = true End Sub Pam M wrote: One more quick question Dave--can I set the application.enable events = false or true within a macro so that it disables events in order to save the template, but then re-enables them? "Dave Peterson" wrote: After you type that "application.enableevents = false" into the immediate window, are you hitting enter? This command tells excel to stop looking for events completely. So it should work for you. (I've never seen it fail.) If the next test fails, enter this command right after the first: ?application.enableevents That should return True or False. If you see true, then events are enabled and your SaveAs will fire that event. If you see false (I hope!), then events are disabled and you should be able to do the SaveAs. Pam M wrote: I did--still no luck. Here is what I am doing. Clicking View - Immediate Window Entering Application.EnableEvents = False into the immediate window Back to Excel, clicking File-Save As, but can't because I get the pop up to force entry into the cell. "Dave Peterson" wrote: Try the instructions about disabling events once more. Pam M wrote: Gord--I followed these instructions to save the workbook template but still get the message that the cells need to be filled out. I cannot even click Save-As--the message pops up immediately. Any thoughts? "Gord Dibben" wrote: You will have to save the workbook as a Template with the beforesave code and the required cells empty. To do this, add the code to Thisworkbook.........I think you already did that. Leave the required cells empty then save the workbook with events disabled. Application.EnableEvents = False entered into the Immediate Window will do that for you. Save the workbook as a FileType of Template(*.xlt) Now close the workbook. You will not be asked to fill in the cells Go back to the Immediate window and re-enable events by making them = True. From now on when users open a new workbook based upon the Template, they will have to fill in the cells before saving. Gord Dibben MS Excel MVP On Thu, 8 Jan 2009 07:04:47 -0800, mdtuckett wrote: Hi Julie, I have added your code to my spreadsheet and it does force the user to populate the required cells. However, I need the cells to be blank when other users open the spreadsheet and for them to be forced to populate the cells before they save a copy. Do you know how I can save the spreadsheet having added the code, without populating the fields myself? Thanks "JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaテム堙つ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreテム堙つ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#28
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making cells mandatory to fill in
Perfect! Thanks so much Dave--you're the best.
"Dave Peterson" wrote: Sure. Make a macro that only you know the name of and make it invisible to the alt-f8 dialog. In a separate module: Option Explicit Option Private Module Sub PamsSaveAs() application.enableevents = false thisworkbook.save 'save the current file?? application.enableevents = true End Sub Pam M wrote: One more quick question Dave--can I set the application.enable events = false or true within a macro so that it disables events in order to save the template, but then re-enables them? "Dave Peterson" wrote: After you type that "application.enableevents = false" into the immediate window, are you hitting enter? This command tells excel to stop looking for events completely. So it should work for you. (I've never seen it fail.) If the next test fails, enter this command right after the first: ?application.enableevents That should return True or False. If you see true, then events are enabled and your SaveAs will fire that event. If you see false (I hope!), then events are disabled and you should be able to do the SaveAs. Pam M wrote: I did--still no luck. Here is what I am doing. Clicking View - Immediate Window Entering Application.EnableEvents = False into the immediate window Back to Excel, clicking File-Save As, but can't because I get the pop up to force entry into the cell. "Dave Peterson" wrote: Try the instructions about disabling events once more. Pam M wrote: Gord--I followed these instructions to save the workbook template but still get the message that the cells need to be filled out. I cannot even click Save-As--the message pops up immediately. Any thoughts? "Gord Dibben" wrote: You will have to save the workbook as a Template with the beforesave code and the required cells empty. To do this, add the code to Thisworkbook.........I think you already did that. Leave the required cells empty then save the workbook with events disabled. Application.EnableEvents = False entered into the Immediate Window will do that for you. Save the workbook as a FileType of Template(*.xlt) Now close the workbook. You will not be asked to fill in the cells Go back to the Immediate window and re-enable events by making them = True. From now on when users open a new workbook based upon the Template, they will have to fill in the cells before saving. Gord Dibben MS Excel MVP On Thu, 8 Jan 2009 07:04:47 -0800, mdtuckett wrote: Hi Julie, I have added your code to my spreadsheet and it does force the user to populate the required cells. However, I need the cells to be blank when other users open the spreadsheet and for them to be forced to populate the cells before they save a copy. Do you know how I can save the spreadsheet having added the code, without populating the fields myself? Thanks "JulieD" wrote: Hi Rob from the menu in the VBE screen choose view / project explorer Cheers JulieD "abfabrob" wrote in message ... It doesn't say "This Workbook" anywhere... the VBE screen is completely blank... Rob. "JulieD" wrote: Hi Rob to use the code, right mouse click on a sheet tab and choose view / code - in the VBE window you'll see down the left hand side your workbook's name in bold & brackets ... under that will be things like sheet1,sheet2, ThisWorkbook - the code needs to go into the "ThisWorkbook" section - so double click on ThisWorkbook then on the top right side of the screen you have two drop down arrows - on the one of the left choose Workbook on the one on the right choose Before Save then Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub will appear on your screen - the first line (starting with Private & ending with Cancel As Boolean is all one line like in your screen) now between these two lines you need to copy & paste the code Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell --- now where it says "Sheet2" above change that to the sheet name you want to check for entry on (leave the "") and where it says A1, A2, A3, A4 change these to the actual cells you want to ensure have data in them. once you've done that use ALT & F11 to switch back to your workbook and test it. Let us know how you go. Cheers JulieD "abfabrob" wrote in message ... I was given this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Booleaテθ津「ぎナ。テム堙つ*n, _ Cancel As Boolean) Dim cell As Range For Each cell In Range("Required_Cells") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Addreテθ津「ぎナ。テム堙つ*ss Application.Goto cell Cancel = True Exit For End If Next cell End Sub But I have no idea what it means. I put it in the right place (I think) and I input the cells I want the users to fill in, butI keep getting this message: "Compile error: user-defined type not defined". I so have no knowledge of anything, quite clearly. I need it explained step by step. Cheers, Rob "JulieD" wrote: Hi following on from your discussion with Jason Morin (please stay in the original thread) - what is the code you're getting the errors on, what is the sheet name and cells that you want to make mandatory? Cheers JulieD "abfabrob" wrote in message ... I am creating an application form where I want users to fill certain cells in before closing the document. Does anyone have an idiots guide to doing this, cos I'm not great with excell. ANY help welcome!!! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#29
|
|||
|
|||
I have a question about this same thing. I need to make inside sales fill out a spread sheet. I have the code that is listed in this forum. But I need to include each cell with a different message box. How do I tie it together. example C1 Message box - must enter todays date. C2 - must enter name and so on. This is what I have so far
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Cell As Range For Each Cell In Sheets("Sheet1").Range("C3") If IsEmpty(Cell.Value) Then MsgBox "You must enter Today's Date" & Cell.Address Application.Goto Cell Cancel = True Exit For End If Next Cell End Sub I have tried to enter code for each cell seperately but cannot get it to work. I am very new to this. Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making cells compulsary to fill in on a worksheet | Excel Discussion (Misc queries) | |||
Change a cell's fill color dynamically? | Excel Discussion (Misc queries) | |||
How to create mandatory cells in a worksheet | Excel Worksheet Functions | |||
Excel - formula to calculate colored fill cells within a range wi. | Excel Worksheet Functions | |||
making cells adjust | Excel Discussion (Misc queries) |