I have a textbox that is getting a range address.
If the total of columns or row is higher than 5 I want to show an MsgBox and
clear its contents. It works fine but when the value becomes "" the change
event is obviously triggered but in this case since tha value is nothing it
still validates the number of columns/ros as higher than 5 and prompts the
message again

Private Sub TextBox1_Change()

If Range(TextBox1.Value).Columns.Count > 5 Or
Range(TextBox1.Value).Rows.Count > 5 Then

MsgBox "Limit of 5 columns and rows"
TextBox1.Value = ""

Else
Call AnotherMacro

End If
End Sub

I tried the afterupdate event but it doesn't work.

Thanks in advance.

Does this work:

Private Sub TextBox1_Change()
if TextBox1.Value = "" then exit sub

If Range(TextBox1.Value).Columns.Count > 5 Or
Range(TextBox1.Value).Rows.Count > 5 Then

MsgBox "Limit of 5 columns and rows"
TextBox1.Value = ""

Else
Call AnotherMacro

End If
End Sub

LuisE wrote:
> I have a textbox that is getting a range address.
> If the total of columns or row is higher than 5 I want to show an MsgBox and
> clear its contents. It works fine but when the value becomes "" the change
> event is obviously triggered but in this case since tha value is nothing it
> still validates the number of columns/ros as higher than 5 and prompts the
> message again
>
> Private Sub TextBox1_Change()
>
> If Range(TextBox1.Value).Columns.Count > 5 Or
> Range(TextBox1.Value).Rows.Count > 5 Then
>
> MsgBox "Limit of 5 columns and rows"
> TextBox1.Value = ""
>
> Else
> Call AnotherMacro
>
> End If
> End Sub
>
> I tried the afterupdate event but it doesn't work.
>
> Thanks in advance.

Darren, thanks for the prompt response
I found another approach that works turning the Application.EnableEvents =
False




Private Sub TextBox1_Change()
On Error GoTo XIT
Application.EnableEvents = False

If Range(TextBox1.Value).Columns.Count > 5 Or
Range(TextBox1.Value).Rows.Count > 5 Then

MsgBox "Limit of 5 columns and rows"
TextBox1.Value = ""

Else
Call AnotherMacro

End If

XIT:
Application.EnableEvents = True

End Sub

> LuisE wrote:
> > I have a textbox that is getting a range address.
> > If the total of columns or row is higher than 5 I want to show an MsgBox and
> > clear its contents. It works fine but when the value becomes "" the change
> > event is obviously triggered but in this case since tha value is nothing it
> > still validates the number of columns/ros as higher than 5 and prompts the
> > message again
> >
> > Private Sub TextBox1_Change()
> >
> > If Range(TextBox1.Value).Columns.Count > 5 Or
> > Range(TextBox1.Value).Rows.Count > 5 Then
> >
> > MsgBox "Limit of 5 columns and rows"
> > TextBox1.Value = ""
> >
> > Else
> > Call AnotherMacro
> >
> > End If
> > End Sub
> >
> > I tried the afterupdate event but it doesn't work.
> >
> > Thanks in advance.
>

After all it worked better than the other approach because I had references
to other objects. Thank you

"Darren Hill" wrote:

> Does this work:
>
> Private Sub TextBox1_Change()
> if TextBox1.Value = "" then exit sub
>
> If Range(TextBox1.Value).Columns.Count > 5 Or
> Range(TextBox1.Value).Rows.Count > 5 Then
>
> MsgBox "Limit of 5 columns and rows"
> TextBox1.Value = ""
>
> Else
> Call AnotherMacro
>
> End If
> End Sub
>
> LuisE wrote:
> > I have a textbox that is getting a range address.
> > If the total of columns or row is higher than 5 I want to show an MsgBox and
> > clear its contents. It works fine but when the value becomes "" the change
> > event is obviously triggered but in this case since tha value is nothing it
> > still validates the number of columns/ros as higher than 5 and prompts the
> > message again
> >
> > Private Sub TextBox1_Change()
> >
> > If Range(TextBox1.Value).Columns.Count > 5 Or
> > Range(TextBox1.Value).Rows.Count > 5 Then
> >
> > MsgBox "Limit of 5 columns and rows"
> > TextBox1.Value = ""
> >
> > Else
> > Call AnotherMacro
> >
> > End If
> > End Sub
> >
> > I tried the afterupdate event but it doesn't work.
> >
> > Thanks in advance.
>

Glad to help

Darren

LuisE wrote:
> After all it worked better than the other approach because I had references
> to other objects. Thank you
>
> "Darren Hill" wrote:
>
>> Does this work:
>>
>> Private Sub TextBox1_Change()
>> if TextBox1.Value = "" then exit sub
>>
>> If Range(TextBox1.Value).Columns.Count > 5 Or
>> Range(TextBox1.Value).Rows.Count > 5 Then
>>
>> MsgBox "Limit of 5 columns and rows"
>> TextBox1.Value = ""
>>
>> Else
>> Call AnotherMacro
>>
>> End If
>> End Sub
>>
>> LuisE wrote:
>>> I have a textbox that is getting a range address.
>>> If the total of columns or row is higher than 5 I want to show an MsgBox and
>>> clear its contents. It works fine but when the value becomes "" the change
>>> event is obviously triggered but in this case since tha value is nothing it
>>> still validates the number of columns/ros as higher than 5 and prompts the
>>> message again
>>>
>>> Private Sub TextBox1_Change()
>>>
>>> If Range(TextBox1.Value).Columns.Count > 5 Or
>>> Range(TextBox1.Value).Rows.Count > 5 Then
>>>
>>> MsgBox "Limit of 5 columns and rows"
>>> TextBox1.Value = ""
>>>
>>> Else
>>> Call AnotherMacro
>>>
>>> End If
>>> End Sub
>>>
>>> I tried the afterupdate event but it doesn't work.
>>>
>>> Thanks in advance.