Userform checkbox submission

[kayakginge]

Hello,

I am brand new to VBA and would really appreciate any help anybody can offer with my userform.

I am using excel 2016.

I am trying to make a userform to record a daily log of crew on board a number of different boats.

I have date input via textbox
Boat selection is via three different checkboxes
crew selection is from 10 checkboxes
passenger input in via textbox
i have an ok button for submission and a close button to unload and close.

I would like it to do the following all on a sheet called Crew.

1.

Record the date from a textbox in column A in format dd/mm/yyyy
Record boat name in column B by selecting the boat with a checkbox
Record Crew names in any order over the next available columns and row from Column C to H again by checkbox selection (Maximum is 6 crew hence C to H)
Record any passengers in Column I from a textbox input.

2.

I would like the OK button to be used to submit the data and clear the entries but keep the userform open.

3.

I would like to make it so that the sheet is protected so that the only way of changing the data is through the userform or password.

4.

If possible i would like the sheet to be hidden unless you type in a password.

I realise that this is a lot to ask so the requests are numbered in order of importance. Any help would be appreciated and thank you in advance.

 

[My Aswer Is This]

So your restricting yourself to always having the same crew members

If check 1 is named Albert and checkbox2 is named Sam etc.

That's means you will never have any other names to use.

And your going to have a checkbox named for every possible "Boat"

And if you have all these checkboxes how will we know if checkbox named "Albert" if the name of a Boat or the name of a crew member

Normally it's better to use Textboxes or Listboxes.

 

[kayakginge]

I would like to use checkboxes for speed rather than have 7 different listboxes to select. (1 for boat and 6 possible for crew). The form is to be used by people not comfortable with computers let alone excel or VBA and checkboxes seemed the easiest to me?

The crew names will always be the same, a different checkbox for each person (10 total).
There are two checkboxes for boats, boat A and Boat B although this one could be just as quick by listbox i suppose as there are only two boat names.

I dont understand your last question, could you rephrase it so i can try to answer it?

Many thanks

 

[My Aswer Is This]

I'm was just providing some suggestions.
You said "I am brand new to VBA"

You could use one mutiselect listbox

This means you could select Bob and Mary and Carl etc.
And a second Mutiselect list box for the Boat names

This would mean you would not need 12 or so Checkboxes.

But hey I'm just here providing help.

Sounds like you have it all planned out and want to do it your way and that's great.

 

[kayakginge]

Thank you for the suggestions, i had not heard of the multiselect list box and will use that or another part of the model i intend to make.

If anybody knows how i can get the the checkbox selections into the different columns that would be a huge help.

Many thanks

 

[kayakginge]

Here is my code so far, it doesn't meet any of my criteria in terms of placing crew in any order from C - H but i dont know what to do.
It also says syntax error on the first line.

Private Sub CommandButton1_Click()
Dim lrD As Long,


lrD = Sheets("Crew").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Crew").Cells(lrD + 1, "A").Value = TextBox1.Text
Sheets("Crew").Cells(lrD + 1, "I").Value = TextBox2.Text
Sheets("Crew").Cells(lrD + 1, "B").Value = CheckBox1.Value = True
Sheets("Crew").Cells(lrD + 1, "C").Value = CheckBox2.Value = True
Sheets("Crew").Cells(lrD + 1, "C").Value = CheckBox3.Value = True
Sheets("Crew").Cells(lrD + 1, "D").Value = CheckBox4.Value = True
Sheets("Crew").Cells(lrD + 1, "E").Value = CheckBox5.Value = True
Sheets("Crew").Cells(lrD + 1, "F").Value = CheckBox6.Value = True
Sheets("Crew").Cells(lrD + 1, "G").Value = CheckBox7.Value = True
Sheets("Crew").Cells(lrD + 1, "H").Value = CheckBox8.Value = True
Sheets("Crew").Cells(lrD + 1, "C").Value = CheckBox9.Value = True
Sheets("Crew").Cells(lrD + 1, "D").Value = CheckBox10.Value = True
Sheets("Crew").Cells(lrD + 1, "E").Value = CheckBox11.Value = True
Sheets("Crew").Cells(lrD + 1, "F").Value = CheckBox12.Value = True




Dim mbResult As Integer
mbResult = MsgBox("Data Submitted")


Dim Ctrl As Control
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.ComboBox Then
Ctrl.Text = ""
End If
Next Ctrl
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "TextBox" Then
Ctrl.Text = ""
End If
Next Ctrl


End Sub

 

[My Aswer Is This]

The value of a Checkbox is either True or False
If you want the checkbox value like "Sam"
You would have to use Checkbox.Caption
And give each checkbox a caption like "Sam"

 

[My Aswer Is This]

To clear a ComboBox you could use:
ComboBox1.Clear

 

[kayakginge]

thank you for the reply.

i have finally cracked my userform.