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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

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>
 

Watch MrExcel Video

Forum statistics

Threads
1,114,052
Messages
5,545,730
Members
410,702
Latest member
clizama18
Top