I've gotten very good with comboboxes in UserForms, but this is the first time I've tried putting one directly onto a worksheet.
I have a worksheet for entering using the user's personal info. There are four cells for the user to enter, and then two cmdButtons to launch macros that use this information.
I need to make the department field a combobox, so that the tracking database has consistent departments. The combobox pulls its list from a hidden worksheet, but eventually will pull from an Access table.
The problem is, how do I pass the combobox.value to the code?
I tired useing ComboBox1.value =, but it gave me an "Object Required" error. Is there a way to make this value public? or is it trapped on the sheet from which it came? I tried using worksheets("User Data").combobox1.value, but this errored too. Maybe I need to assign a public variable in the worksheet code?
My current work around stinks: I've got a cell under the box which is linked to box, and then I pull my data from the cell. Works great from a VBA perspective, but I had do a bunch of voodoo to keep the user from directly selecting or editing the cell.
I have a worksheet for entering using the user's personal info. There are four cells for the user to enter, and then two cmdButtons to launch macros that use this information.
I need to make the department field a combobox, so that the tracking database has consistent departments. The combobox pulls its list from a hidden worksheet, but eventually will pull from an Access table.
The problem is, how do I pass the combobox.value to the code?
I tired useing ComboBox1.value =, but it gave me an "Object Required" error. Is there a way to make this value public? or is it trapped on the sheet from which it came? I tried using worksheets("User Data").combobox1.value, but this errored too. Maybe I need to assign a public variable in the worksheet code?
My current work around stinks: I've got a cell under the box which is linked to box, and then I pull my data from the cell. Works great from a VBA perspective, but I had do a bunch of voodoo to keep the user from directly selecting or editing the cell.
