UserForm

i have created two user forms....

once the data has been submitted from both forms onto the worksheet... how do i close UserForm2 and display UserForm1..... also making sure the the fields are all clear...

ive attached the UserForm... for clarification

Hi

In your commandbutton2_click code, you could add loops to clear out the comboboxes before unloading the form. Something like

For I = 1 To 20
    UserForm1.Controls("ComboBox" & I).Value = ""
Next I

Do the same for Textbox1.

Is this what you are chasing? You seem to be able to shut down form 2, and have form 1 show again.

rylo

cheers bud... thats perfect....

lastly how do i validate that every combo box has a selection before submission of the data into the worksheet.

For example dont allow the form to be submitted and a message box should appear prompting the user to complete all combo boxes.....

Hi

Try something like this.

Private Sub CommandButton2_Click():

Dim LastRow As Range
Dim strVal As String
  For i = 1 To 20
    If UserForm1.Controls("combobox" & i).Value = "" Then
      MsgBox "Please fill in all options"
      Exit Sub
    End If
  Next i

I've just put in the start of the module so you can see its position.

rylo

thats perfect mate...

you a star... ive used it so the combo box is filled and the textbox field....

for each combo box there are 3 values to select 'Yes', 'No' and 'N/A'

when i press Y on the keyboard, it changes the combo box to Yes, when the N button is pushed its changes it to No, how can i assign a different character on the keyboard so 'N/A' can be selected in the combo box... like the letter 'A'

thanks

Hi

Only thing I can think of is to change "N/A" to " N/A". You can then use a space bar to select.

When the data is being output, you could remove the leading space (TRIM function) or replace when the output is completed.

rylo