Command button - check listbox contents before activating?

[Roger on Excel]

[Excel 2003]

I have command buttons on the userform which access other userforms (which
drive the spreadsheet data entry)

I have a listbox on the userform displaying a number.

I would like to enter code into the command button activations such that if
the number in the listbox does not equal the number stored within the code
of the command button then a mesage box is displayed and the command button
does not activate its sub routines.

Can anyone help?

Thanks,

Roger

 

[AB]

Something like this:

Option Explicit

Public Property Get MayProceed() As Boolean

Const MySecretNumber As Long = 7

With Me.ListBox1
If Not .Value = MySecretNumber Or .ListIndex = -1 Then
MayProceed = False
Else
MayProceed = True
End If
End With

End Property


Private Sub CommandButton1_Click()

If Not MayProceed Then
MsgBox "Uups - numbers don't match!!", vbExclamation
Exit Sub
End If

End Sub

 

[Roger on Excel]

Hi,

Thanks for the code. Would you know how to make it work if the listbox
contents are a text string?

Roger

Something like this:

Option Explicit

Public Property Get MayProceed() As Boolean

Const MySecretNumber As Long = 7

With Me.ListBox1
If Not .Value = MySecretNumber Or .ListIndex = -1 Then
MayProceed = False
Else
MayProceed = True
End If
End With

End Property


Private Sub CommandButton1_Click()

If Not MayProceed Then
MsgBox "Uups - numbers don't match!!", vbExclamation
Exit Sub
End If

End Sub

[Excel 2003]

I have command buttons on the userform which access other userforms (which
drive the spreadsheet data entry)

I have a listbox on the userform displaying a number.

I would like to enter code into the command button activations such that if
the number in the listbox does not equal the number stored within the code
of the command button then a mesage box is displayed and the command button
does not activate its sub routines.

Can anyone help?

Thanks,

Roger

.

 

[AB]

Hi,

if the only difference is comparing strings instead of comparing
numbers then technically the only change in the code necessary would
be to dim the constant with a different type (string instead of Long)
like this:

replace this:
Const MySecretNumber As Long = 7

with this
Const MySecretNumber As String = "7"

Obviously the "7" can be anything you need (like "Seven" or "Apple"
or ...)

Post back if i misunderstood the question or didn't answer it.

Hi,

Thanks for the code.  Would you know how to make it work if the listbox
contents are a text string?

Roger

Something like this:

Option Explicit

Public Property Get MayProceed() As Boolean

    Const MySecretNumber As Long = 7

    With Me.ListBox1
        If Not .Value = MySecretNumber Or .ListIndex = -1 Then
            MayProceed = False
        Else
            MayProceed = True
        End If
    End With

End Property

Private Sub CommandButton1_Click()

    If Not MayProceed Then
        MsgBox "Uups - numbers don't match!!", vbExclamation
        Exit Sub
    End If

End Sub

[Excel 2003]
I have command buttons on the userform which access other userforms (which
drive the spreadsheet data entry)
I have a listbox on the userform displaying a number.
I would like to enter code into the command button activations such that if
the number in the listbox does not equal the  number stored within the code
of the command button then a mesage box is displayed and the command button
does not activate its sub routines.
Can anyone help?
Thanks,
Roger

.- Hide quoted text -

- Show quoted text -