Public Variables? Static Variables?

mqarep

Board Regular
Joined
Mar 12, 2002
Messages
91
I have vba code (Sub CheckBox1_Click() ) that looks for a checked checkbox. When the checkbox is checked my variable =1 else the variable is 0.

When the workbook is clicked to close by the user (Sub Workbook_BeforeClose) and the checkbox was checked, a message pops up to inform the user of an additional worksheet that needs to be completed.

The problem is that I cannot get the value of the variable to pass from the "checkbox click" module to the "workbook close" module.
See code below...
-------------------------------------------------------------------------------------

Public multiplecostcenters As Long
Sub CheckBox1_Click()

If CheckBox1.Value = True Then
multiplecostcenters = 1
End If
If CheckBox1.Value = False Then
multiplecostcenters = 0
End If
End Sub
-----------------------------------------------------------------------------------
Sub Workbook_BeforeClose(Cancel As Boolean)

If multiplecostcenters = 1 Then
MsgBox ("Please complete the Profit Center information required on worksheet tab: Profit Centers")
End If
----------------------------------------------------------------------------------

Thanks for your expertise.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Try making it a public variable in a general module rather than the sheet module. I think that will make it available to both the sheet and workbook modules.


Tony
 

mqarep

Board Regular
Joined
Mar 12, 2002
Messages
91
It actually already is, under General - Declarations (followed by CheckBox1 - Click):

Public multiplecostcenters As Long
_____________________________

Sub CheckBox1_Click()
Static multiplecostcenters As Long
If CheckBox1.Value = True Then
multiplecostcenters = 1
MsgBox ("checkbox is has been checked and variable = 1")
End If
If CheckBox1.Value = False Then
MsgBox ("checkbox is has NOT been checked and variable = 0")
multiplecostcenters = 0
End If
End Sub
 
L

Legacy 98055

Guest
Controls are public members of the object that contains them. There is really no need to take the extra step to store your checkbox's value in a separate variable. With this in mind, the only code necessary is located below. Located in your workbook close procedure. Make sure that "Profit Centers" is the actual name of the worksheet...

<table width="100%" border="1" bgcolor="White" style="filter:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">If</font> Sheets("Profit Centers").CheckBox1.Value = <font color="#0000A0">True</font> <font color="#0000A0">Then</font>
       MsgBox ("Please complete the Profit Center information required on worksheet tab: Profit Centers")
  <font color="#0000A0">End</font> <font color="#0000A0">If</font>
</FONT></td></tr></table><button onclick='document.all("10182006194620386").value=document.all("10182006194620386").value.replace(/<br \/>\s\s/g,"");document.all("10182006194620386").value=document.all("10182006194620386").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("10182006194620386").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="10182006194620386" wrap="virtual">
If Sheets("Profit Centers").CheckBox1.Value = True Then
MsgBox ("Please complete the Profit Center information required on worksheet tab: Profit Centers")
End If</textarea>
 

Forum statistics

Threads
1,140,913
Messages
5,703,131
Members
421,277
Latest member
abudgen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top