How do I require data entry in a cell before moving to the next cell

[Matticos]

I need to be able to force the user to fill in specific cells in the row,
from drop down boxes, as they move across the worksheet. If they do not
choose from the drop-down box, I need the cursor to stay in that cell and
force them to choose. Ive got coding for a single Cell.

Put the following line in a standard module:
Public checkit As Boolean

Put the following worksheet event macro in the worksheet code area:

[VBA]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set b9 = Range("B9")
Set t = Target
If Intersect(t, b9) Is Nothing Then
If checkit Then
If b9.Value = "" Then
Application.EnableEvents = False
b9.Select
Application.EnableEvents = True
Else
checkit = False
End If
End If
Else
checkit = True
End If
End Sub

[/VBA]
However i want to use it on around 5 different cells within the sheet.
I tried copying the same forumla with different cells. but i get a error message of "compile error: ambiguous name Worksheet_SelectionChange(ByVal Target As Range) HELP please?

[tstav]

Hi Matticos,
welcome to the forum.

Let me see if I have understood well the various requirements you are stating.

You want the user to fill only 5 specific cells of a worksheet.
The values of the cells are to be selected from drop-down menus appearing when the user clicks in these cells (the user is not allowed to type in values by himself).
The user, once he has clicked in one of these cells, will not be allowed to leave the cell, unless he has filled it.

Correct? Any misunderstandings? Anything to add?

PS. When posting code, it is better to indent it. It makes it easier to read. To do that, press the Edit button, and when the Editor comes up, select (highlight) your code and press the green button that says VBA.

[tstav]

Also: what happens after the cells are filled? Just give a general idea.

[Matticos]

basically, ive made a database for tools, and for it to be efficient, i dont want to cell to be left blank. I want it to be similar to the MS Access requirement? So when the data is being added, i dont want it to be left blank.

If you try that VBA code quickly, you'll understand more

[tstav]

I can see what the code is intended to do. You just need to initialize the checkit variable to True, in order for the code to work. Otherwise I'm free to click anywhere i like.

[Simon Lloyd]

you could use something like this:
[VBA]Dim MyCell As Range
Dim Rng As Range
Set Rng = Sheets("Sheet1").Range("A1:A100") 'set your range
For Each MyCell In Rng 'check each cell in that range
If IsEmpty(MyCell) Then 'if it is empty
MsgBox "Empty Cells exist" 'actions To Do If True
MyCell.Select 'goto the offending cell
Exit Sub
End If
Next [/VBA]

[Matticos]

Basically that code does exactly has i want. i just want to do more than one cell. for example that does b9.

I know to change i just need to change all the b9's to E4's for example. however Excel isnt letting me use that code more than once? i get the error


"compile error: ambiguous name Worksheet_SelectionChange(ByVal Target As Range)" as a pop up

[tstav]

I have kept your variables and style of code and I'm giving you one of several ideas to work on.
Try this and see if it suits your needs.

I'll have to go now. I'll be back tomorrow.

Regards,
tstav
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
checkit = True
Set b9 = Range("B9")
Set c9 = Range("C9")
Set t = Target
If Intersect(t, b9) Is Nothing Or Intersect(t, c9) Is Nothing Then
If checkit Then
Select Case vbNullString
Case b9.Value
Application.EnableEvents = False
b9.Select
Application.EnableEvents = True
Case c9.Value
Application.EnableEvents = False
c9.Select
Application.EnableEvents = True
Case Else
checkit = False
End Select
End If
Else
checkit = True
End If
End Sub
[/vba]
[vba]Sub ValidateCells()
Dim strS As String
strS = "value1,value2,value3"
With Range("B9").Validation '<-- create drop down list
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=strS
.IgnoreBlank = True
.InCellDropdown = True
End With
With Range("C9").Validation '<-- create drop down list
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=strS
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub[/vba]

[Matticos]

The first one worked perfectly!! Thanks alot. thats exactly what i needed

[tstav]

Which is the first one?

[Matticos]

This one!


[VBA]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
checkit = True
Set b9 = Range("B9")
Set c9 = Range("C9")
Set t = Target
If Intersect(t, b9) Is Nothing Or Intersect(t, c9) Is Nothing Then
If checkit Then
Select Case vbNullString
Case b9.Value
Application.EnableEvents = False
b9.Select
Application.EnableEvents = True
Case c9.Value
Application.EnableEvents = False
c9.Select
Application.EnableEvents = True
Case Else
checkit = False
End Select
End If
Else
checkit = True
End If
End Sub
[/VBA]

[tstav]

Matticos,
I have recoded the whole thing, trying to make the code do some more e.g. add drop down lists to the cells you want filled by the user.
As you will see, I have dropped your coding style and incorporated Simon's (post#6), since that is really the way you (as well) will be coding later on...
Create a new excel file(test file).
Place the 'Workbook_Open' sub in the 'ThisWorkbook' code module and the 'Worksheet_SelectionChange' sub in the Worksheet's code module.
Save the file, close it and reopen it.
Dropdowns will be automatically filled with fake data in cells B9 and D9.
[vba]Private Sub Workbook_Open()
'Force user to fill cells
Dim strB As String, strD As String
strB = "B1,B2,B3" '<--Supply elements of list
With Range("B9").Validation 'create drop down list
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=strB
.IgnoreBlank = True
.InCellDropdown = True
End With
strD = "D1,D2,D3" '<--Supply elements of list
With Range("D9").Validation 'create drop down list
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=strD
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub
[/vba]
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Force user to fill cells
Dim rng As Range, cel As Range
Set rng = Union(Range("B9"), Range("D9")) 'Add here the cells to be filled
On Error GoTo ErrorHandler
If Intersect(Target, rng) Is Nothing Then
For Each cel In rng
If cel.Value = "" Then
Application.EnableEvents = False
cel.Select
Exit For
End If
Next
ErrorHandler:
Application.EnableEvents = True
End If
End Sub[/vba]