Error Alert

[anar_baku]

Hi guys,

This should be straighforward, but I can't figure out. Please help:

I have created a text box that when clicked on runs a macro. So currently:

1. User clicks on the TextBox
2. Specified Macro is run

and I want:

1. User clicks on the TextBox
2. Excel checks if at least one of the 3 cells (A1, A2, A3) has a value in it
if Yes then it runs the Macro as planned
if Not then it brings up an error message asking the user to enter a value in any of the cells (A1, A2 or A3).

Thanks for your help

[Jim Cone]

a,
Add the check for cell content to the beginning of your macro...

If Len(Range("A1") + Len(Range("A2") + Len(Range("A3") = 0 Then
Msgbox "An entry is required on the worksheet. "
Exit Sub
End If

Jim Cone
San Francisco, USA


"anar_baku"
<[email protected]>
wrote in message
news:[email protected]
Hi guys,
This should be straighforward, but I can't figure out. Please help:
I have created a text box that when clicked on runs a macro.
So currently:
1. User clicks on the TextBox
2. Specified Macro is run
and I want:
1. User clicks on the TextBox
2. Excel checks if at least one of the 3 cells (A1, A2, A3) has a value
in it
if Yes then it runs the Macro as planned
if Not then it brings up an error message asking the user to enter a
value in any of the cells (A1, A2 or A3).
Thanks for your help
--
anar_baku

[anar_baku]

Thanks a lot Jim, the code seems OK but I'm getting a syntax error for some reason I've copy-pasted the whole code below, any idea what I'm doing wrong?
Text in Blue - my original Macro
Text in Red - the code you suggested I add

Sub Macro5()

If Len(Range("A1") + Len(Range("A2") + Len(Range("A3") = 0 Then
MsgBox "An entry is required on the worksheet. "
Exit Sub
End If
Range("List").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range( _
"Criteria"), Unique:=False
ActiveSheet.Shapes("Button").Select
Selection.Characters.Text = "Show All"
ActiveSheet.Shapes("Button").OnAction = "Macro6"
Range("A7").Select

End Sub


Sub Macro6()

ActiveSheet.ShowAllData
ActiveSheet.Shapes("Button").Select
Selection.Characters.Text = "Search"
ActiveSheet.Shapes("Button").OnAction = "Macro5"
Range("A7").Select

End Sub

[Jim Cone]

a,

The line...
If Len(Range("A1") + Len(Range("A2") + Len(Range("A3") = 0
should read...
If Len(Range("A1")) + Len(Range("A2")) + Len(Range("A3")) = 0 Then

I omitted the required ")" in my original post.
Note: Colored text does not always show up, best to not use it.

Regards,
Jim Cone
San Francisco, USA


"anar_baku"
<[email protected]>
wrote in message
news:[email protected]
Thanks a lot Jim, the code seems OK but I'm getting a syntax error for
some reason I've copy-pasted the whole code below, any idea what I'm
doing wrong?
Text in Blue - my original Macro
Text in Red - the code you suggested I add

Sub Macro5()
If Len(Range("A1") + Len(Range("A2") + Len(Range("A3") = 0
Then
MsgBox "An entry is required on the worksheet. "
Exit Sub
End If
Range("List").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range( _
"Criteria"), Unique:=False
ActiveSheet.Shapes("Button").Select
Selection.Characters.Text = "Show All"
ActiveSheet.Shapes("Button").OnAction = "Macro6"
Range("A7").Select
End Sub


Sub Macro6()
ActiveSheet.ShowAllData
ActiveSheet.Shapes("Button").Select
Selection.Characters.Text = "Search"
ActiveSheet.Shapes("Button").OnAction = "Macro5"
Range("A7").Select
End Sub