Use multiple combo boxes to select cell

Question

I'm trying to make a simple user form in Excel to enter the date that a staff member did a certain task. I have a spreadsheet that lists all of my staff down column B and I have a list of all the tasks along row 5.

I've created a form with a couple of combo boxes so I can select the staff member and also select the task. There is also a text box which I will type a date into.

What I need is when I click the "submit" button it will enter the date contained in the text box into the cell defined by the 2 selections in the combo boxes. If either of the combo boxes have nothing selected then no data should be entered into the spreadsheet when the button is clicked.

Dim nameRow As Integer 
Dim job1col As Integer 
nameRow = cbxStaffName.ListIndex + 6 
job1col = cbxJob1.ListIndex + 3 
ThisWorkbook.Sheets("Job Rotation").Cells(nameRow, job1col).Value = tbxDate.Value

The combo box containing the list of staff names is called cbxStaffName. The combobox containg the jobs is cbxJob1. The textbox containing the date I want to enter is named tbxDate.

The combo boxes have been populated with data that exists on the spreadsheet, simply names down the left and jobs along the top. If for example Jim did the job welding I want to select Jim from one combo box, select welding from the other box and when I click the button the date will go into that cell on the spreadsheet.

Please share what you have so far, what you want it to look like, and which part you're stuck on. — ashleedawg, Jun 05 '18 at 05:56
@ashleedawg Dim nameRow As Integer Dim job1col As Integer nameRow = cbxStaffName.ListIndex + 6 job1col = cbxJob1.ListIndex + 3 ThisWorkbook.Sheets("Job Rotation").Cells(nameRow, job1col).Value = tbxdate.Value This is the sub I have made to run when the submit button is pressed. When it get to assigning the listindex to the variables I get an object required error. Am I going about this the wrong way? — Matt, Jun 05 '18 at 06:04
Code goes in the question - you can edit to include it there. — Tim Williams, Jun 05 '18 at 06:07
Welcome to [so]! Normally you woul [edit] your post when adding code or any other relevant information. (Comments are subject to deletion, and are just harder to read) I added it for you. We're definitely going to need more information. What are the combo boxes called? What values do they contain? etc — ashleedawg, Jun 05 '18 at 06:09

Pᴇʜ · Accepted Answer · 2018-06-05T06:39:43.530

0

You would just need an If statement to check if both checkboxes have a value:

If cbxStaffName.Value <> vbNullString And cbxJob1.Value <> vbNullString Then
    'write into cell
Else
    MsgBox "Please select staff and job first"
End If

Alternatively you can check the ListIndex which should be -1 if nothing was selected.

Note that I recommend to always use Long instead of Integer especially when dealing with row counts. Excel has more rows than Integer can handle.

edited Jun 05 '18 at 06:39

answered Jun 05 '18 at 06:12

Pᴇʜ

56,719
10
49
73

thanks @peh, I've changed them to Long but I'm still getting an object required error when it gets to the line where I set the variables. Have I missed something? – Matt Jun 05 '18 at 06:30
Are your comboboxes named correctly? In which line exactly do you get the error? – Pᴇʜ Jun 05 '18 at 06:33
The names are definitely correct, error comes up when it gets to nameRow = cbxStaffName.ListIndex + 6 – Matt Jun 05 '18 at 06:34
If that code is in a module you will need to specify the userform: `YourUserFormName.cbxStaffName.ListIndex`. The error means that your combobox was not found. – Pᴇʜ Jun 05 '18 at 06:36

Use multiple combo boxes to select cell

1 Answers1