A better way to do this?

[michael.beckinsale]

Hi All,

Is there a more efficient / better way of doing this?

The code below is used in a user form where the user selects a single
cell using the RefEdit control. It ensures the user only selects a
single cell, within a specified range, and a selection is actually
made. The form is not to be unloaded until the correct conditions
exist.

All help gratefully received.

Private Sub cmdEnter_Click()
Dim Cella As Range
If rngCell.Value = "" Then
MsgBox ("You must select a single cell in the range H5:I11")
Exit Sub
Else
Set Cella = Range(rngCell.Value)
End If
If Cella.Cells.Count > 1 Or Cella Is Nothing Then
MsgBox ("You must select a single cell in the range H5:I11")
Exit Sub
End If
If Intersect(Cella, ActiveSheet.Range("H5:I11")) Is Nothing Then
MsgBox ("You must select a single cell in the range H5:I11")
Exit Sub
Else
Cella.Select
Unload frmCell
End If

End Sub

Regards

Mike Beckinsale

 

[Guest]

I'm thinking that this might be all your need to tie it down"

If Cella.Cells.Count <> 1 Or Cella Is Nothing Then
MsgBox("You must select a single cell in the range H5:I11")
Exit Sub
End If

 

[Guest]

I can't say it is any better - only a little different. I think you have to
perform all the checks you are doing.

Private Sub cmdEnter_Click()
Dim Cella As Range
Dim bBad as Boolean
On Error Resume Next
Set Cella = Range(rngCell.Value)
On Error goto 0
if Cella is nothing then
bBad = True
elseif cella.count > 1 then
bBad = True
elseif Intersect(ActiveSheet.Range("H5:I11"),Cella) is nothing then
bBad = True
End if
if bBad then
MsgBox ("You must select a single cell in the range H5:I11")
Exit Sub
End If
Cella.Select
Unload frmCell
End Sub

 

[Guest]

if cella is nothing, then the first condition will raise an error. So no,
that isn't all that is needed to tie it down.

 

[michael.beckinsale]

Tom / GWhiz,

Sorry for the delay replying. Many thanks for your responses.

Gwhiz, as far as l can see your code does not check that the selected
cell is in the valid range ie the Intersect bit.

Tom, appreciate the alternate method. It just seems to me that both
bits of code is a bit 'clunky' for what appears to be a simple check.

Regards

Michael