Help ensuring ActiveX combobox stays on one sheet

[Everettexcel]

Hello all,
Apologies if this is a really stupid question; six weeks ago I'd barely ever done anything with Excel and now I'm trying to use code to do things, and I'm entirely reliant on what I can find online.
I have a workbook with 14 sheets, and on one of them an ActiveX combobox to enable people to look up specific data from within the rest of the sheets. It all works fine, except for the fact that the box won't disappear - if I move around in that sheet, or go into other sheets, the box keeps reappearing. I have tried some of the fixes I've found online but none of them work (some of them won't run at all), so I'm guessing I'm using the code incorrectly - one thing I find really difficult is that code posted online tends not to show precisely which bits need to be changed according to sheet names etc.. I found one suggested sub that involved Get Focus, but when I ran that, the box stopped displaying all of the possible options and only showed the first one. If anyone has the time to set out the right code, I'd be hugely grateful. Thank you.

 

[Akuini]

@Everettexcel, welcome to the Forum.
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

 

[GWteB]

Hi & welcome to MrExcel.

Would recommend to use a combo box put on a userform instead of a combo on a worksheet. If you're able to post the code you already have so far, then we can see how to amend it for use with a userform.

 

[Everettexcel]

Hi & welcome to MrExcel.

Would recommend to use a combo box put on a userform instead of a combo on a worksheet. If you're able to post the code you already have so far, then we can see how to amend it for use with a userform.

Hi, and thank you for the welcome! Is it possible to move the existing combo to a userform, or do I have to start from scratch? I'm sorry for more questions!

 

[GWteB]

The combo box cannot be moved, the code can but probably will need some modification.
In order to add a Userform to your project open the VBE (Alt+F11) and its project explorer (Ctrl+R). On the menu click Insert & Userform.
The toolbox window with controls will open and you can add a combo box to the userform in a similar way you did on your worksheet.

Now we are ready for coding so if you would like to post your code you already have (which was working fine you said) we can modify it so it will work with the combo box on the userform, and make adjustments for making the userform appear and disappear as desired.

 

[Everettexcel]

Thanks, I have created a combo box in a userform. It will take me a while to create a non-confidential example of things in order to share it here so forgive me for any delay. Thank you very much for even getting me this far!