Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Dec 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Making cells mandatory (2003)

    Is there any way of making the inputting of data onto a call mandatory? I've set the spreadhseet up to tab between the cells that need to be entered, and I need to ensure thatcertain cells are filled in, either by preventing the user from tabbing past these cells, or by preventing teh speadsheet being saved until all the mandatory cells are filled in.

    Thanks in advance...

    Nick

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Making cells mandatory (2003)

    You could create a Workbook_BeforeSave event procedure in the ThisWorkbook module:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Worksheets("Sheet1").Range("A1") = "" Then
    Worksheets("Sheet1").Select
    Range("A1").Select
    MsgBox "You must enter something in cell A1.", vbExclamation
    Cancel = True
    ElseIf Worksheets("Sheet1").Range("B3") = "" Then
    Worksheets("Sheet1").Select
    Range("B3").Select
    MsgBox "You must enter something in cell B3.", vbExclamation
    Cancel = True
    End If
    End Sub

    See attached sample workbook. Clear A1 or B3, then try to save the workbook.

    Important note: if macro security is set to High, or if the user disables macros, the code won't work.

  3. #3
    New Lounger
    Join Date
    Dec 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making cells mandatory (2003)

    You know what - thats pretty damn neat - thankyou very much

    Can this script be edited to prevent the spreadsheet being emailed as well if the fields are blank?

    [img]/forums/images/smilies/smile.gif[/img]

    Nick

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Making cells mandatory (2003)

    If you are going to email it from some macro in excel, you could have it tested.

    If you have the file saved and attach it via a separate email program, it would depend on the email program. You would have to write code to open and check the file before sending it in the email. This could be done with Outlook (though I do not program in Outlook, so can not help) but it seems a little "overkill" to check every attachment to see if it is an excel file to to see if it is one that should have particular cells not empty and then test it.

    This would only prevent you (or whoever you provided the Outlook code to) from sending it, not anyone else...

    Steve

  5. #5
    New Lounger
    Join Date
    Dec 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making cells mandatory (2003)

    nope - they'll either save it first, then email - in which case there's no problem, of they'll choose file|sendto from excel - and it's this I want to catch...

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Making cells mandatory (2003)

    You can enable/disable the menu items as appropriate (30095 is the the "SendTo" ID)

    The enable/ disable code is by Chip Pearson and is from John Walkenbach's site

    Add this code to a module (change the sheet names as appropriate)

    <pre>Option Explicit
    Sub SetSendTo()
    'Tests conditions and sets accordingly
    If Worksheets("Sheet1").Range("A1") = "" Or _
    Worksheets("Sheet1").Range("b3") = "" Then
    EnableControl 30095, False
    Else
    EnableControl 30095, True
    End If
    End Sub

    Sub EnableControl(Id As Integer, Enabled As Boolean)
    'From: http://www.j-walk.com/ss/excel/eee/eee020.txt
    'Enables/disables control given ID number
    Dim CB As CommandBar
    Dim C As CommandBarControl
    For Each CB In Application.CommandBars
    Set C = CB.FindControl(Id:=Id, recursive:=True)
    If Not C Is Nothing Then C.Enabled = Enabled
    Next
    End Sub</pre>


    Add this code to the code of the appropriate worksheet object (in the example it is "Sheet1"). This will check after cells are changed
    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    SetSendTo
    End Sub</pre>


    This goes into the code for thisworkbook:

    <pre>Option Explicit
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    EnableControl 30095, True
    End Sub
    Private Sub Workbook_Deactivate()
    EnableControl 30095, True
    End Sub

    Private Sub Workbook_activate()
    SetSendTo
    End Sub

    Private Sub Workbook_open()
    SetSendTo
    End Sub
    </pre>


    So when file is closed or deactivated the item is enabled. When the workbook is opened or enabled it will be tested to see if it should be active or not.

    Just like Hans mentioned, this also requires that macros are enabled.

    Steve

  7. #7
    New Lounger
    Join Date
    Dec 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making cells mandatory (2003)

    one small flaw...

    when I add this, I obviously need to save the changes - but it won't let me whilst the fields I need filling are still empty. I need to be able to create the form - leaving these fields empty, so that they can be filled in by the users..

    Cheers

    Nick

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Making cells mandatory (2003)

    You can run the following macro. It turns off event handling temporarily while saving the workbook.

    Sub SaveIt()
    Application.EnableEvents = False
    ActiveWorkbook.Save
    Application.EnableEvents = True
    End Sub

    Alternatively, you can execute the three instructions one by one from the Immediate window (press Ctrl+G in the Visual Basic Editor to activate this window).

  9. #9
    New Lounger
    Join Date
    Dec 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making cells mandatory (2003)

    Steve,

    It doesn't like this:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    SetSendTo
    End Sub

    I get the following error:

    "compile error
    Expected: List sperator or )"



    and when I click on ok, it highlights the "as" between "Target" and "Excel"

    Cheers
    Nick

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Making cells mandatory (2003)

    Delete the space before the word As, and type a new space. Do the same for the space after the word As.

    (I suspect that one of these is a non-breaking space, this confuses VBA)

  11. #11
    New Lounger
    Join Date
    Dec 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making cells mandatory (2003)

    Right...

    Sorted that - lol...

    Thanks for all your help so far everyone...

    ...however...

    Turns out the clients firewall/vurus scanner wont let me send attachements containing marcos incse it's a virus... so, any other ideas?

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Making cells mandatory (2003)

    Sometimes zipping the file and then sending it will get past the virus protection

    You could also put it onto a CD and mail it to them...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •