Command Button Prompt

[adambush4242]

I have a command button on one of my worksheets and I want to add a
conditional function to it. There are over a hundred cells in my worksheet
that if they all equal zero and you push the button a dialogue box would
appear with the message "Are you sure you want to proceed?" It would have
two options, OK, or cancel, in which the attached macro would only run if the
OK button was pushed. Is this possible? Any help would be greatly
appreciated.

Thanks

Adam Bush

 

[Bob Phillips]

Sub myMacro
If Application.CountIf(Range("A1:A100",0)) =Range("A1:A100").Cells.Count
Then

If MsgBox("Are you sure that you want to proceed?",vbOKCancel)
=vbOK Then

Call OtherMacros
End If
End If
End SUb

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"(e-mail address removed)"

 

[adambush4242]

Bob,

Thanks for the prompt reply. I forgot to mention that the cells in question
that must equal zero are all over the place, not in a certain range. Is
there any way to take this into consideration?

Thanks

Adam Bush

 

[adambush4242]

Bob,

I linked all the relevant cells to a range, however, when I type the
following code:


Sub sendwarning()
If Application.CountIf(Range("CJ1:CJ143", 0)) =
Range("CJ1:CJ143").Cells.Count Then

If MsgBox("No Pivot Detected. Proceed Anyways?", vbOKCancel) = vbOK
Then

Call OtherMacros
End If

End If

End Sub

an error pops up saying "Sub or function not defined" which is referring to
the Call OtherMacros section. Any ideas? Also, I want the button to run my
other macro if the OK button is pushed OR if any of the cells in my range do
not equal zero. How can I modify to incorporate this?

Thanks

Adam Bush

 

[Conan Kelly]

Bob,

Please forgive me if I'm stepping on your toes. Please feel free to correct
me if I get this wrong.

Adam,

In the line "Call OtherMacros", it is trying to run another macro named
"OtherMacros". It is not likely that you have another macro nameed
"OtherMacros". Replace "OtherMacros" with the name of your other macro that
you want to run.

Also, in order to get this to run, adjust the code like this:

Sub sendwarning()
If Application.CountIf(Range("CJ1:CJ143", 0)) =
Range("CJ1:CJ143").Cells.Count Then
If MsgBox("No Pivot Detected. Proceed Anyways?", vbOKCancel) = vbOK
Then
Call OtherMacros
Else
Msgbox "Canceled by user", vbOKOnly + vbInformation, "Canceled"
Exit Sub
End If
Else
Call OtherMacros
End If
End Sub

Once again, remember to change "OtherMacros" in "Call OtherMacros" to the
name of your macro that you want to run.

HTH,

Conan


"(e-mail address removed)"

 

[adambush4242]

Conan,

I tried the code you provided but when I replaced OtherMacros with the name
of my macro, I got an error. In VB, the first line was highighted in yellow
and the messade said "Method 'Range' of object'_Global' failed". Any
suggestions?

Thanks

Adam Bush

 

[Conan Kelly]

Adam,

Please clear something up for me. In an earlier post, you said "I linked
all the relevant cells to a range, however, when I type the following
code:". What do you mean by "linked all the relevant cells to a range". Do
you mean you gave it a name (Named Range). Also notice, this code is using
the range CJ1:CJ143. Have you adjusted this code to use your range?

Try the corrections below to see if it will keep you from getting that error
message.

Change the line that reads:

If Application.CountIf(Range("CJ1:CJ143", 0)) =
Range("CJ1:CJ143").Cells.Count Then

To:

If Application.WorksheetFunction.CountIf(Range("CJ1:CJ143"), 0) =
Range("CJ1:CJ143").Cells.Count Then

See if that works.

Post back and let me know what happens.

HTH,

Conan






"(e-mail address removed)"

 

[adambush4242]

Conan,

I made the change and now I get a compile error: Argument not optional.
The .countif is highlighted. Just to clarify, with the words othermacros in
the code, I got an error because the macro couldn't find the function to
call. When I replaced the words othermacros with my real macro, I got the
"Method 'Range' of object'_Global' failed". error. Also, when I was talking
about the range earlier I was just saying that the cells that I want to equal
0 are scattered throughout the page so I just linked them to be in order so I
could select a concurrent range.

Thanks

Adam Bush

 

[Conan Kelly]

Adam,

Look a little bit closer at my previous post. There were 2 corrections in
the line of text.

The first one was adding ".WorksheetFunction".
The second one was changing the location of one of the closing parentheses:
changed "CountIf(Range("CJ1:CJ143", 0))" to "CountIf(Range("CJ1:CJ143"),
0)".

That location of the closing paren. is what is causing that "Argument not
optional" error.

Also, there are a couple of ways to design this code if you need the cells
to be scattered throughout. But if you can get away with putting them in a
concurrent range, we will just continue as we are.

Let me know if this works out,

Conan




"(e-mail address removed)"

 

[adambush4242]

Conan,

I got it to work! Thank you for your help. I think the problem was a
missing parentheses.

Thanks Again,

Adam Bush

 

[Conan Kelly]

Adam,

No problem.......any time.

Also, check out my reply to your last post.

Conan



"(e-mail address removed)"