| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event duplicating action
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Event duplicating action
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Event duplicating action
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Event duplicating action
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Event duplicating action
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Action Event, CheckBox, Add Row and Textbox | Excel Discussion (Misc queries) | |||
| Change to a Cell causes action | Excel Programming | |||
| Change in action when converting to *.xla | Excel Programming | |||
| Cell change causes action | Excel Programming | |||
| MsgBox in Enter event causes combobox not to run Change event | Excel Programming | |||
Linear Mode
