Public Variables? Static Variables?

[mqarep]

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.

 

[acw]

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]

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

 

[Legacy 98055]

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="filterrogid: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>

 

[mqarep]

Thank you very much for your assistance.