Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / February 2006

Tip: Looking for answers? Try searching our database.

Making cells mandatory to fill in if a previous cell contains info

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
leonardo - 23 Feb 2006 10:01 GMT
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 - 23 Feb 2006 15:43 GMT
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.

> 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
[quoted text clipped - 3 lines]
> thanks,
> leonardo

Signature

Dave Peterson

leonardo - 24 Feb 2006 15:39 GMT
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 are: 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!

> I would use a helper column that put a warning in big red letters:
>
[quoted text clipped - 10 lines]
> > thanks,
> > leonardo
Dave Peterson - 24 Feb 2006 17:01 GMT
This is a formula that sits in a cell.

You'll have to put the formula in a cell in each worksheet.

> 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 are: X340, X342n and
[quoted text clipped - 21 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

leonardo - 24 Feb 2006 17:15 GMT
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

> This is a formula that sits in a cell.
>
[quoted text clipped - 25 lines]
> > >
> > > Dave Peterson
Dave Peterson - 24 Feb 2006 18:39 GMT
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.

> sorry, this is the code I am reffering to: and I would like this code to work
> for all worksheets: X340, X342n and X642e,
[quoted text clipped - 43 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Dave Peterson - 24 Feb 2006 18:43 GMT
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

> sorry, this is the code I am reffering to: and I would like this code to work
> for all worksheets: X340, X342n and X642e,
[quoted text clipped - 43 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.