Prev: Include guid (uuid) from Access in Excel
Next: is it possible to create Workbook Navigation
From: Faraz A. Qureshi on 11 Jan 2010 06:07
In the following code when the TST(Rng) function is applied upon four cells
simply (i.e. by Ctrl+Enter), while Rng selected for the same being cells not
equal to 4 in number, results into reappearing of a msgbox 4 times, unless
the function is applied as an array formula.

How to have the same presenting the message:

"Activecells' and the Selection's ranges should be of the Exactly Same Size."

be presented only once and TST returning a zero?

Function TST(Rng As Range)
Dim AR As Range
Set AR = Selection
If AR.Cells.Count <> Rng.Cells.Count Then
MsgBox "Activecells' and the Selection's ranges" & vbNewLine & _
" should be of the Exactly Same Size."
Exit Function
Else
TST = "OK"
End If
End Function

--
Thanx in advance,
Best Regards,

Faraz
From: paul.robinson on 11 Jan 2010 07:59
Hi
I can't see anything in the code you have shown which will make the
message box appear more than once. The problem is then with the code
calling this function. Can you show us the code which uses this
function?
It might help to change your function a little too and separate the
test and the message box:

Function TST(Rng As Range) as Boolean
Dim AR As Range
Set AR = Selection
If AR.Cells.Count <> Rng.Cells.Count Then
TST = False
Else
TST = True
End If
End Function

In your calling code you would now have

If TST(Rng) = True then
'your code
Else
MsgBox "Activecells' and the Selection's ranges" & vbNewLine & _
" should be of the Exactly Same Size."
End if

regards
Paul

On Jan 11, 11:07 am, Faraz A. Qureshi
<FarazAQure...(a)discussions.microsoft.com> wrote:
> In the following code when the TST(Rng) function is applied upon four cells
> simply (i.e. by Ctrl+Enter), while Rng selected for the same being cells not
> equal to 4 in number, results into reappearing of a msgbox 4 times, unless
> the function is applied as an array formula.
>
> How to have the same presenting the message:
>
> "Activecells' and the Selection's ranges should be of the Exactly Same Size."
>
> be presented only once and TST returning a zero?
>
> Function TST(Rng As Range)
>     Dim AR As Range
>     Set AR = Selection
>     If AR.Cells.Count <> Rng.Cells.Count Then
>         MsgBox "Activecells' and the Selection's ranges" & vbNewLine & _
>         " should be of the Exactly Same Size."
>         Exit Function
>     Else
>         TST = "OK"
>     End If
> End Function
>
> --
> Thanx in advance,
> Best Regards,
>
> Faraz

 | 
Pages: 1
Prev: Include guid (uuid) from Access in Excel
Next: is it possible to create Workbook Navigation