MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Force a user to follow our insrtuctions


Posted by karto on July 01, 2000 8:57 AM

i have created a form that requires a user to fill
their details. the problem is they always left something
to fill in and i have to call them back, and always
remind them a dozen of time

what i like to do is like this

i have wrote name in A1 and the user need to fill
their name in B1.also i have wrote sex in A2 and the
user have to fill their sex in B2

i need some kind of code that can prevent the user
from filling cell B2 before they filling cell A2
and i need a message box appear telling them
that they have to fill A2 first before they filling
B2.

hope you all great excel master can help me on this
thanks


Posted by Ryan on July 02, 0100 7:25 AM

Re: it only works for two times after that nothing happened could u repair the code, please...

Here you go. If you need more then B1:B5, you can change the code where it says:
Set VRange = Range("B1:B5"). Let me know how it turns out!

Ryan

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim VRange As Range
Set VRange = Range("B1:B5")
Dim NewData As Range
Dim DataPage As Range
Dim Cell As Range
Application.ScreenUpdating = False

On Error Resume Next


If Union(Target, VRange).Address = VRange.Address Then
For Each Cell In VRange
If Cell.Value = "" Then
If Cell.Address >= ActiveCell.Address Then Exit Sub
MsgBox "You must first enter " & Cell.Offset(0, -1).Value, _
vbOKOnly + vbInformation, "Enter Name"
Application.EnableEvents = False
Cell.Select
Application.EnableEvents = True
Exit Sub
End If
Next Cell
End If
Application.ScreenUpdating = True
End Sub

Posted by Karto on July 02, 0100 9:20 AM

you make my day Ryan.god bless you it work and my dream come true.thanks

Posted by Ryan on July 01, 0100 9:27 AM

Karto,
Here is code that will take care of your example. If B1 is empty(where the name is), then it will pop a little box up that says they missed on and that they must first fill out there name. Then when they fill out there name all is back to normal. This goes in the Sheet module. Hope it works, let me know!

Ryan

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim VRange As Range
Dim V2Range As Range
Set VRange = Range("B1")
Set V2Range = Range("B2")
Dim NewData As Range
Dim DataPage As Range

Application.ScreenUpdating = False

On Error Resume Next

If Union(Target, V2Range).Address = V2Range.Address Then

If Range("B1").Value = "" Then
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "You missed one"
.ErrorTitle = ""
.InputMessage = "You must enter your name first"
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
VRange.Locked = False
ActiveSheet.Protect

Else
ActiveSheet.Unprotect
With V2Range.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.InputMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End If
Application.ScreenUpdating = True
End Sub

Posted by Ryan on July 01, 0100 9:30 AM

Karto,
the seconde part of the code can be cleaned up to this, sorry. Let me know how it turns out.
Ryan


Else
ActiveSheet.Unprotect
V2Range.Validation.Delete
End If

Posted by Karto on July 01, 0100 5:07 PM

it only works for two times after that nothing happened could u repair the code, please...


and could you please write the whole code
coz i an't catch it.thanks

Application.ScreenUpdating = False On Error Resume Next If Union(Target, V2Range).Address = V2Range.Address Then If Range("B1").Value = "" Then With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = "You missed one" .ErrorTitle = "" .InputMessage = "You must enter your name first" .ErrorMessage = "" .ShowInput = True .ShowError = True End With VRange.Locked = False ActiveSheet.Protect Else ActiveSheet.Unprotect With V2Range.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .InputMessage = "" .ShowInput = True .ShowError = True End With End If End If Application.ScreenUpdating = True

Posted by Ryan on July 01, 0100 8:15 PM

Re: it only works for two times after that nothing happened could u repair the code, please...

Here you go, I changed it so it pops up a message box instead of the validation box. I liked it better! Hope you like it too. Let me know how it works.

Ryan

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim VRange As Range
Set VRange = Range("B2")
Dim NewData As Range
Dim DataPage As Range

Application.ScreenUpdating = False

On Error Resume Next

If Union(Target, VRange).Address = VRange.Address Then

If Range("B1").Value = "" Then
MsgBox "You must first enter your name", vbOKOnly + vbInformation, "Enter Name"
Range("B1").Select
End If
End If
Application.ScreenUpdating = True
End Sub

Posted by Karto on July 01, 0100 9:01 PM

Re: it only works for two times after that nothing happened could u repair the code, please...

Ryan,

you are great..but how am to use
this code if i want to expand it..
let's say now the order is B1 and B2,
the user have to fill B1 before they
can fill B2..let say i want to use
the code until B5..if they don't fill
B1 and B2, they can't fill B3 and until
B5 they have to fill it step by step
could you spare you time in helping on this once
more.thank you very much