CODEWITHSUNDEEP
excelvba
Gexas
Gexas

Reputation: 678

Stop UserForm_Initialize from triggering ToggleButton_Click event

I have one sheet ("Settings"), which needs to be hidden most of the time. I have created Settings UserForm which contains various settings buttons and toggle button in there hides/show that hidden sheet (then clicked and password is entered).

Macro for hiding/showing sheet: 

Private Sub SettingsTB_Click()

Dim strPassTry As String
Dim strPassword As String
Dim lTries As Long
Dim bSuccess As Boolean

If SettingsTB.Value = True Then
    strPassword = "asd"
    For lTries = 1 To 3
        strPassTry = InputBox("Enter password", "Show Settings sheet")
        If strPassTry = vbNullString Then Exit Sub
        bSuccess = strPassword = strPassTry
        If bSuccess = True Then Exit For
        MsgBox "Incorrect password"
    Next lTries

    If bSuccess = True Then
        Worksheets("Settings").Visible = True
    End If
Else
    Worksheets("Settings").Visible = xlSheetHidden
End If

End Sub

That macro works as intended, problem arises then I open UserForm and "Settings" Sheet is left visible. UserForm_Initialize event triggers SettingsTB_Click event (ask for entering password).

Code in UserForm_initialize used for remembering toggle button position (without it, every time, UserForm is opened, toggle button in FALSE possition):

Private Sub UserForm_Initialize()

If Worksheets("Settings").Visible = True Then
    SettingsTB.Value = True
Else
    SettingsTB.Value = False
    End If

End Sub

Is it possible to stop SettingsTB_Click from triggering on UserForm_Initialize or should I use completely different approach?

Upvotes: 1

Views: 261

Answers (1)

EvR
EvR

Reputation: 3498

Use a public variable or the tag-property to stop the click-event from running.

Private Sub SettingsTB_Click()


Dim strPassTry As String
Dim strPassword As String
Dim lTries As Long
Dim bSuccess As Boolean

If SettingsTB.Tag Then Exit Sub

If SettingsTB.Value = True Then
    strPassword = "asd"
    For lTries = 1 To 3
        strPassTry = InputBox("Enter password", "Show Settings sheet")
        If strPassTry = vbNullString Then Exit Sub
        bSuccess = strPassword = strPassTry
        If bSuccess = True Then Exit For
        MsgBox "Incorrect password"
    Next lTries

    If bSuccess = True Then
        Worksheets("Settings").Visible = True
    End If
Else
    Worksheets("Settings").Visible = xlSheetHidden
End If

End Sub
Private Sub UserForm_Initialize()
SettingsTB.Tag = True
If Worksheets("Settings").Visible = True Then

    SettingsTB.Value = True
Else
    SettingsTB.Value = False
    End If
SettingsTB.Tag = False
End Sub

Upvotes: 1

Related Questions