if statements in userform

Logistix

Board Regular
Joined
Aug 23, 2006
Messages
77
Hi,

I'm having a little problem with an if formula in my userforms. What I'm trying to do is, if a user leaves a combo box empty, prompt that there has been no selection made. But if a selection has been made for each of the 3 comboboxes, then the next button will allow the user to jump to the next form,

The code i have so far ...

Code:
Private Sub NextForm_Click()

    
    If EmployeeNameBox = "" Then

        MsgBox ("You have not selected an employee name")
        
    End If
    
    If DTPicker1.Value = "" Then
    
        MsgBox ("You have not selected an date")
        
    End If
    
    If HoursDepositBox = "" Then
    
        MsgBox ("You have not selected any hours")
    
    End If
    
    
        EnterHours.Hide
        ConfirmDataForm.Show
    
    

End Sub

It's pretty obvious from the above code that it will simply move onto the next form regardless if the combox is empty or not. Can anyone see a solution here??

Thanks in advance
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

how about something like (untested)

Code:
Private Sub NextForm_Click() 
    dim moveforward as boolean
    do
    moveforward = true
    If EmployeeNameBox = "" Then 

        MsgBox ("You have not selected an employee name") 
        moveforward = false
        
    End If 
    
    If DTPicker1.Value = "" Then 
    
        MsgBox ("You have not selected an date") 
        moveforward = false
        
    End If 
    
    If HoursDepositBox = "" Then 
    
        MsgBox ("You have not selected any hours") 
        moveforward = false
    
    End If 
    loop until moveforward
    
        EnterHours.Hide 
        ConfirmDataForm.Show 
    
    

End Sub


Tony
 

Logistix

Board Regular
Joined
Aug 23, 2006
Messages
77
Thanks for the code. Except it doesn't work, it just loops over and over. Any onther ideas? Thanks
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

OK, try

Code:
Private Sub NextForm_Click()
  Dim MoveForward As Boolean
  Dim Boxx As String
  MoveForward = True
  If EmployeeNameBox = "" Then
    MsgBox ("You have not selected an employee name")
    MoveForward = False
    Boxx = "EmployeeNameBox"
  ElseIf DTPicker1.Value = "" Then
    MsgBox ("You have not selected an date")
    MoveForward = False
    Boxx = "DTPicker1"
  ElseIf HoursDepositBox = "" Then
    MsgBox ("You have not selected any hours")
    MoveForward = False
    Boxx = "HoursDepositBox"
  End If
    
  If MoveForward Then
    EnterHours.Hide
    confirmdataform.Show
  Else
    Controls(Boxx).SetFocus
  End If
End Sub


Tony
 

Logistix

Board Regular
Joined
Aug 23, 2006
Messages
77

ADVERTISEMENT

That awesome, thanks Tony. Only I'm not getting prompted to input a date if I havn't. I figure it's because I have a default value already in the DTPicker (which is unchecked). How would I express "if dtpicker1 not checked"??

Thanks,
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

What exactly is DTPicker? I thought it was a combobox?


Tony
 

Logistix

Board Regular
Joined
Aug 23, 2006
Messages
77
Dtpicker is a control that I added named "Microsoft Date And Time Picker 6.0(SP4). It allows me to select a date from a calander using the mouse.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Don't have access to that control. Can only get the calendar.


Tony
 

Forum statistics

Threads
1,136,647
Messages
5,676,983
Members
419,667
Latest member
MegEri

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