Solved

Excel - Cell Content Must Contain Data

Posted on 1998-11-02
17
419 Views
Last Modified: 2008-03-10
How do I validate a cell's content for data, i.e., Ascii codes 32 thru 126.  I want to require the user to input something into the cell.
0
Comment
Question by:gbuck
  • 8
  • 4
  • 3
  • +2
17 Comments
 

Expert Comment

by:steath_acid
ID: 1614273
do you have a specific number range if so I know how to do it or atleats does it have to be a whole number
0
 
LVL 1

Expert Comment

by:dtavassoli
ID: 1614274
Is this in a standard sheet or can you gon through a form (OK button and VB content check) ?
0
 

Author Comment

by:gbuck
ID: 1614275
There is not a specific necessarily a specific range.  As the user tabs through a ss, I would like to give a warning that an error will occur if the cell is left blank.
Their are twelve sheets in a workbook, Excel 5.0
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:gbuck
ID: 1614276
Yes, I can specify a range.  I am only concerned that they enter at least the first row on each sheet.  FYI..The first cell is a drop-down list. We are using data validation, but if the user cancels the error, they are able to tab out without selecting one of the list items.
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1614277
You can use try something like that:
Sub CheckIt()
  If IsEmpty(ActiveSheet.Cells(1, 1)) Then
    MsgBox "There is no data in cell!"
  End If
End Sub

Sub Ini()
 Application.OnSheetDeactivate = "CheckIt"
 Application.OnKey "{TAB}", "CheckIt" ' and for any another key : arrows etc
End Sub
0
 

Author Comment

by:gbuck
ID: 1614278
That would probably work if I knew the steps to do it.  Kinda green at this.  Any suggestions would be appreciated
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1614279
You have to choose Insert|marcos|module and place code there. Stay on Sub Ini() and press F5. (By the way - you can rename Sub Ini() to Sub Auto_Open() - so it'll be activate on Workbook open )
Then go onto your worksheet and look at how it works (when user press Tab, it checks for Cells(1,1) - "A1" and open messagebox with reminder, if cell is empty. Read VBA Help for Cells, OnKey etc - learn VBA. Good luck
0
 
LVL 1

Expert Comment

by:ajmendes
ID: 1614280
Hi,
You can put some code in cell change event, on Excel's VB editor.

Private Sub Worksheet_Change(ByVal Target As Excel.Range), where Target represents the changed cell.

Then you must test if this target belongs to your range and if it is then you check for the size of cell value.

Bye.
0
 

Author Comment

by:gbuck
ID: 1614281
I would rather accept the answer submitted by vboukhar.  That is the code that I have been trying and is more self explanatory than the other.  Either way, what's the protocal?
0
 

Expert Comment

by:steath_acid
ID: 1614282
Select the cell you want to validat and click Data, Validarion.
Than select whole number.  IN minimum put 0 or 1 which ever is valid, then put the maximum in maximum.  Then under the error elert tab you can specify a specific message for the error.
0
 

Author Comment

by:gbuck
ID: 1614283
Doesn't prevent a Tab or space or arrow keys.  see my last comments
0
 

Expert Comment

by:steath_acid
ID: 1614284
Yes it does if you uncheck "ignore blank" the only problem is it only works after they click on the cell.
0
 

Author Comment

by:gbuck
ID: 1614285
But when the msgbox appears, the user enters the cancel key, the field is not vailidated.
0
 
LVL 5

Accepted Solution

by:
vboukhar earned 70 total points
ID: 1614286
Look at - there are some changes (Cell "A1" on first worksheet):
Sub CheckCell()
 If IsEmpty(Worksheets(1).Cells(1, 1)) Then
   MsgBox "There is no data in cell!"
   Worksheets(1).Activate
   Cells(1, 1).Select
  End If
End Sub

Sub Ini()
Application.OnSheetDeactivate = "CheckCell"
Application.OnKey "{TAB}", "CheckCell" ' and for any another key : arrows etc
Application.OnKey "{ENTER}", "CheckCell"
Worksheets(1).OnEntry = "CheckCell"
End Sub
0
 

Author Comment

by:gbuck
ID: 1614287
Good answer.  Plan on using.  Thanks
0
 

Author Comment

by:gbuck
ID: 1614288
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim m As Integer
Dim n As Integer
Dim r As Integer
Dim c As Integer
If Worksheets(2).Cells(1, 1) = "grady" Then
    Exit Sub
End If
    For n = 1 To 3
    If n = 2 Then
        m = 3
    Else
        m = 5
    End If
    For c = 1 To m
        Let r = 1
        If IsEmpty(Worksheets(n).Cells(r, c)) Then
            Worksheets(n).Activate
            Cells(r, c).Select
            Cancel = True
            MsgBox "Cell Cannot be Blank. Enter ""N/A"" if Unknown."
            Exit Sub
        End If
    Next c
Next n
End Sub

this is the code i ended up with, as i had several sheets and several cells to verify.  unfortunately, when i try to move this to excel 5.0 (which my client is using), the macro does not appear to be working.  I tried changing it to sub auto_close, but no luck.  if anyone reads this and has a suggestion, please let me know.

0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1614289
You  have to remove parameters in brackets and initialize your function with Auto_Open()
Sub Auto_Open()
  Application.OnSheetDeactivate = "Workbook_BeforeSave"
End Sub
Sub Workbook_BeforeSave()
 ' .... body - remove string with Cancel = True
End Sub

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question