| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making cells mandatory to fill in if a previous cell contains info
I have seen the making cells mandatory to fill in comment and it worked, but
I need the same only if a previous cell has been filled. I have various columns not all of them will be filled but if a cell is filled the complete row will have to be filled, how do I do this, if one cell is filled then the complete row should be filled? thanks, leonardo |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Making cells mandatory to fill in if a previous cell contains info
I would use a helper column that put a warning in big red letters:
=if(and(counta(b2:f2)0,counta(b2:f2)<5)),"Please fix this row!","") If there's anything in B2:F2, but they're not all filled in (B:F is 5 columns), then put that warning message. leonardo wrote: I have seen the making cells mandatory to fill in comment and it worked, but I need the same only if a previous cell has been filled. I have various columns not all of them will be filled but if a cell is filled the complete row will have to be filled, how do I do this, if one cell is filled then the complete row should be filled? thanks, leonardo -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Making cells mandatory to fill in if a previous cell contains
hi, I used this code and it worked, but I would like to be able to apply this
code to all my worksheets. The name of my worksheets a X340, X342n and X642e? How can I modify this code so it applies to all my worksheets? thanks, p.d. thanks for the other tip! "Dave Peterson" wrote: I would use a helper column that put a warning in big red letters: =if(and(counta(b2:f2)0,counta(b2:f2)<5)),"Please fix this row!","") If there's anything in B2:F2, but they're not all filled in (B:F is 5 columns), then put that warning message. leonardo wrote: I have seen the making cells mandatory to fill in comment and it worked, but I need the same only if a previous cell has been filled. I have various columns not all of them will be filled but if a cell is filled the complete row will have to be filled, how do I do this, if one cell is filled then the complete row should be filled? thanks, leonardo -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Making cells mandatory to fill in if a previous cell contains
This is a formula that sits in a cell.
You'll have to put the formula in a cell in each worksheet. leonardo wrote: hi, I used this code and it worked, but I would like to be able to apply this code to all my worksheets. The name of my worksheets a X340, X342n and X642e? How can I modify this code so it applies to all my worksheets? thanks, p.d. thanks for the other tip! "Dave Peterson" wrote: I would use a helper column that put a warning in big red letters: =if(and(counta(b2:f2)0,counta(b2:f2)<5)),"Please fix this row!","") If there's anything in B2:F2, but they're not all filled in (B:F is 5 columns), then put that warning message. leonardo wrote: I have seen the making cells mandatory to fill in comment and it worked, but I need the same only if a previous cell has been filled. I have various columns not all of them will be filled but if a cell is filled the complete row will have to be filled, how do I do this, if one cell is filled then the complete row should be filled? thanks, leonardo -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Making cells mandatory to fill in if a previous cell contains
sorry, this is the code I am reffering to: and I would like this code to work
for all worksheets: X340, X342n and X642e, thnaks, Dim cell As Range For Each cell In Sheets("X340").Range("L2,L58") If Len(Trim(cell.Text)) = 0 Then MsgBox "Please make sure that column B, K & L are filled" Application.Goto cell Cancel = True Exit For End If Next cell "Dave Peterson" wrote: This is a formula that sits in a cell. You'll have to put the formula in a cell in each worksheet. leonardo wrote: hi, I used this code and it worked, but I would like to be able to apply this code to all my worksheets. The name of my worksheets a X340, X342n and X642e? How can I modify this code so it applies to all my worksheets? thanks, p.d. thanks for the other tip! "Dave Peterson" wrote: I would use a helper column that put a warning in big red letters: =if(and(counta(b2:f2)0,counta(b2:f2)<5)),"Please fix this row!","") If there's anything in B2:F2, but they're not all filled in (B:F is 5 columns), then put that warning message. leonardo wrote: I have seen the making cells mandatory to fill in comment and it worked, but I need the same only if a previous cell has been filled. I have various columns not all of them will be filled but if a cell is filled the complete row will have to be filled, how do I do this, if one cell is filled then the complete row should be filled? thanks, leonardo -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Making cells mandatory to fill in if a previous cell contains
Are you trying to look at all the rows 2:58 or just row 2 and row 58?
I'm gonna guess that you want all the rows: Dim cell As Range dim myRow as long with sheets("x340") For Each cell In .Range("a2:a58").cells 'just define the row myrow = cell.row if trim(.cells(myrow,"B")) = "" _ or trim(.cells(myrow,"K")) = "" _ or trim(.cells(myrow,"L")) = "" then MsgBox "Please make sure that column B, K & L are filled on this row" Application.Goto cell Cancel = True Exit For End If Next cell end with But you could use "L2,L58" if that's what you wanted. As a user, I think I'd rather receive that immediate feedback that the worksheet formula gives me--rather than waiting until I was saving the workbook. leonardo wrote: sorry, this is the code I am reffering to: and I would like this code to work for all worksheets: X340, X342n and X642e, thnaks, Dim cell As Range For Each cell In Sheets("X340").Range("L2,L58") If Len(Trim(cell.Text)) = 0 Then MsgBox "Please make sure that column B, K & L are filled" Application.Goto cell Cancel = True Exit For End If Next cell "Dave Peterson" wrote: This is a formula that sits in a cell. You'll have to put the formula in a cell in each worksheet. leonardo wrote: hi, I used this code and it worked, but I would like to be able to apply this code to all my worksheets. The name of my worksheets a X340, X342n and X642e? How can I modify this code so it applies to all my worksheets? thanks, p.d. thanks for the other tip! "Dave Peterson" wrote: I would use a helper column that put a warning in big red letters: =if(and(counta(b2:f2)0,counta(b2:f2)<5)),"Please fix this row!","") If there's anything in B2:F2, but they're not all filled in (B:F is 5 columns), then put that warning message. leonardo wrote: I have seen the making cells mandatory to fill in comment and it worked, but I need the same only if a previous cell has been filled. I have various columns not all of them will be filled but if a cell is filled the complete row will have to be filled, how do I do this, if one cell is filled then the complete row should be filled? thanks, leonardo -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Making cells mandatory to fill in if a previous cell contains
And if you wanted to loop through those worksheets...
Dim wks As Worksheet Dim cell As Range Dim myRow As Long Dim FoundAnError As Boolean FoundAnError = False For Each wks In Worksheets(Array("x340", "x342n", "x642e")) If FoundAnError Then Exit For End If With wks For Each cell In .Range("a2:a58").Cells 'just define the row myRow = cell.Row If Trim(.Cells(myRow, "B")) = "" _ Or Trim(.Cells(myRow, "K")) = "" _ Or Trim(.Cells(myRow, "L")) = "" Then MsgBox "Please make sure that column B, K & L are filled on this row" Application.Goto cell 'Cancel = True FoundAnError = True Exit For End If Next cell End With Next wks leonardo wrote: sorry, this is the code I am reffering to: and I would like this code to work for all worksheets: X340, X342n and X642e, thnaks, Dim cell As Range For Each cell In Sheets("X340").Range("L2,L58") If Len(Trim(cell.Text)) = 0 Then MsgBox "Please make sure that column B, K & L are filled" Application.Goto cell Cancel = True Exit For End If Next cell "Dave Peterson" wrote: This is a formula that sits in a cell. You'll have to put the formula in a cell in each worksheet. leonardo wrote: hi, I used this code and it worked, but I would like to be able to apply this code to all my worksheets. The name of my worksheets a X340, X342n and X642e? How can I modify this code so it applies to all my worksheets? thanks, p.d. thanks for the other tip! "Dave Peterson" wrote: I would use a helper column that put a warning in big red letters: =if(and(counta(b2:f2)0,counta(b2:f2)<5)),"Please fix this row!","") If there's anything in B2:F2, but they're not all filled in (B:F is 5 columns), then put that warning message. leonardo wrote: I have seen the making cells mandatory to fill in comment and it worked, but I need the same only if a previous cell has been filled. I have various columns not all of them will be filled but if a cell is filled the complete row will have to be filled, how do I do this, if one cell is filled then the complete row should be filled? thanks, leonardo -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Making cells mandatory to fill in | Excel Discussion (Misc queries) | |||
| Making Cells Mandatory and Running Checks | Excel Worksheet Functions | |||
| Making Cell Entry Mandatory | Excel Discussion (Misc queries) | |||
| Is there a way of making data imput in to a cell mandatory before. | Excel Worksheet Functions | |||
| Making Cells mandatory | Excel Programming | |||
Linear Mode
