0

Can I have opinions please?

I have a panel of user setting options hosted in ActiveX controls. Users make a selection and click a button. This hides the panel and presents results. Another button deletes the results and makes visible again the panel of ActiveX objects

Hiding works, but when the ActiveX objects are made visible again Listbox ActiveX controls (not others) no longer function, as if I am in Design Mode. I get this problem in Excel 2013, it was working in Excel 2010.

I have seen similar issues solved with MS Office updates, but I don't have the exact symptoms they describe. I don't expect I can persuade my IT department to role out MSOffice updates, if necessary, workarounds are welcome.

Code snip-its below. If i comment this out this section of the hide sub the problem doesn't occur.

Sub HideFilters()

...

For Each shp In ActiveSheet.Shapes
    If Left(shp.Name, Len("AutoShape_Index_")) <> "AutoShape_Index_" Then
        If Left(shp.Name, Len("Gant_btn")) <> "Gant_btn" Then
            shp.Visible = False
        End If
    End If
Next

'''

End Sub

Sub ShowFilters()

...

For Each shp In ActiveSheet.Shapes
    If Left(shp.Name, Len("AutoShape_Index_")) <> "AutoShape_Index_" Then
        If Left(shp.Name, Len("Gant_btn")) <> "Gant_btn" Then
            shp.Visible = True
        End If
    End If
Next

...

End Sub
Martijn Pieters
  • 889,049
  • 245
  • 3,507
  • 2,997
Bezique
  • 27
  • 4
  • What type of `ActiveX Controls` are you trying to hide and unhide ? `TextBox` ? `ComboBox` ? – Shai Rado Mar 30 '17 at 10:46
  • When a control is made invisible Word will also disable it and possibly lock it. It seems that the reverse doesn't work as it should. So, I recommend that you try to explicitly set those properties. Frankly, I am not sure that they exist for ActiveX controls (as they do for form controls), but even if they don't the error you get might be caused by their equivalents working in the background. Therefore try to let VBA assign a value and remove it, possibly doing the removal in a second loop to allow Word to respond. – Variatus Mar 30 '17 at 11:24
  • As stated, list boxes. And I am not using word, this all takes place in Excel. I chose ActiveX over form controls to give me more control over the appearance. My backup plan is to redesign the approach and move the panel to one sheet and the result to another, hiding and showing them as required – Bezique Mar 30 '17 at 11:51
  • Variatus, I have just added a line of code specifying Shp.Locked = False when showing the panel. It seems to have fixed my problem. Thank you – Bezique Mar 30 '17 at 11:56
  • As indicated in my comment to the answer below. The apparent solution was not reproducible. In the end I changed how I lay out settings panel and results, splitting them over two sheets. This turned out to be far more sensible. I should have done it in the first place. – Bezique Mar 30 '17 at 13:41

1 Answers1

0

When I try the code below it unhides all Active-X Controls that their name doesn't start with "AutoShape_Index_" or "Gant_btn" , in my ActiveSheet (not recommended).

Option Explicit

Sub ShowFilters()

Dim Shp As Shape

For Each Shp In ActiveSheet.Shapes
    If Not Shp.Name Like "AutoShape_Index_*" And Not Shp.Name Like "Gant_btn*" Then
        Shp.Visible = True
    End If
Next

End Sub
Shai Rado
  • 31,979
  • 6
  • 20
  • 45
  • This code did not address my issue, but it did tidy up the surrounding code, I thought it was just extra assistance, rather than a solution to the current problem. Initially Variatus' suggestion seemed to solve my problem. But it is not any longer - I am currently investigating – Bezique Mar 30 '17 at 12:36