Combobox not loading the items if the source sheet is not activated

[ImranBhatti]

Hi

In the attached workbook, I have a userform
the combobox ComboMainHead is being populated from the chart of accounts sheet. the problem is if the said sheet is not activated the combox is not populated.

What am I am doing wrong

Here is the code behind the userform

Please Login or Register  to view this content.

[sktneer]

Replace existing UserForm_Initialize code with the following one...

Please Login or Register  to view this content.

[ImranBhatti]

Guru G

The problem is if I call the userform from the Database then the said combobox is not getting populated even with the amended code. This is what I just came to know. It should be populated if I call the userform from Database.

[sktneer]

After making the changes, if I click the button called "Show Voucher Entry Form" on DataBase sheet, the ComboBox gets populated as desired.

Please refer to the demo video.

[Fluff13]

Whilst the code supplied by sktneer worked for me, how about

Please Login or Register  to view this content.

[bakerman2]

Works fine for me.

Please Login or Register  to view this content.

[ImranBhatti]

All you Gurus are correct at your place. If by means of the amended codes the ComboMainHead is populated then the second depended combobox is not populated.Please select the second item from the Mian Head and then click on sub Head combo.

[sktneer]

The issue with both of your codes is because of the way you are trying to find the last used row in column A. Look at the bold part in the For Loop...
For Each cell In Sheet4.Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row)
This part is not qualified with the Sheet reference so it would always find the last row used in column A on the ActiveSheet.
So all you need is to make the same change in the ComboMainHead_Change code as well.

Try it like this...

Please Login or Register  to view this content.

[bakerman2]

Is this what you mean ?

Please Login or Register  to view this content.

[ImranBhatti]

The issue with both of your codes is because of the way you are trying to find the last used row in column A. Look at the bold part in the For Loop...
For Each cell In Sheet4.Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row)
This part is not qualified with the Sheet reference so it would always find the last row used in column A on the ActiveSheet.
So all you need is to make the same change in the ComboMainHead_Change code as well.

Try it like this...

Please Login or Register  to view this content.

Dhanayawaad Guru Ji!
That worked like a charm.much much appreciated.

Thanks to bakerman and others who tried their best to help out.

[sktneer]

You're welcome Imran! Glad we could help.
Thanks for the feedback.