make input mandatory for specific cell in a row if any cell in the same row contains data

[peterCOB]

Greetings, I'm new to this forum but have visited many times for great ideas. This time i'm stumped.

I have an applications tracking chart with dozens of rows. I'd like to make key cells in each row corresponding to a new applicaiton mandatory if data is input to any cell in the row.

I also have a macro that copies the formats and formulas from the last row of the chart to the next row down which works reasonably well. I provide it here for context:

Sub CopyMacro()
ActiveSheet.Range(Range("AD65536").End(xlUp), "AD" & Range("AD65536").End(xlUp).Row).Copy
ActiveSheet.Range("AD65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
ActiveSheet.Range(Range("X65536").End(xlUp), "X" & Range("X65536").End(xlUp).Row).Copy
ActiveSheet.Range("X65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
ActiveSheet.Range(Range("R65536").End(xlUp), "R" & Range("R65536").End(xlUp).Row).Copy
ActiveSheet.Range("R65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
ActiveSheet.Range(Range("O65536").End(xlUp), "O" & Range("O65536").End(xlUp).Row).Copy
ActiveSheet.Range("O65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
ActiveSheet.Range("a65536").End(xlUp).EntireRow.Copy
ActiveSheet.Range("a65536").End(xlUp).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub

To make cells in the newly created row manadatory i did the following:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = False
Application.EnableEvents = True

If Trim(Range("A8")) <> "" Then
If Trim(Range("B8")) = "" Then Cancel = True
If Trim(Range("C8")) = "" Then Cancel = True

If Trim(Range("E8")) = "" Then Cancel = True
If Trim(Range("F8")) = "" Then Cancel = True
If Trim(Range("G8")) = "" Then Cancel = True
If Trim(Range("H8")) = "" Then Cancel = True
If Trim(Range("I8")) = "" Then Cancel = True
If Trim(Range("J8")) = "" Then Cancel = True
If Trim(Range("K8")) = "" Then Cancel = True
If Trim(Range("L8")) = "" Then Cancel = True
If Trim(Range("M8")) = "" Then Cancel = True
If Trim(Range("N8")) = "" Then Cancel = True
End If

If Trim(Range("A9")) <> "" Then
If Trim(Range("F9")) = "" Then Cancel = True
If Trim(Range("G9")) = "" Then Cancel = True
If Trim(Range("H9")) = "" Then Cancel = True
End If

If Cancel = True Then MsgBox "Please fill in all the cells required."

End Sub

D8 is left out because i want it to be mandatory only if C8="registered"

The problem I'm having is the cells are only mandatory if data is input in A8, How do I specify a range like A8:N8, or A8, B8, C8.... in the initial argument? I tried doing this and it did nothing.

Also I want this to work for every new row created and so far have only thought of repeating the code for A9, A10 etc., but this will quickly become overwhelming, is there a way of simplifing this?

Any help that may be offered is greatly appreciated.

[stnkynts]

Hopefully this will help answer some of your questions. I tried to include descriptions of what is going on.

Please Login or Register  to view this content.

[peterCOB]

Thanks stnkynts, this is brilliant and worked perfectly.
I altered the code somewhat to reflect that the fist formula copied to the new row occurs in column R and for the time being I'm applying this only to rows 140 and beyond.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet: Set ws = Sheets("Applications")
Dim lastrow As Long
Dim myRange As Range, icell As Range
Dim msg As Variant

lastrow = ws.Range("R" & Rows.Count).End(xlUp).Row

Set myRange = Union(ws.Range("A140:C" & lastrow), ws.Range("E" & lastrow), ws.Range("G140:N" & lastrow), ws.Range("W" & lastrow), ws.Range("Z" & lastrow))

For Each icell In myRange

If IsEmpty(icell) Then
msg = MsgBox("Please input data to ALL mandatory fields. Cell " & icell.Address & " has no data in it. Please correct.", vbExclamation, "Error")
Cancel = True
Exit Sub
End If
Next icell

End Sub


What i'd like to do is set the range to include cells that are only mandatory if certain cells have specific inputs. I tried adding an if/then argument to the Set myRange = Union string but it would not compile. I also tried the following as a separate line, which did not produce a compile error but also did not do anything:


If "C" & lastrow = "Registered" Then Set myRange = ws.Range("D" & lastrow)


I have several cells in the row that should be conditionally mandatory, also I'd like to be able to highlight the mandatory cells in the active row.

Thanks agian for your assistance and if you have any thoughts on the above, I'd greatly appreciate your help.

[stnkynts]

Glad to hear that things are moving in a forward direction. Could you please describe which cells you need to test for what value and what you want the range to be if the test is true. For example, If Cell C200 has a value of Registered Then Set myRange = ?

Note: when you say "C" & lastrow it means nothing in code. Range("C" and lastrow) could be a possible range provided lastrow is defined. However, it is only going to be equivalent to 1 cell. If you need the whole entire column it would need to be written differently. Finally an if statement by itself will only work on 1 cell or value.

[peterCOB]

Thanks for your quick response,


If Cell C200 contains the text "Registered" then D200 is a mandatory cell
If Cell C200 contains the text "Approved" then F200 is a mandatory cell

[stnkynts]

I would probably use Select/Case. I like it better than a bunch of if statements. I added to the above mentioned code of mine since it seemed to make sense to you (with a couple of rearranges that is). Let me know if you need any explanation.

Please Login or Register  to view this content.

*Note: The select/case is only going to check for 1 value in 1 cell. If we need to check all of column C we will need to incorporat it into a loop or we might be able to use specialcells

[peterCOB]

Once again, this worked brilliantly.

I think i can modify this code as needed for any other conditions. My last remainning issue is, is there a way that the mandatory cells, if empty, are highlighted if the row or any cell on the row is selected? I've been experimenting, and have gotten this to work with conditional formatting but of course it applies the format to the cell whether the row is selected or not which may be distracting to the end user.

On the whole this is working very well, thanks again for all your help.

[stnkynts]

Your last question is pretty easy to solve I believe. I set the ColorIndex to 6 which is yellow. You can google search colorindex for all the colors. This will highlight the whole row if one of the mandatory cells is empty. It won't remove the highlight if it's not because I wasn't sure if you had other stuff highlighted in your workbook. See the highlighted red text below for the added code.

Please Login or Register  to view this content.

If you have enjoyed my help please click the star to add to my reputation.

[peterCOB]

Thanks I'll try this.

My thought was that the mandatory cells in a given row are only highlighted in yellow when the user selects a row or a cell in the row and only if the mandatory cells are blank. But there is a logic to keeping all mandatory cells highlighted until the data is entered regardless of what row the user is interested in. I've managed to make this work using conditional formats.

You had mentioned previously that in order to make the Select Case function for a whole coloumn, it requires a loop. Could you show me how that might function? It occurs to me that if the status of an application changes from "in progress" to "approved" at a later date and the entry is no longer the last row in the chart then the code would not apply to the the associated mandatory cell.

Thanks again for your help

[stnkynts]

The above code has a form of "loop" in it. It is called a For/Next loop. A loop basically goes through a specific range, say C100 to C300, and applies the code within the loop to each of those cells. From the sounds of your most recent post you may not need to use a loop but instead check the last row.

If you wanted to check just the last row, you could use this code instead of the one provided:

Please Login or Register  to view this content.

If you did indeed need to do a loop, say from C100 to C300, then you could do it like this;

Please Login or Register  to view this content.

Hope this helps.

[peterCOB]

Thanks again,

The loop did exactly what i wanted it to do.

With a combination of scripts and conditonal formats my chart is performing great. Thanks so much for all your help.

Slightly off topic - now that i have all this VBA code working away, How do i prevent a user from accessing it and potentially altering it? the worksheet is password protected but even so alt f11 still works and the code can still be edited.

another Slightly off topic - select copy and past locked and unlocked cells is still possible when the sheet is protected. is it possible to change the default copy to limit it to values only leaving formats, conditional formats etc. alone? Reason being is many conditional formats contain formulas that i don't want applied to other cells (not all of them use absolute coordinates). plus muliple copy/pastes creates very many copies of conditional formats - must tax resourses.

I'll check out some other threads for ideas also.

Thanks again for all your help.

[stnkynts]

You can lock the VBA from viewing in the VBA Project Properties under Tools in the Visual Basic Editor. It's not fool proof but should work for the most part.

Not quite sure about your locked/unlocked cells problem

[peterCOB]

Thanks again, that did the trick,

[peterCOB]

I Have one more related issue that perhaps you could shed some light on.

To give staff some time to complete the missing mandatory field data in the chart. i've set the mandatory cells to be mandatory only for rows 140 and beyond. for earlier rows, I want to have greeting message box that reminds users to fill in the missing data.

I used the following to create the greetings and message box, but when i added the if/then statements to GoTo different messages depending on who the user is, it does not compile.

I'm sure I'm not using the IF statements correctly but can't seem to figure out the right syntax. Do i need to use Else If?

Any help is greatly appreciated.


Private Sub Workbook_Open()
Dim sName As String
Dim sTxt As String
Dim CurrTime As Long
sName = Environ("UserName")

Select Case Environ("UserName")
Case Is = "Xxxxxx"
aTxt = "Bob"
Case Is = "Yyyyyy"
aTxt = "Doug"
Case Else
aTxt = Environ("username")
End Select

CurrTime = Hour(Now)
Select Case CurrTime
Case Is > 18
sTxt = "Good evening "
Case Is > 12
sTxt = "Good afternoon "
Case Is < 8
sTxt = "In so early? Good morning, "
Case Else
sTxt = "Good Morning "
End Select

'Sheet3!D3 contains the formula: =IF(ISNA(B3),Applications!BA9, SUMIF(Applications!I10:I1048576,Sheet3!B3,Applications!BA10:BA1048576))
'Sheet3!B3 contains a vlookup to a table of staff usernames that may use the chart. a logged in user with a username not in the table (manager) results in a NA# error.
If [Sheet3!D3].Value > 0 Then
GoTo iMsg4
If [Sheet3!D3].Value = 0 Then
GoTo iMsg5
If [Sheet3!D3].Value > 0 And IsNA([Sheet3!B3].Value = 1) Then
GoTo iMsg6
If [Sheet3!D3].Value = 0 And IsNA([Sheet3!B3].Value = 1) Then
GoTo iMsg7

Exit Sub
iMsg4:
msg = MsgBox(sTxt & aTxt & ". This is your Computer, I noticed that there are " & [Sheet3!D3] & "cells missing mandatory data associated with applications where you are the assigned staff member. Please input the required data at your earliest convenience. Thank you.")
Exit Sub
iMsg5:
msg = MsgBox(sTxt & aTxt) & ". This is your Computer. Congratulations! You have no missing data associated with your applications. Thank you."
iMsg6:
msg = MsgBox(sTxt & aTxt & ". This is your Computer, I noticed that there are " & [Sheet3!D3] & "cells missing mandatory data. Please ask the appropriate staff member to input the required data as soon as possible. Thank you.")
iMsg7:
msg = MsgBox(sTxt & aTxt & ". This is your Computer. All mandatory data fields are complete and up to date. Thank you.")

Exit Sub

End Sub

[stnkynts]

Sorry for the delayed response. Your code is failing because you are missing the "end if" statement. All If statements need to have an "end if" accompany then unless you put all the code on one line. Let me see if I can demonstrate using your code:

BAD:

Please Login or Register  to view this content.

OK

Please Login or Register  to view this content.

BETTER:

Please Login or Register  to view this content.

BETTER:

Please Login or Register  to view this content.

In addition, by not having an "Exit Sub" after each message "at the bottom" you will have multiple messages displayed to 1 person when you do not want it to. Hope this helps.