GoTo problem.

  • Thread starter Thread starter johnlute
  • Start date Start date
J

johnlute

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
 
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
 
Thanks, Patrick!!!

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










- Show quoted text -
Click to expand...
 
Thanks, muddan!!!

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 CELLC5")
               If ans = "" Then GoTo first
             Target.Offset(0, 1).Value = ans
End If

End Sub








- Show quoted text -
Click to expand...
 
Back
Top