Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel - Cell Content Must Contain Data

Posted on 1998-11-02
17
Medium Priority
?
433 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 210 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 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