A better way to do this?  
Author Message
muggzzi





PostPosted: Mon Sep 18 12:11:01 CDT 2006 Top

Excel Programming >> A better way to do this?

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

Excel12  
 
 
JLGWhiz





PostPosted: Mon Sep 18 12:11:01 CDT 2006 Top

Excel Programming >> A better way to do this? 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




> 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
>
>
 
 
TomOgilvy





PostPosted: Mon Sep 18 12:25:01 CDT 2006 Top

Excel Programming >> A better way to do this? 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

--
Regards,
Tom Ogilvy



> 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
>
>
 
 
TomOgilvy





PostPosted: Mon Sep 18 14:37:01 CDT 2006 Top

Excel Programming >> A better way to do this? 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.

--
Regards,
Tom Ogilvy




> 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
>
>

>
> > 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
> >
> >
 
 
michael





PostPosted: Tue Sep 19 06:42:09 CDT 2006 Top

Excel Programming >> A better way to do this?
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