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's Profile: http://www.excelforum.com/member.php...o&userid=18259
View this thread: http://www.excelforum.com/showthread...hreadid=399042

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"

wrote in message

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

Thanks a lot Jim, the code seems OK but I'm getting a syntax error fo
some reason I've copy-pasted the whole code below, any idea what I'
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") =
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 Su

--
anar_bak
-----------------------------------------------------------------------
anar_baku's Profile: http://www.excelforum.com/member.php...fo&userid=1825
View this thread: http://www.excelforum.com/showthread.php?threadid=39904

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"

wrote in message

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