Setting controls on a user form as compulsory?

nicolavision

New Member
Joined
Oct 21, 2004
Messages
31
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]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Oct 21, 2004
Messages
31
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:

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

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Oct 21, 2004
Messages
31
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
 

Forum statistics

Threads
1,147,848
Messages
5,743,525
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top