I'm not very familiar with Excel but managed to construct this:
Sub DDUOM_Change()
' DDUOM_Change Macro
' Macro recorded 10/22/2009 by jlute
If Range("B5") <= 4 Then
Range("C5") = Null
End If

If Range("B5") 4 Then
MsgBox "Density is required!"
End If

End Sub

I want the cursor to go to cell C5 after clearing the message box
however I can't seem to work that out. I know GoTo is the right
command but I keep sending the debugger into fits.

Also, once the cursor is in C5 and If B5 is 4 I don't want the user
to be able to exit C5 until a value is entered. How can I do that?

Thanks for your help!!!

Right click on sheet name | click view code
Paste the below code


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address < "$B$5" Then Exit Sub

If Target.Value <= 4 Then
Target.Offset(0, 1).Value = Clear
End If

first:
If Target.Value 4 And Target.Offset(0, 1).Value = "" Then
Target.Offset(0, 1).Select
ans = InputBox("Density Required", "Enter Value in CELL C5")
If ans = "" Then GoTo first
Target.Offset(0, 1).Value = ans
End If

End Sub


On Oct 23, 5:58*pm, johnlute wrote:
I'm not very familiar with Excel but managed to construct this:
Sub DDUOM_Change()
' DDUOM_Change Macro
' Macro recorded 10/22/2009 by jlute
* If Range("B5") <= 4 Then
* *Range("C5") = Null
* End If

* If Range("B5") 4 Then
* * MsgBox "Density is required!"
* End If

End Sub

I want the cursor to go to cell C5 after clearing the message box
however I can't seem to work that out. I know GoTo is the right
command but I keep sending the debugger into fits.

Also, once the cursor is in C5 and If B5 is 4 I don't want the user
to be able to exit C5 until a value is entered. How can I do that?

Thanks for your help!!!

Sub DDUOM_Change()
' DDUOM_Change Macro
' Macro recorded 10/22/2009 by jlute
If Range("B5") <= 4 Then
Range("C5") = ""
End If

If Range("B5") 4 Then
MsgBox "Density is required!"
Range("C5").Select
End If
End Sub

you can use the sheet's selection_change event to keep C5 selected

right click the sheet's tab and select view code, then paste this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("C5") = "" And Range("B5") 4 Then
Range("C5").Select
End If
End Sub



"johnlute" wrote:

I'm not very familiar with Excel but managed to construct this:
Sub DDUOM_Change()
' DDUOM_Change Macro
' Macro recorded 10/22/2009 by jlute
If Range("B5") <= 4 Then
Range("C5") = Null
End If

If Range("B5") 4 Then
MsgBox "Density is required!"
End If

End Sub

I want the cursor to go to cell C5 after clearing the message box
however I can't seem to work that out. I know GoTo is the right
command but I keep sending the debugger into fits.

Also, once the cursor is in C5 and If B5 is 4 I don't want the user
to be able to exit C5 until a value is entered. How can I do that?

Thanks for your help!!!
.

Thanks, Patrick!!!

On Oct 23, 10:45*am, Patrick Molloy
wrote:
Sub DDUOM_Change()
' DDUOM_Change Macro
' Macro recorded 10/22/2009 by jlute
* If Range("B5") <= 4 Then
* *Range("C5") = ""
* End If

* If Range("B5") 4 Then
* * MsgBox "Density is required!"
* *Range("C5").Select
* End If
End Sub

you can use the sheet's selection_change event to keep C5 selected

right click the sheet's tab and select view code, then paste this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
* * If Range("C5") = "" And Range("B5") 4 Then
* * * * Range("C5").Select
* * End If
End Sub



"johnlute" wrote:
I'm not very familiar with Excel but managed to construct this:
Sub DDUOM_Change()
' DDUOM_Change Macro
' Macro recorded 10/22/2009 by jlute
* If Range("B5") <= 4 Then
* *Range("C5") = Null
* End If

* If Range("B5") 4 Then
* * MsgBox "Density is required!"
* End If

End Sub

I want the cursor to go to cell C5 after clearing the message box
however I can't seem to work that out. I know GoTo is the right
command but I keep sending the debugger into fits.

Also, once the cursor is in C5 and If B5 is 4 I don't want the user
to be able to exit C5 until a value is entered. How can I do that?

Thanks for your help!!!
.- Hide quoted text -

- Show quoted text -

Thanks, muddan!!!

On Oct 23, 10:30*am, muddan madhu wrote:
Right click on sheet name | click view code
Paste the below code

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address < "$B$5" Then Exit Sub

If Target.Value <= 4 Then
* * * Target.Offset(0, 1).Value = Clear
End If

first:
If Target.Value 4 And Target.Offset(0, 1).Value = "" Then
* * Target.Offset(0, 1).Select
* * * * ans = InputBox("Density Required", "Enter Value in CELL C5")
* * * * * * * *If ans = "" Then GoTo first
* * * * * * *Target.Offset(0, 1).Value = ans
End If

End Sub

On Oct 23, 5:58*pm, johnlute wrote:



I'm not very familiar with Excel but managed to construct this:
Sub DDUOM_Change()
' DDUOM_Change Macro
' Macro recorded 10/22/2009 by jlute
* If Range("B5") <= 4 Then
* *Range("C5") = Null
* End If

* If Range("B5") 4 Then
* * MsgBox "Density is required!"
* End If

End Sub

I want the cursor to go to cell C5 after clearing the message box
however I can't seem to work that out. I know GoTo is the right
command but I keep sending the debugger into fits.

Also, once the cursor is in C5 and If B5 is 4 I don't want the user
to be able to exit C5 until a value is entered. How can I do that?

Thanks for your help!!!- Hide quoted text -

- Show quoted text -