Excel VBA Fill Textbox on Userform from worksheet depending on another textbox input

Question

So i have a Userform,

On this UserForm I have a ComboBox (comboDepartment), a TextBox (txtCode), and another TextBox (textDescription). Each ComboBox selection has a sheet. (e.g. Robo1, Robo2, etc...). The worksheets have 2 columns ( Column A = is the Error Code and Column B = is the Error Description)

Now my question, Depending on (comboDepartment) comboBox selection have it select the proper worksheet (Maybe set it as a varible?).

Next user would input an Error Code into (txtCode) TextBox (Just Numbers e.g. 1,2,3, etc...), after inputing the code I need it to fill in the (textDescription) TextBox from column B from the worksheet depending on the error code the user inputed.

Was wondering if this is at all possible?

Thank you in advance!

Answer 1

Collect all sheets by getting their names and add them on your combobox. On your Private sub Userform_Initialize method:

For each deptSheet in Thisworkbook.Worksheets
    comboDepartment.AddItem deptSheet.Name
Next

Create a private variable for your worksheet (dim deptSheet as Worksheet) and set the selected value on the combobox. On your Private sub comboDepartment_Change method:

set deptSheet = Thisworkbook.Sheets(comboDepartment.Value)

You may now reference the sheet on the selected dropdown by: deptSheet.Range("...