Results 1 to 9 of 9

Thread: Excel

  1. #1
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel

    I am using Excel97sr2 and need to know of a way to prevent a cell from being left blank during data entry of a worksheet. Any help would be appreciated.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel

    Can you tell us at what point in the data entry the cell would be considered to be left blank? In other words, at what point should the user be prevented from doing something (what?) before the cell in filled in?
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel preventing nonblank data entry

    I'm not sure if there is an easy way to accomplish this because I need to ensure a certain set of cells (all in one comumn) contains a numeric value before the user closes the worksheet. The initial value in these cells is null. I want to make sure that these cells are not skipped or that a user forgets to enter something(I have turned validation on to check the range of values and to ensure numeric data). Thanks for your reply and any help

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel preventing nonblank data entry

    You can do this fairly easily by creating a Workbook_BeforeClose event procedure. This procedure would simply loop through all of the cells checking for a null. When it finds one, it would activate the sheet containing the cell, select the cell, and then set the Cancel parameter passed to the procedure to true to cancel closing the workbook. It would probably also be nice to display a message to the user telling him why he can't close the workbook.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel preventing nonblank data entry

    Thanks so much for your speedy response however, where can I obtain information on creating such a procedure? Standard help in Excel does not address such issues. Again I appreciate your help. Anxious in Atlanta...

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel preventing nonblank data entry

    Are you familiar with writing VBA/Macros at all? If so, everything you need is in the help files, you just need to know how to find the place to put the VBA code.

    Go to the VBA Editor (Alt/F11 from the spreadsheet). In the project explorer (usually on the left side of the screen if you haven't moved it or closed the window), look for the "Workbook" object for your project. Right click on the Workbook object and select "View code" from the pop up menu. That should give you a VBA Editor window on the right side of the screen. At the top left side of this window is a drop down list that should say (General). Click the down arrow in that box to drop the list down. Select "Workbook" from the list (it should be the only item other than (General))." The code edit window should now show an empty Sub procedure named Workbook_Open. Now, in the drop down list at the top right, drop it down and select "Workbook_BeforeClose from the list. The code window should now contain a second procedure named Workbook_BeforeClose. You put your code to check the cells here.

    If you are having problems writing the code, get back to us and we will try to help. We will need to know the name of the sheet and what cells you want to check.
    Legare Coleman

  7. #7
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel preventing nonblank data entry

    Thanks Legare, I would appreciate your help. The workbook will consist of 9 sheets labeled "school1" through "school9" and the cells within the worksheets are named "ag1.1" through "ag1.15"(non-contiguous). But if you can help with one sheet, I should be able to follow you code for the other 8. Many thanks.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel preventing nonblank data entry

    Something like this should do what you want to do:

    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim I As Long, J As Long
    For I = 1 To 9
    For J = 1 To 15
    If Range("school" & I & "!ag1." & J).Value = "" Then
    Worksheets("school" & I).Activate
    Range("ag1." & J).Select
    Application.Goto "a1", True
    MsgBox "You must enter a value in ag1." & J, vbExclamation + vbOKOnly
    Cancel = True
    Exit Sub
    End If
    Next J
    Next I
    Cancel = False
    End Sub
    </pre>

    Legare Coleman

  9. #9
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel preventing nonblank data entry

    Legare:
    Thank you so much for your efforts, I shall use the code for my worksheets. I am working today to complete the task, again I appreciate your much needed help.

    Jerome Morgan

Posting Permissions

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