Test for range value

[Patrick C. Simonds]

Is there a way to have this check the rng(1, 5) for a value.

If the value is blank I need to fire a msgbox "Sorry but you have clicked to
far down on the worksheet. Please select the next blank row."

If the value is not blank then show the UserForm.



Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If bSELCTIONCHANGE Then

If Not Application.Intersect(Target, Range("B3:M5000")) Is Nothing Then
Lost_And_Found.Show


End If

End Sub

 

[Mike H]

Hi,

I'm not sure how the question related to the code you posted so I offer this
suggestion without any of your code

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target = Cells(1, 5) Then
If Target.Value = "" Then
MsgBox "You clicked in the wrong place"
Else
myval = Target.Value
Lost_And_Found.Show
End If
End If
End Sub

Mike

 

[Patrick C. Simonds]

I meant say that I want it to check the rng(1, 5) of the row above.

 

[Mike H]

and if the actiice cell (target) was (say) C10 where is rng(1,5) in relation
to that?

 

[Patrick C. Simonds]

Thanks,

What my intention is, is to require the user to use the next row. Data is
placed on the worksheet by the UserForm "Lost_And_Found" I just want to
make sure that when the UserForm only shows if the row above has data. So if
the select C10 (to use your example) then there must be data in cell E9.

 

[Mike H]

Hi,

i don't think you can look at an offset to do this because the user could
click in the middle of the used range and there could be dat both above and
below the target cell. try this instead it ensures the target row is directly
below the used range.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Row <> ActiveSheet.UsedRange.Rows.Count + 1 Then
MsgBox "You clicked in the wrong place"
Else
Lost_And_Found.Show
End If

End Sub

 

[Patrick C. Simonds]

Thanks for your help. Here is what I finally came up with. Please feel free
to let me know if it is fatally flawed. I do want the user to be able to
reselect a row so that additional information can be added as it becomes
available.


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Set rng = Cells(ActiveCell.Row, 1)

If rng(-0, 6).Text = "" Then
MsgBox "You clicked in the wrong place. Please select the next blank
row."
Else
If Not Application.Intersect(Target, Range("B3:M5000")) Is Nothing Then
Lost_And_Found.Show

End If

End Sub

 

[Mike H]

Patrick,

My comment is the same as before. Doing it in this way only ensures the cell
in the row above in column 6 is populated, it doesn't guarantee the selected
row is empty.

If you cange this line you get the first unused row and validate your cell

If Target.Row <> ActiveSheet.UsedRange.Rows.Count + 1 Or rng(-0, 6).Text =
"" Then


Mike