Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Require entry in a cell (2003 sp3)

    I have 2 sheets in a workbook that I want users to fill out (may via Data>Form?) or just entering directly into the cells. There will be 4 fields on one sheet that you could enter data in and 6 on the other. I want entering data in all fields to be optional except column A. If they enter data in any other column I don't want them to be able to save the workbook if column A is blank in that row. I have no idea how to do this in Excel, but have created some macros and edited others co-workers have created, so I'm not totally uncomfortable with trying that approach. I just don't know how to go about requiring data in that field. Can anyone point me in the right direction?

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

    Re: Require entry in a cell (2003 sp3)

    You could use the Workbook_BeforeSave event:
    - Press Alt+F11 to activate the Visual Basic Editor.
    - Double-click the ThisWorkbook node of the workbook in the Project Explorer on the left hand side.
    - Copy/paste the following code into the module and substitute the correct sheet name:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wsh As Worksheet
    Dim r As Long
    Dim m As Long
    ' Reference to worksheet
    Set wsh = Worksheets("MySheet")
    ' Last used row
    m = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    ' Loop through the rows
    For r = 1 To m
    If wsh.Range("A" & r) = "" Then
    ' If column A is blank, select cell and issue warning
    wsh.Select
    wsh.Range("A" & r).Select
    MsgBox "Please enter a value!", vbExclamation
    ' Cancel the save and get out
    Cancel = True
    Exit Sub
    End If
    Next r
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Require entry in a cell (2003 sp3)

    I followed your directions and it worked perfectly for one sheet or the other, when I substitue the actual sheet name, but how do I make it check both sheets? I tried coping the macro and putting the second sheet name in. but then I can't use the same name for the macro. I'm sure this is simple, but I can't figure out how to have it do the same check on 2 different sheets. Thanks for this though..it's exactly what I need!

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

    Re: Require entry in a cell (2003 sp3)

    You can create only a single Workbook_BeforeSave event procedure, but you can handle as many sheets as desired:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wsh As Worksheet
    Dim r As Long
    Dim m As Long

    ' Reference to worksheet
    Set wsh = Worksheets("MySheet")
    ' Last used row
    m = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    ' Loop through the rows
    For r = 1 To m
    If wsh.Range("A" & r) = "" Then
    ' If column A is blank, select cell and issue warning
    wsh.Select
    wsh.Range("A" & r).Select
    MsgBox "Please enter a value!", vbExclamation
    ' Cancel the save and get out
    Cancel = True
    Exit Sub
    End If
    Next r

    ' Reference to other worksheet
    Set wsh = Worksheets("OtherSheet")
    ' Last used row
    m = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    ' Loop through the rows
    For r = 1 To m
    If wsh.Range("A" & r) = "" Then
    ' If column A is blank, select cell and issue warning
    wsh.Select
    wsh.Range("A" & r).Select
    MsgBox "Please enter a value!", vbExclamation
    ' Cancel the save and get out
    Cancel = True
    Exit Sub
    End If
    Next r
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Require entry in a cell (2003 sp3)

    I actually figured it out myself!! I copied the existing code to the bottom of the module and put in the second sheet name. Thanks!

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

    Re: Require entry in a cell (2003 sp3)

    Great! It's more satisfying if you can solve a problem yourself. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Require entry in a cell (2003 sp3)

    I should have known once I had this piece that next they would want a second field on each sheet (and not the same one) checked at save time to ensure it had data. Thanks to your comments in the code, I was able to follow the logic and amend it to check the additional field. They also wanted to insert a new column where my old colmun A was and I was able to change where it began looking for the required fields. You're right, it is satisfying to figure it our yourself. Glad to see I could build on what you provided. Thanks again....Patti

Posting Permissions

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