New and need help with conditional locking of button

If you want to specifically identify the incomplete cells in the MessageBox (more like my earlier code), and select those incomplete cells, you could try this modification.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> EmailOrderWhenComplete()<br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> bCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> MissedR <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> FirstAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> myRange = Worksheets("Sheet1").Range("D12:D20")<br>    <SPAN style="color:#00007F">With</SPAN> myRange<br>        <SPAN style="color:#00007F">Set</SPAN> bCell = .Find(What:="")<br>        <SPAN style="color:#00007F">If</SPAN> bCell <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">' Code to email</SPAN><br>            MsgBox "Email sent"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> MissedR = bCell<br>            FirstAddress = bCell.Address<br>            <SPAN style="color:#00007F">Do</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> MissedR = Union(MissedR, bCell)<br>                <SPAN style="color:#00007F">Set</SPAN> bCell = .FindNext(bCell)<br>            <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> bCell <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And bCell.Address <> FirstAddress<br>            MissedR.Select<br>            MsgBox "Please complete the following cell(s): " & vbLf & MissedR.Address(0, 0)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Oh i like that, so that would tell them exactly which cells need to be completed. loving your work!

Can i ask how you got to know how to write this sort of thing from scratch? can you recomend any good books etc? i would love to be able to just write these codes. I can piece things together, and mostly recognise which bits do what, but wouldn't know where to start when starting from scratch.

Sarah_d said:

Can i ask how you got to know how to write this sort of thing from scratch?

Click to expand...

For me, almost entirely using these resources:
1. Using the macro recorder and viewing the resulting code.
2. Reading questions about vba on this board, trying to write a solution, comparing my effort to the "experts" who were posting answers.
3. Reading the built-in Help in the Visual Basic module.
4. Lots of trial and error, and this - - >

Ha Ha Ha ok i guess i should just keep doing what i am doing, and making sure that i don't give in to asking the questions on here too easily, try and figure it out myself first.

Again, thanks very much for all your help on this, it works a treat.

Sarah_d said:

Ha Ha Ha ok i guess i should just keep doing what i am doing, and making sure that i don't give in to asking the questions on here too easily, try and figure it out myself first.

Click to expand...

Exactly right.

Also, you will generally get more people willing to help if you have done that and post your attempted code along with details of what goes wrong or what isn't quite right. The one proviso with that is that if your code is very long, some people will not wade through it trying to work out what it is doing and what needs changing.