Solved

Excel - Cell Content Must Contain Data

Posted on 1998-11-02
17
414 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
Comment Utility
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
Comment Utility
Is this in a standard sheet or can you gon through a form (OK button and VB content check) ?
0
 

Author Comment

by:gbuck
Comment Utility
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
 

Author Comment

by:gbuck
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:gbuck
Comment Utility
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
Comment Utility
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
Comment Utility
Doesn't prevent a Tab or space or arrow keys.  see my last comments
0
 

Expert Comment

by:steath_acid
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Good answer.  Plan on using.  Thanks
0
 

Author Comment

by:gbuck
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now