VBA to lock a Form Control to a cell?

[Lewej23]

I have a combobox (form control) on a spreadsheet. I'd like to be able to lock the combobox to a cell, so it always stays in that cell. Is there any VBA code that will accomplish this?

[Richard Buttrey]

Hi,

Not sure what you mean by 'always stays in that cell'.

Do you perhaps mean the ControlSource property of the combobox which will read a specified cell value into the combobox, or update the cell value in response to a change in the combobox?

[Lewej23]

Neither... what I want is the ability for another user to still select one of the options in the combobox
i.e. let's say I have a combobox that lists all the sales personal: Joe, Jane, Mark, Rose, Sara... I want the user to be able to select "Rose"...

BUT not be able to move the combobox around.
i.e. currently I have the comobox above cell D4, I don't want the user to be able to move it to hover over cell J9 or P30...

[mikerickson]

If you assign it to this macro, the user can move it, but as soon as they click on the combobox, it will spring back where it belongs.
You might also want set the properties of the combobox to Move but don't resize.

Please Login or Register  to view this content.

(BTW, is there any reason that the user doesn't know the most convenient location for the control? I always assume that the user knows what lay-out works better than I do.)

[Lewej23]

I attached the spreadsheet I'm working on...
Right now I have a Button on top "Add Record" that will
1. add a row right under Row 2
2. insert the current date & time in B3, then copy and paste special as values in the same cell
3. inserts a ComboBox Form Control in D3, selects the input range, linked cell (E3), drop down lines, 3-D shading and unchecks the Print object property
4. applies the VLOOKUP function in D3 - which lists the selection from the ComboBox in the cell (if you move one of the ComboBoxes to the left or right a little, you'll see what I mean)
5. adds another ComboBox Form Control in F3 and the VLOOKUP function for it.
6. defaults to select cell C3 at the end
So, since the ComboBox is created by a macro and there will be many created, I don't think the VBA below will work? But, note that I am not very familier with VBA and VBA code - I know enough to be slightly dangerous.


And - to answer your question about why the user doesn't know the most convenient place to put the ComboBox is because the ComboBox is automatically placed where it should be by the "Add Record" macro.... The people that will be using this are not familier with using these kinds of controls - I can totally predict that someone will accidentally right click on one of them and move it... then the spreadsheet will be messed up and they won't have a clue on how to fix it.

Also note - I would LOVE to do this in Microsoft Access... it would be SO much easier.... but none of the 5 users have Access.

[mikerickson]

It isn't as visually appealing, but putting Data Validation on those cells is easy.
Since the lists are on a different sheet, Named ranges ListOfDealers, ListOfStatus, etc have been added.
(Unneeded Selecting has also been removed)

[Lewej23]

I stumbled upon a solution for this that seems to be working so far... once I place the button, the "Drawing Tools" tab appears in the Ribbon. In the Arrange section, I selected Align, then Snap to Grid. This allows me to re-size the button to cover the whole cell.
It's not 100% what I was looking for, but I think it might work.