Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel - Cell Content Must Contain Data

Posted on 1998-11-02
17
Medium Priority
?
436 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

879 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