Solved

Excel - Cell Content Must Contain Data

Posted on 1998-11-02
17
423 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

734 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