Code to minimaze workbook and read checkbox value from worksheet

[jaryszek]

Hi,

cross posted here:
Code to find out value in checkbox when workbook is minimazed

MY code is:

Private Sub Workbook_WindowResize(ByVal Wn As Window)

    Dim CheckBox1 As CheckBox
    
    Set CheckBox1 = ActiveSheet.CheckBoxes("Check Box 1")
    Dim ValChcBox As String
    
    ValChcBox = CheckBox1.Value

    If Wn.WindowState = xlMaximized Then
    ElseIf Wn.WindowState = xlMinimized Then
            If ValChcBox = xlOn Then
                If Not UserForm1.Visible Then UserForm1.Show vbModeless
            End If
    
    Else ' normal
    End If

End Sub

is is possible to catch value of checbox before resizing workbook event?

Jacek

 

[Fluff]

You could create a public variable to store the state of the checkbox & whenever the checkbox is clicked update that value.

 

[jaryszek]

thank you!

Ooo so it would work after clicking and storing variable even if macro was finished?
How long public variable is kept in memory?

Jacek

 

[Fluff]

It will be kept in memory until the workbook is closed.

 

[jaryszek]

Thank you so much!

Jacek

 

[Fluff]

You're welcome

 

[jaryszek]

Hello,

i tried to put code like here in worksheet where i have Checkbox1:

Public IsEnabled As Boolean

Public Sub CheckBox1_Click()

If TimerWsh.CheckBox1.Value = True Then
    CheckBox1.Caption = "Disable Sound for Timer"
    IsEnabled = True
Else
    TimerWsh.CheckBox1.Caption = "Enable Sound for Timer"
    IsEnabled = False
End If

End Sub

And now in module (after clicking checkbox):

sub test 

debug.print isEnabled

end sub

but i have that variable is not find.
Why?
How to do this working?

Jacek

 

[Fluff]

You need to declare the variable in a standard module & not in the userform module.

 

[jaryszek]

thanks!

 

[Fluff]

You're welcome.