SOLVED

ActiveX Controls / OLEobjects in Excel Worksheet

Copper Contributor

I have a Workbook with several Worksheets.

in a worksheet there are seven Comboboxes each with some Vba Code for events Got/Lost focus and Change.

Added another Combo it doesn't work. I'm struggling to understand why

4 Replies

@Piero800 

There are several reasons why the newly added combobox might not be working as expected in your Excel worksheet:

1. Event Code Not Assigned:

  • Double-check if you've assigned the VBA event code (GotFocus, LostFocus, Change) to the new combobox. Right-click the combobox, select "Assign Macro," and choose the appropriate event procedure.

2. Enabled Property:

  • Ensure the newly added combobox's "Enabled" property is set to True. You can access this property through the VBA editor or by right-clicking the combobox and selecting "Format Control."

3. Linked Cell:

  • If the combobox is linked to a cell, verify that the cell value is valid and doesn't cause any errors in the code.

4. Code Conflicts:

  • If you have existing code manipulating other comboboxes, it might unintentionally interfere with the new one. Review your code for any potential conflicts, especially related to naming conventions or event handling.

5. Object Model Reference:

  • Make sure you're referencing the correct combobox object in your code. If you're using loops or referencing comboboxes by name, ensure the new combobox is included correctly.

Troubleshooting Steps:

  • Test a Simple Code: Try assigning a simple code snippet like displaying a message box in the "Change" event of the new combobox to see if it executes at all.
  • Debug: Use breakpoints in your VBA code to step through the execution and identify where the issue might be occurring.
  • Check for Errors: Review the VBA editor's "Immediate Window" for any error messages related to the new combobox.

Additional Tips:

  • Use descriptive names for your comboboxes and event procedures to avoid confusion.
  • Consider using object-oriented programming techniques to manage your comboboxes and their associated code, especially if you have many of them.

By following these steps and carefully examining your code, you should be able to identify the reason behind the non-functioning combobox and get it working correctly.

 

 

 

 

 

 

@smylbugti222gmailcom thank for reply.

I've already checked everything

"Not working" means that I cannot either click on it. When I try to click on it it "moves" some points left and do nothing.

All other Control work fine.

I've also tried to add a couple of simple option-boxes without any code and the result is the same: no interaction allowed

@Piero800 

If you've already checked the common causes mentioned earlier and the combobox still exhibits this unusual behavior, here are some additional things to explore:

1. ActiveX settings:

  • Enable ActiveX controls: Ensure that ActiveX controls are enabled in your Excel settings. Go to File > Options > Trust Center > Trust Center Settings > ActiveX Settings. Make sure "Enable all controls" is selected.
  • Security restrictions: Check if any security restrictions in your organization's policies might be blocking ActiveX controls. Consult your IT department if necessary.

2. Add-in interference:

  • Disable temporary add-ins: Try temporarily disabling any add-ins you have installed in Excel. Go to File > Options > Add-Ins > Manage COM Add-ins. Uncheck any add-ins and restart Excel to see if the issue persists.

3. File format compatibility:

  • Save as different format: If you received the workbook from another source, try saving it as a different format like xlsx or xlsm. Sometimes, compatibility issues between different versions of Excel can cause unexpected behavior with controls.

4. Repair Excel installation:

  • Office repair tool: As a last resort, consider using the Office repair tool to potentially fix any corrupted files or settings related to Excel. You can find this tool in your Control Panel under "Programs and Features" or "Apps & features" depending on your operating system.

5. Seek professional assistance:

  • Advanced troubleshooting: If none of the above solutions work, you might need to consult with an Excel expert or IT professional for further troubleshooting and potential code analysis. They can help identify any underlying issues specific to your situation.

Additional notes:

  • The behavior you described where the combobox "moves" slightly when clicked is unusual. It could be indicative of an underlying issue with the control itself or how it interacts with the worksheet.
  • Since you mentioned option boxes also exhibit similar behavior, it suggests the issue might be broader than just the comboboxes and could be related to how controls are rendered or interact within your specific Excel environment.

By exploring these additional possibilities and potentially seeking further assistance, you should hopefully be able to resolve the issue and regain functionality for your comboboxes and other controls in Excel.

best response confirmed by Piero800 (Copper Contributor)
Solution

@smylbugti222gmailcom thanks again

I've solved simply deleting and re-adding all the activeX controls of the worksheet (without editing the sheet code module) and everything now works fine.

1 best response

Accepted Solutions
best response confirmed by Piero800 (Copper Contributor)
Solution

@smylbugti222gmailcom thanks again

I've solved simply deleting and re-adding all the activeX controls of the worksheet (without editing the sheet code module) and everything now works fine.

View solution in original post