VBA to lock a Form Control to a cell?

**mikerickson** · 01-06-2012, 10:03 PM

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.

Sub stickControl()
    With ActiveSheet
        .Shapes(Application.Caller).Top = .Range("D4").Top
        .Shapes(Application.Caller).Left = .Range("D4").Left
    End With
End Sub

(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** · 01-06-2012, 10:48 PM

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** · 01-07-2012, 02:49 AM

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)