Macro for Active X ComboBoxes

[liquidmettle]

Hi Everyone!

Okay, so I have a dashboard I'm building and I have a lot of combo boxes I have put into it. Alot. After updating 40 manually, I decided I would seek your help.

I have searched for this online but have not been able to find a working solution.

I would like help writing a macro that will update the relative reference of the linked cell for each combo box to the cell it is stationed in (each box sits in a merged cell that is two cells wide, 1 row tall). Updating the relative reference for each would be mind-numbingly awful, and extremely inefficient. I have already placed the boxes where I want, I just need to update their linked cell references.

The listfillrange does not need to change as this is set perfectly for each one already.

Also, I am applying a percentage format to each combo box (so that their number appears as a percentage instead of a decimal).

I am using this macro code:

ComboBox33 = Format(ComboBox33, "0%")

This works great. But is there a way to apply it to all of my combo boxes at once (I pulled that one at random) so I also do not have to manually copy and paste this into the macro of each combo box?

I appreciate all of your help, you guys are usually super spot on.

Thanks,

-LM

[Domenic]

I'm not sure that I understood you correctly, but see if this helps...

Please Login or Register  to view this content.

Hope this helps!

[liquidmettle]

Hi Domenic!

Thank you so much for replying so quickly! I just got back into town (was away on business) and was able to try your code.

The first macro worked flawlessly!!! This has saved me a tremendous amount of time. The relative references for the linked cells are all updated correctly. This is code I will definitely use in the future.


The second macro did not really do anything. There was no error, but it also did not update the format of the combo boxes. I noticed in visual basic there were only subs for the boxes I originally, manually, applied the format to.

If we could get this working that would be incredible.

Thank you so much for your help so far!!

-LM

[Domenic]

I'm glad to hear that the first macro works as expected. However, it's unclear to me how the second one should work. Can you please clarify?

[liquidmettle]

Domenic or Others,

I have a follow-up to this question. First Domenic, you can ignore my previous secondary question you asked me to clarify. I found a suitable work-around to that problem that I'm happy with.

However, I'd like to do the same thing we did with the ActiveX Combo Boxes with the Form Control Combo Box (Drop Down).

I tried this code but it did nothing:

Sub linkDropDowns()
Dim OleObj As OLEObject

For Each OleObj In Worksheets("Income").OLEObjects
If TypeName(OleObj.Object) = "DropDown" Then
OleObj.LinkedCell = OleObj.TopLeftCell.Address
End If
Next OleObj
End Sub

I have tried, for the life of me, to search for how you reference Form Controls versus Active X controls in Macros thinking "OleObj" is the culprit here, but there seems to be zero references online (or no easy to find ones).

Is is possible to link non-active x controls in the same way we did the active x combo boxes?

Thanks!

[Domenic]

Try...

Please Login or Register  to view this content.

Hope this helps!