Issues with Renaming ActiveXComboBoxes with VBA

Hello,

I will preface this by saying that I am not very experienced with VBA and that everything I have learned has come from trial and error & these forums. It is very likely that I'm making a dumb little error somewhere, but I am incredible frustrated after days of troubleshooting and decided it might be time to ask for help.

I am currently designing a scheduling "program" of sorts. The idea is that the user could add a new shift at the click of a button. I currently have it set up to where a series of macros will add a new row to the schedule, insert ActiveXComboBoxes into a cell for each day of the week, and populate each ComboBox with employee name options from a list.

The code to format & populate the ComboBoxes references them as their default names, "ComboBox1", "ComboBox2", etc., so as not to break the code, I want to rename the created ComboBoxes after they are formatted so that the ComboBoxes generated the next time the user adds a shift can be named "ComboBox1", "ComboBox2", etc. and will be formatted correctly.

I figured I could achieve this with a counter and a loop, and essentially just have each box renamed as "i+1" so the boxes continue to just get higher and higher in number. I'm having issues well before that point though.

I have tried a variety of different codes and ideas that I found online but am still met with the same error every time:

"1004: Unable to get the OLEObjects property of the Worksheet class"

I've included a bare-bones version of the code below:

Please Login or Register  to view this content.

All I'm trying to do is select ComboBox1 and rename it to something different. I feel like there's a really easy fix here that I'm missing. Error is being thrown by line 3 of the code.

Thank you in advanced for your help, and please let me know if you need any additional information!

Do you have a particular need for ActiveX controls? Using a data validation dropdown would be a lot simpler and less prone to weird behaviour.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Hello jordandria,

Welcome to the forum!

OLE objects have two parts: the OLE object and the Control, each has it's own properties. The Name belongs to the OLE Object and not the Control (OLEObject.Object).

Please Login or Register  to view this content.