How can I require user to fill cell1 also if he/she fille cell2?

[mimitan]

Greeting to All,

Question 1)
I am trying to implement something like this: I have in Column A as Date and Column B as Quantity.

Date Qty
07/25/05 1

if a user enter a number in the Qty field and go to different cell and left the Date blank, a message should pop up and ask the user to enter a date for that Qty before he/she can move on.

Questions 2: Is there something I can do to show a reminder message to users to log their name in Cell C1, for example, anytime they save the sheet and C1 is blank?

Thanks for helps

 

[SkipVought]

Hi,

Take a look at the Worksheet_Change event. If the user enters a value in column B AND the corresponding cell in column A is empty, then...

Skip,
[sub]
[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![/sub]

 

[shetlandbob]

1.On the

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

You can check the cells for entries and pop up message boxes etc...

2. You should be able to capture the OnWorkbook Close event. Then you can check for cell C1 value. Other way is again in the above routine, if worksheet changes make sure user enters name in C1 (you can also get excel to enter the windows user ID login name on Worksheet Open call.



Robert Cumming

http://www.consultantengineer.co.uk

 

[shetlandbob]

You beat me to it skip!



Robert Cumming

http://www.consultantengineer.co.uk

 

[mimitan]

I am sorry. I tried but was not successful. I have so little knowledge on VBA, and was unable to get the syntax correctly. Could you please show me more detail. Thanks!

 

[SkipVought]

Exactly WHAT did you enter and exactly WHERE?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[mimitan]

Hi Skip, I think I got this working and this is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Column = 2) Then
If (Target.Value > 0) Then
If (Cells((Target.Row), 1) = "") Then
MsgBox "Input Date"
End If
End If
End If

End Sub

It is working but I am not sure this is a good way to put it. and How can I make it in a way that the user HAS to enter a date if not the message keeps poping up, and can not continue unless a date is enter next to it.
Thanks for your helps

 

[tytus2005]

Maybe you should try something like this:

In your Worksheet_Change subroutine replace MsgBox with InputBox like this

Dim x as Variant
...

Do
  x = InputBox("Enter date", "Date")
Loop While (x = False Or x = Empty)

If user submits without entering anything x will be equal empty, if user cancels x will be equal to false.

 

[tytus2005]

Of course you should also add to loop condition something like:

Do
  x = InputBox( "Enter date", "Date" )
Loop While (x = False Or x = Empty Or Not IsDate(x))