If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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
|
|||
|
|||
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
|
|||
|
|||
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!!! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
fill random cells in an area | hulub | General Discussion | 1 | September 17th, 2004 05:48 PM |
Making a toolbar button that can format cells a certain way... | Tripp Knightly | General Discussion | 1 | September 15th, 2004 06:18 PM |
I want to randomly fill an array of cells with values from a list I create | shadestreet | General Discussion | 2 | June 21st, 2004 11:59 AM |
Excel formula - select cells based upon fill color and then sum | Worksheet Functions | 0 | May 13th, 2004 07:45 PM |