ActiveX Controls that remain in a static position on a Worksheet regardless of scrolling

[ttt123]

Hello,

What I'd like to do is have some ActiveX controls in a worksheet that remain in a static position regardless of whether someone is scrolling up or down on the worksheet.

So for example on the left hand side of the worksheet, I'd like to have a frame that has some labels, textboxes and comboboxes in it. On the right hand side of the worksheet, I'd like to have some data in some cells and depending on which cell is selected, I will load some data into my activeX controls. The catch is that I want to be able to scroll down the data on the right hand side of the sheet, while the activeX frame and controls remain in the top left hand corner of the screen.

I've investigated with Freeze Panes, and I don't think they will work for what I am trying to do. Another idea I had was to see if I could catch a Worksheet Scroll event and then reposition the activeX controls in that events code, but there doesn't seem to be a Worksheet Scroll event.

Does anyone have any ideas on how I could achieve this?

Thanks,
Taylour

 

[AlphaFrog]

You could create a userform with the controls on it and set the UserForm's ShowModal property to False so you can interact with the worksheet.

 

[ttt123]

thanks for the suggestion of using a Modeless userform!

If I use API calls, I can remove the title bar of the userform which makes it so that the userform can't be repositioned.

I can also reposition the form by catching if the workbook is resized incase someone resizes the Excel Window. Is there a way to detect whether the workbook window is moved by dragging the titlebar, as the resize event wont pick this up?

 

[ttt123]

I found a way to make the form stay in a static place regardless of whether the Excel Application is resized or moved using this example: http://www.cpearson.com/excel/SetParent.aspx

So basically the solution to this is:
1) Create a Modeless Userform and show it on Worksheet Activate, hide it on worksheet deactivate and workbook deactivate

2) Hide the menu bar of the form so that it can't be moved within the worksheet

3) use the example here: http://www.cpearson.com/excel/SetParent.aspx to link the form up with the Excel Window so that it always remains in a fixed position relative to the Excel application.

thanks for your help

 

[Jaafar Tribak]

You may want to take a look at post #5 HERE - there is also a downloable example.