Forms, Formulas and Dropdowns. Difficult

[Pinacolada16]

I have created a form for work.
I have different sections for users to fill out. I have a submit button at the top which validates data in specific cells and then if they are complete, it allows and email to pop up with the file attached, if not, then it comes up with an error to complete all fields.

The user has the option to click a button to unhide additional rows for another quote and they can then fill out that section. I am trying to get the submit button to then verify all of the cells in that section are complete before it sends... and so on for 9 additional quotes.

How can i get this to happen? I have tried doing an additional VALIDATEDATA macro but not sure how to get it into this formula to verify.

I do have a cell that is calculating how many of these "quotes are filled out"
is there a way for the button to take that number and then determine how many cells should be filled out?

Please help!button.jpg

[SamT]

Go Advanced >> Manage Attachments... And upload the workbook.

[Pinacolada16]

file attached

[p45cal]

You could have a VALIDATEDATA function like this:

Function VALIDATEDATA() As Boolean
For Each cll In RequestCellsToCheck.Cells
  If IsEmpty(cll) Then
    VALIDATEDATA = True
    Exit For
  End If
Next cll
End Function

which depends on another function RequestCellsToCheck:

Public Function RequestCellsToCheck() As Range
With Sheets("Quick Quote Form")
  .Unprotect "OGSProc"
  Set myRng = .Range("G7")
  For Each cll In Intersect(.UsedRange, .Range("D:D,J:J").SpecialCells(xlCellTypeVisible)).Cells
    If Not cll.Locked And (cll.MergeArea.Cells(1).Address = cll.Address) Then
      Set myRng = Union(myRng, cll)
    End If
  Next cll
  .Protect Password:="OGSProc", AllowFormattingColumns:=True, AllowFormattingRows:=True
End With
Set RequestCellsToCheck = myRng
End Function

You should be able to see that this second function returns a range of cells to check depending on them being visible and being not locked and in either column D or J (plus cell G7).
This may need some tweaking; you may not want to check for a secondary email and currently the requested timeframe cell is locked and perhaps shouldn't be.
If you wanted something similar for the proposal side you could addd a similar function and call it, say, ProposalCellsToCheck (or better, include arguments in the RequestCellsToCheck function).

[Pinacolada16]

OMG you're awesome! That totally worked.

I need to get the other side to work now so I will see if I can get it. I was not even on the same page as you - I was thinking maybe a loop would help but then i dont know what a loop does!

I'm new at this excel stuff... really just started using excel about 2 weeks ago and this is the stuff work has me doing right now! I'm eager to learn more but jeez! they have me learning pretty quick! haha

Thank you again ! I'll see if i can get the other side and if not, you may hear back from me for assistance!

[Pinacolada16]

How do I get the Msgbox "By Clicking Ok..." to pop up on the Proposal side if the check box is selected as no response and the boxes are grayed out?

It's checking to make sure the data is filled in but if the check box is selected, the cells don't need to be filled out.

[SamT]

Without looking

This is not real Code. Fix what's inside the []

If [CheckBox is checked] then
   MsgBox "By Clicking Ok..."
Else
   [Check if the cells are filled out]
   [New MsgBox]
End if

[p45cal]

Something along the lines of:

Public Function ProposalCellsToCheck() As Range
With Sheets("Quick Quote Form")
  .Unprotect "OGSProc"
  Set myRng = .Range("P16")
  For Each cll In Intersect(.UsedRange, .Range("P:P,R:R,U:U").SpecialCells(xlCellTypeVisible)).Cells
    If Not cll.Locked And (cll.MergeArea.Cells(1).Address = cll.Address) And cll.DisplayFormat.Interior.Pattern <> -4126 Then
    'If Not cll.Locked And (cll.MergeArea.Cells(1).Address = cll.Address) And cll.DisplayFormat.Interior.Pattern = 1 Then 'an alternative to the above line.
      Set myRng = Union(myRng, cll)
    End If
  Next cll
  .Protect Password:="OGSProc", AllowFormattingColumns:=True, AllowFormattingRows:=True
End With
Set ProposalCellsToCheck = myRng
End Function

Should U7 be locked?
The Auction Type fields; should there be both a formula and a dropdown? Once the dropdown has been used once, the formula disappears for ever.

[Pinacolada16]

S7 should be locked, i want it to pull the data from the other side.
Also, they wanted the drop down to pull from the other side but also have the option to change it if they need to - which is why there is a formula, i know it seems stupid but i didnt make the decision on that one.. .

Also, now we're putting in a formula where there will be something entered into T26 which is merged with U26 and V26 where it will mark that side and quote as complete, incomplete, no response or response pending. we would like the send proposal button to send when it either says complete, no response or quote pending.

How do I get that instead of checking that all the fields are complete?

[p45cal]

S7 should be locked, i want it to pull the data from the other side.

I was talking of U7; it's just an empty cell.

Also, now we're putting in a formula where there will be something entered into T26 which is merged with U26 and V26 where it will mark that side and quote as complete, incomplete, no response or response pending. we would like the send proposal button to send when it either says complete, no response or quote pending.

How do I get that instead of checking that all the fields are complete?

That's not so straightforward and would take me some time - which I'm not prepared to spend on this, sorry. Others though…

[Pinacolada16]

In the most recent version, U7 is merged with S7.

What we need and this shouldnt be as complex is this -

There is a formula that is putting a "1" in Column T when the quote section is complete.

We need the button to work when the column has a 1 and when P16 - P24 are filled in. T:T is a locked Column and P16 - P24 are unlocked...

Any chance you knwo of a way to get the submit button to work?

Thank you so much - i cannot for the life of me figure it out!

[p45cal]

I'm not promising anything but attaching your recent version here would help anyone prepared to help.

[Pinacolada16]

The formulas have only been put into the first quote because we changed so much of it - I will be updating the hidden quotes to be the same but we need to get them to email out like the other side.

Thank you

[Pinacolada16]

Or would it be easier if there was a 1 in column "T" if it was incomplete AND/OR the cells P16:P24 are not complete then the file should not send?

I'm just not sure how to take care of the hidden rows and locked / unlocked cells.

[SamT]

I changed the title to reflect the latest issue

[p45cal]

There is a formula that is putting a "1" in Column T when the quote section is complete.
We need the button to work when the column has a 1

If you've only got these 1's in column T (one for each section) and nothing else, then you can use a formula to compare the COUNT with the COUNTA of column T.
You could use the visible-cells-only versions of these two functions:
=SUBTOTAL(102,T1:T350)=SUBTOTAL(103,T1:T350)
or
=AGGREGATE(2,5,T1:T350)=AGGREGATE(3,5,T1:T350)
which will return TRUE if the counts are the same, that is: the count of visible non-empty cells in column T is the same as the count of visible cells with a number in, in column T. Then your macro just needs to check that cell for TRUE (Perhaps avoid using a cell in column T for this formula!).
For the top Contractor section, you can do something similar to what you've done in cell X27.

[Pinacolada16]

Thank you! What I ended up doing was i made a count over on the right side for when something was incomplete - then I just had it so that if the cell was 0 then the email would send and if it was 1 then it would not. I'm sure there is another way to do this but I just needed to get it done ASAP for work as it was something being submitted... so far it's working without issue!

Again, I've never does this before, this was my first Macro experience... so complicating! haha