Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.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 |
#2
Posted to microsoft.public.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 "michael.beckinsale" wrote: 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 |
#3
Posted to microsoft.public.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 "michael.beckinsale" wrote: 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 |
#4
Posted to microsoft.public.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 "JLGWhiz" wrote: 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 "michael.beckinsale" wrote: 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 |
#5
Posted to microsoft.public.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|