Update UserForm label text based on dropdown and formula

Hi,

I looking to create a UserForm to generate location coordinates. Please see example attached.

Clicking 'Receipt' brings up a UserForm. The idea is that you would scan in the item number, select the room location, and that would flash up the assigned sublocation. Clicking 'Mark as Received' would then transfer that information to the bottom of columns A/B/C.

The formula for calculating the sublocation can be found in cell F11. I've found it very difficult to convert this to a VBA formula, or know how to use it properly in VBA.

Any help would be massively appreciated.

Many thanks,
Jamie

Hi Joshi,

Thanks for your help!

Updating the Label4 caption and clicking the 'Mark as Received' work well. The method for calculating the location isn't quite right, though. The 'AA' portion is actually a box ID that starts at AA and can go up to ZZ (AX, AY, AZ, BA, BB etc). This increments up by 1 letter when the final part of the location reaches 100, eg 1-AA99, 1-AA100, 1-AB1, etc.

Is there a way to calculate this location using VBA itself, and referring to ComboBox1.value etc, rather than needing to add helper cells to the sheet?

Thanks,
Jamie