Setting controls on a user form as compulsory?

[nicolavision]

Hi

I have a user form that I want to set some of the controls to be compulsory and a message to appear that tells the user and stops them from closing/OK the form.

The form opens in two ways depending on what selection before you make so some controls are hidden and if these are hidden then I don't want them to be compulsory but if they are displayed then they should be.

Thanks for the help
[/code]

 

[Smitty]

Something along these lines maybe:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    <SPAN style="color:#007F00">'   Update form</SPAN>
    <SPAN style="color:#00007F">If</SPAN> TextBox1.Value = "" And TextBox1.Visible = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "XXX must be filled in", vbInformation + vbOKOnly, "Data required"
        TextBox1.SetFocus
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[nicolavision]

Hi

Thanks for that - it works on the first control but when I try and add another one to check it doesn't work? I think I've got my If's and Elses wrong!

Here's the code:

Private Sub cmdOK_Click()
On Error GoTo Handler

If cboEmployeeNo.Value = "" And cboEmployeeNo.Visible = True Then
MsgBox "Please enter Employee Number", vbInformation, "Data Required"
cboEmployeeNo.SetFocus

Else

   
    ActiveWorkbook.Sheets("User Information").Activate
    Range("A1:B15").Select
    Selection.Copy
    Workbooks.Add
    Application.DisplayAlerts = False
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.DisplayZeros = False
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Application.CutCopyMode = False
    ChDir "C:\"
    ActiveWorkbook.SaveAs Filename:="C:\UserInformation.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False

   

Handler:

Application.DisplayAlerts = False
ActiveWorkbook.Close
Unload Me
Application.DisplayAlerts = False
Sheet1.Visible = xlSheetHidden
ActiveWorkbook.Sheets("Standard Expense Claim").Activate
Range("A5").Select

End If

End Sub

Thanks for your help

Nicola

 

[Smitty]

Not tested, but you had your End If after the Handler statement:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cmdOK_Click()
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Handler
    
        <SPAN style="color:#00007F">With</SPAN> Application
            .DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
            .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
            
            <SPAN style="color:#00007F">If</SPAN> cboEmployeeNo.Value = "" And cboEmployeeNo.Visible = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
                MsgBox "Please enter Employee Number", vbInformation, "Data Required"
                cboEmployeeNo.SetFocus
            <SPAN style="color:#00007F">Else</SPAN>
                ActiveWorkbook.Sheets("User Information").Activate
                Range("A1:B15").Copy
                Workbooks.Add
                Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                    <SPAN style="color:#00007F">False</SPAN>, Transpose:=<SPAN style="color:#00007F">False</SPAN>
                ActiveWindow.DisplayZeros = <SPAN style="color:#00007F">False</SPAN>
                Columns("A:A, B:B").EntireColumn.AutoFit
                
                ActiveWorkbook.SaveAs Filename:="C:\UserInformation.xls", FileFormat:= _
                    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
                    , CreateBackup:=<SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            
            .DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
            .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
            .CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        
Handler:
        
        Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
        ActiveWorkbook.Close
        Unload Me
        Sheet1.Visible = xlSheetHidden
        ActiveWorkbook.Sheets("Standard Expense Claim").Activate
        Range("A5").Select
        Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Smitty

 

[nicolavision]

Hi

I've tried that and the code works but I'm struggling with adding another statement after the txtEmployeeNumber.visble.

I would like to know how I can add another control to be checked after the first control and then run the else function

Thanks for all your help

N