I know this is a simple question to answer but I am just not that familiar with VBA yet.
I have a worksheet with 100's of checkboxes. The worksheet also contains an "Exit" button. When the "Exit" button is selected, I want to run a macro that looks for any checkboxes that may be checked. If a checkbox is checked, I want to supply a message that reads "You have a Checked Box".
Can anyone help?
Let's begin with: What type of checkboxes are you using? Is it a Form Control, or an ActiveX Object?
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
The checkboxes are form control.
Here is the macro I tried.
Sub CHECKBOXEXIT()
Please Login or Register to view this content.
Sheets("UNVERIFIED").Select
If (CheckBox = True) Then
MsgBox "EITHER TRANSFER PARTS OR UNCHECK BOX"
End If
End SubPlease Login or Register to view this content.
It's a good start. Always be sure to wrap your code with [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] per the Forum Rules. Makes things easy to read.
Please Login or Register to view this content.
Thanks much. I did wrap them in Code but it didn't take for some reason. Sorry about that.
So I tried the macro. It worked well but I realized that I not only need to send the message, but I also want to stop the macro from running further. So when the user hits the "OK" message the macro stops running.
In my code I put a stop immediately after it finds one.
Please Login or Register to view this content.
Perhaps this helps. I'm calling this macro from another macro called SAVEEXIT(). When CHECKBOXEXIT COMPLETES, SAVEEXIT continues to run.
Please Login or Register to view this content.
Ahh, then you need to declare a Private or Public variable at your general declarations at the top of the module, set it when a checkbox is found, and then check that reference after the called Sub ends.
Please Login or Register to view this content.
Thanks for all your help and patience.
That still didn't stop it. As soon as I hit the OK button on the messagebox, the SAVEEXIT macro ran.
--
Edit: So many double posts today.
Last edited by daffodil11; 03-12-2015 at 04:04 PM.
Are both subs in the same module? Is the Private or Public variable at the top?
I've attached a sample. View how the code looks. Try to run the macro button.
I put a second messagebox after the Call. It does not appear, because the code is aborted.
I separated them into different modules. SAVEEXIT is in Module 2 all by itself and CHECKBOXEXIT is in MODULE 1 with other subs. Here is the top of Module 2
Please Login or Register to view this content.
Allow me to explain a little more on the birds and the bees of variables.
When you dimension a variable within a procedure (read: Sub), it exists only while that procedure is running.
If you dimension a variable at the top of a module with Dim or Private, it exists and can only be referenced from anything in that module.
If you wish for a variable to be recognized from ANY module, then it must be made Public.
Please Login or Register to view this content.
You are probably getting pretty perturbed with me by now....please be patient, but I have set up the page exactly as you set up in the check thingies spreadsheet. I even just changed it from Private to Public. I get the same results...as soon as I hit the OK button, SAVEEXIT runs.
Are you calling SAVEEXIT from some other source?
Does it run automatically via a call in ThisWorkbook? Are you able to attach your workbook, minus ALL of the data? Just leave one or two checkboxes.
Look in Module 2
UNVERIFIED.xlsm
Seems to be working as planned. Can you compare this to what you had used?
Here's the problem. You'll notice that the SAVEEXIT macro saves and closes the workbook. I don't want that to happen if a check box is checked. I'd like the macro instead to stop once the user hits the OK button on the message box. I want to force them to either uncheck the box or transfer the information which is another botton at the top of the page. The user would then have to select the Exit button again once he has taken one or the other action
When you hit the Exit button in the attachment, it does not show the second message box. The SaveExit procedure is aborted.
Please Login or Register to view this content.
OK...so I took the macro you provided, created a new module, put the code from the original SAVEEXIT macro and it worked fine. I don't understand why that is but it worked in a new module.
Thanks so much for your patience and help.
Randy
No problem. We're all students here, just working with different levels of proficiency.
Glad we were able to work it all out.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks